By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

multiple table query

P: 12
First, I want to say I have used this board to resolve many issues that I have had with access and I thank everyone who contributes. I have attempted to resolve my issue myself, but I am stumped and would appreciate any help you can give.

This is a database to make reservations

I am trying to run a query against two tables. The first table (families) has the following fields

Family
Neighborhood
Street
nmbsnglebedsavail
Nmbrdoulbedsavail
nmbrtriplebedsavail

The second table, reservations has

Family
Traveler
Arrivaldate
Departuredate
Type (what kind of room)

I set up the form fine, so when I make a reservation and choose a Single Bed, the number is subtracted from the nmbrsnglebedsavail and it gives me the number of available remaining.

My problem is with the query. How can I query on the remaining beds available? When I use the same formula that I used in the form on the query, the beds remaining is static from one record to the next and doesnt subtract the total beds used according to each family. If i have two travelers (two separate records) visiting "family smith" the query subtracts a "single bed" from the Total Avail but does not carry the subtotal to the next record. So I get the same amount of remaining beds on each record, where in fact, it should total all the "singles" from the reservation table and subtract that from the Family table.

I hope I made sense..and again ..thanks for everyone's help!
Nov 18 '06 #1
Share this Question
Share on Google+
27 Replies


PEB
Expert 100+
P: 1,418
PEB
Hi can you show some data and put here the query that you've created...

You want the number of beds accordingly the information of the table with famillies?

I don't understand what is the role of the traveler here...

I think you fill the information for reservations and this data is transfered also in your table with the families? am i right?

The pb in this tructure that appears is that you should link the tables to see when the beds should be free or occupied

But when a family comes for second time??? What do you will do???

It seems there is something missing in the tables isn't it?
Nov 18 '06 #2

P: 12
Sorry, I didnt think I was too clear,

I am creating a database for someone who is in charge of finding places for Travelers to stay....in the houses of Families. I have a Traveler table with Information about the Traveler. I have a Family table with info about the family - this includes Number of Beds available in a single seting, # ina double setting (2 beds) or a triple room (3 beds per room....so 6 beds could be available).

I then have a table for reservations. I lookup field for the Family, I lookup field for the Traveler and a Look up field for type of room (single, double or triple). When I choose a family (lookup) then a traveler (lookup) , then the type of room (single, double,etc). I would like that the total number of beds available be deducted depending upon the type of setting I select. Oh, and more than one traveler can be at a family's house

I hope I have not confused you more.

I know I need to do some type of calculatoin in the query , but I do not know how to confinethat calcution according to the Family's beds availability.

Hi can you show some data and put here the query that you've created...

You want the number of beds accordingly the information of the table with famillies?

I don't understand what is the role of the traveler here...

I think you fill the information for reservations and this data is transfered also in your table with the families? am i right?

The pb in this tructure that appears is that you should link the tables to see when the beds should be free or occupied

But when a family comes for second time??? What do you will do???

It seems there is something missing in the tables isn't it?
Nov 18 '06 #3

PEB
Expert 100+
P: 1,418
PEB
So the information you need is to get information from the availables families, and current reservations, what families have the wanted kind of bed...

It seem that the period as interval should be introduced before opening this query?
Nov 18 '06 #4

NeoPa
Expert Mod 15k+
P: 31,186
It seems that what you want is half-way between an UPDATE query and a simple form.
You need to give a little thought to exactly what you need from your database here.

Think about updating the available beds (or rooms or whatever) in the Family table when a new reservation is made.
That way, you are not trying to calculate it in the query (not an appropriate place in this situation) itself.
If you do it this way, the problem vanishes in the mist.
Nov 18 '06 #5

P: 12
That is what I want...update the available beds when someone takes one (when I choose single, double, etc) from the drop down list. Then if I erase that records (the reservation is old) I would like it to be updated to its original number.

Do I do that through or a event. Then I would be able to query upon what is actually available?

thanks



It seems that what you want is half-way between an UPDATE query and a simple form.
You need to give a little thought to exactly what you need from your database here.

Think about updating the available beds (or rooms or whatever) in the Family table when a new reservation is made.
That way, you are not trying to calculate it in the query (not an appropriate place in this situation) itself.
If you do it this way, the problem vanishes in the mist.
Nov 18 '06 #6

PEB
Expert 100+
P: 1,418
PEB
He he

But one reservation can be for now and other one for 3 months in the future???

And if your bad is engaged Now this is not the situation for 3 months in the future? Am I right?
Nov 18 '06 #7

NeoPa
Expert Mod 15k+
P: 31,186
That's a very good point PEB.

spminba,

Has this concept been covered in your design.
If not, you will not get very far before falling in a heap :(.
Nov 18 '06 #8

PEB
Expert 100+
P: 1,418
PEB
This is delicious topic the reservations..

However i've tested them in the travel agency for which i've writted some things...

But i'm sure that spminba should't touch to the table with families... It's the core information for the beds...

This information should be used in combination with the reservations that have been done for the respective period!

At least I think so!

That's a very good point PEB.

spminba,

Has this concept been covered in your design.
If not, you will not get very far before falling in a heap :(.
Nov 18 '06 #9

P: 12
Exactly, I can make a reservation now, thus taking an available bed from the family table allotment or make a reservation in the future. However, the structure of this company is that usually reservations are made immediately. So, I would just like to be able to query upon the available bed now. So if I have one Traveler staying at the "Smith Family's house" in a single bed, I would like the query to show that the "Smith's" have one less bed available.

here is what i get now

Familia Single Tot Double Tot Triple Tot Type Precio Avail Single
Smith 4 2 0 Single 3
Smith 4 2 0 Single 3

Two travelers are staying at the Smith Family. Both are using a single bed. Total beds 4 but each entry shows remaining 3. How would I make the second reservation show up remaining beds 2?
Nov 22 '06 #10

NeoPa
Expert Mod 15k+
P: 31,186
For this to work, you'd have to clear out reservations from your table as they go into history.
As they are reserved you need to add it to the reservations table.
Your query should then query both tables, grouping by 'Family' and a fields equal to Number of beds - Sum(Bed reservations) should be shown for each Bed type.
Nov 22 '06 #11

P: 12
I have a way to clear out old reservations (by "leave date") and I have grouped by family. I also have an expression that gives a value of 1 when a single,double, etc bed is chosen. I also have another expression that takes expr1 (single double,etc) and subtracts it from total bed available in the Family table. However, each record in the reservation table for the same family still takes the total of bed available minus the reserved bed. So two seperate travelers staying with the same family with a total of 3 single beds, shows, in their query record, a total of 2 bed available. It does not show a 'running total' of beds available, which should just be 1. 3 total beds - 2 single beds (2 travelers) = 1 bed available now.

Have I thouroughly confused everyone now?

ps. maybe it is how I am calculating the single bed number. I have
Expr1: IIf([type]="single",[single_avail]-1,0). Where type is the drop down list of the bed types. Single_avail is the field in the family table.

The for my total i have [Single_avail ]-[Expr1] <---- this is the total that gives me the same number for each record instead of summing up all beds taken.
Nov 24 '06 #12

PEB
Expert 100+
P: 1,418
PEB
Yeah the hous do immediate reservations but only for now and this moment!

But what about the near future? In 2 - 3 months? You invest your time only for 1-2 months in advance? People who pay you should be very rich! For me my employers want system that works at least 10 years!

So better should be to consider the dates...

With your table with reservations maybe should be good to add a field if the reservation is active or not... This will facilitate the search engine...

Instaed list of families it should be better to have the list with rooms in families... With this approach you will know for each period is the room is available...

In the list of rooms you need also category - kind of room that should help you to dress the list of available rooms of the respective kind...

And of course the list of families.. where each family should figure only once! and the ID from the respective table will go to the table with rooms...

This is the relational model of your database..

If you do like this... you will have a list with active reservations... From this list you'll understand which room is available and whicch not for a given period!

In fact you will obtain a query with the engaged rooms for a period...

This is a lot...

Coz using the list of your rooms you will get all double rooms that aren't in the first list... and you can book them!

You see your task a bit decomposed?

Do the list of engaged rooms...

Than the list of available rooms...

The most difficult part is the period condition...

You have Start and end reservation and also you are seeking for a start and end period ...

How to get all occupied rooms for the respective period??? This is the first major probklem to be discussed!

The conditions maybe should be:

The wanted start period shouldn't be between the start and end of the respective reservation

AND

The wanted end period shouldn't be between the start and end of the respective reservation

AND

((The wanted start should be bigger of the end of reservation) or (The wanted end should be less of the begin of the reservation))

PLS try to create those conditions using SQL... If pbs post your SQL here to see what happens!
Nov 26 '06 #13

NeoPa
Expert Mod 15k+
P: 31,186
It is not logical to do as you are doing (in fact you would expect exactly what you're getting).
You need to update the records to reflect bookings otherwise bookings will never interrelate.
When a booking is confirmed - update the beds available.
When the booking is expired - update it back to reflect the bed is again available.
Nov 26 '06 #14

P: 12
That is what I am attempting to accomplish. Update the beds available. But I don't know how to take the requested bed of "single" from the reservation table and subtract it from the beds available from the Family table. Any suggestions?

So far what I have, does subtract from TOTAL number of beds available but does not keep that subtotal for the next traveler who goes to the same family. Both records show the same amount of beds after each individual traveler takes one bed. So the total should be Family!TotalAvail - Reservation!TypeofBed with a running total..but the Family!TotalAvail is constant so the end total is always the same in each Reservation record.

I need the new reservation to subtract the beds available from the other travelers within the same family. This is where I am running into problems.

IE. Jones Family Total Avail :3 Joe Traveler Single Bed Total Remaining:2

Jones Family Total Avail :3 Jane Traveler Single Bed Total Remaining :2

The remaining beds do not update to reflect ALL beds taken within the same family.

It is not logical to do as you are doing (in fact you would expect exactly what you're getting).
You need to update the records to reflect bookings otherwise bookings will never interrelate.
When a booking is confirmed - update the beds available.
When the booking is expired - update it back to reflect the bed is again available.
Nov 28 '06 #15

NeoPa
Expert Mod 15k+
P: 31,186
That's because you're not updating the data at all.
Your're showing calculations which include only the current record.
You need three more fields in Families to hold the Total beds for each type.
When a new Reservation is added (accepted after entry) you need to update the data in the table (Families) and requery the form.
You also need to arrange for reservations to be deleted on expiry and, before deleting you must update Families to show the bed is available again.
This is what is meant by 'updating' the data.
Nov 28 '06 #16

P: 12
Ok...thanks for the explanation. I will try that and let you know how it comes out. Man, you all must have an endless supply of patience with people like me!!!

thanks

That's because you're not updating the data at all.
Your're showing calculations which include only the current record.
You need three more fields in Families to hold the Total beds for each type.
When a new Reservation is added (accepted after entry) you need to update the data in the table (Families) and requery the form.
You also need to arrange for reservations to be deleted on expiry and, before deleting you must update Falilies to show the bed is available again.
This is what is meant by 'updating' the data.
Nov 29 '06 #17

P: 12
Ok...here is what i did and I am sure I did it incorrectly.

On the form page, I have a calculation that DOES give me the number of bed available after a bed is taken. The Main form is Family and subform is Reservations. So the calculation of single type beds is restricted to each family. (Which I cant seem to get to do in a query). I am now trying to take that calculated value and push that information to a bounded field in the family table Single_Avail.

I know storing calculated values is not a wise thing to do, however, I do not know how else I can accomplish this. So, in an after update event i have

me.singleleft.value = me.singleavail.value. Single left is the unbound calculated field and singleavail is a field in the family table. However, it is now not updating.

What am I doing incorrectly? If you could just give me the first 10 things wrongs, I would appreciate it.

Anyone have skype? I'll pay for the call!!!!

thanks
Dec 5 '06 #18

NeoPa
Expert Mod 15k+
P: 31,186
I know storing calculated values is not a wise thing to do, however, I do not know how else I can accomplish this. So, in an after update event i have

me.singleleft.value = me.singleavail.value. Single left is the unbound calculated field and singleavail is a field in the family table. However, it is now not updating.

What am I doing incorrectly? If you could just give me the first 10 things wrongs, I would appreciate it.
Try putting the code in the BeforeUpdate event.

Anyone have skype? I'll pay for the call!!!!
Very generous of you!

In case anyone else doesn't know, Skype to Skype calls are free :).
Dec 6 '06 #19

P: 12
Thanks..put the code in the BeforeUpdate event and still not updating.

Thanks for all your help, but it looks like this one is unsolvable (or they way I have done it is unsolvable). I will leave everyone in peace now!

You've been great!!!!





Try putting the code in the BeforeUpdate event.


Very generous of you!

In case anyone else doesn't know, Skype to Skype calls are free :).
Dec 6 '06 #20

NeoPa
Expert Mod 15k+
P: 31,186
You need both values to be stored in your table.
Putting it in the BeforeUpdate event ensures that it is only updated positively once.
You need similarly to handle the reverse situation when the record is updated or removed (maybe don't allow update as that is quite complicated).
The important point, though, is that you don't have the field bound, therefore it will not update the record, therefore it will not have the desired effect.
Dec 6 '06 #21

MMcCarthy
Expert Mod 10K+
P: 14,534
Maybe I've misunderstood but it sounded like you wanted to pass the value in singleleft to singleavail. If so then try the following:

Expand|Select|Wrap|Line Numbers
  1.  Me.singleavail = Me.singleleft
Mary
Dec 7 '06 #22

P: 12
ok....singleleft passes the value to singleavail on if I first change the current value in the singleavail. If I do not touch the singleavail on the form and the singleleft changes, the singleavail remains the same!!!!

Singleavail is the bound control and singleleft is the unbound.

thanks


Maybe I've misunderstood but it sounded like you wanted to pass the value in singleleft to singleavail. If so then try the following:

Expand|Select|Wrap|Line Numbers
  1.  Me.singleavail = Me.singleleft
Mary
Dec 7 '06 #23

NeoPa
Expert Mod 15k+
P: 31,186
If you have an unbound value then it won't work!
That's why the calculation was wrong in the first place.
Dec 8 '06 #24

P: 12
I want the calculation in the form (unbound) to be transferred to the bound field on the form. How would i make the calculation field bound when it does not exisit anywhere else?

Thanks

sean
Dec 8 '06 #25

NeoPa
Expert Mod 15k+
P: 31,186
I want the calculation in the form (unbound) to be transferred to the bound field on the form. How would i make the calculation field bound when it does not exisit anywhere else?

Thanks

sean
Sean,

You may want to reread some of the posts in this thread.
If you don't store both values then the calculation will be wrong!
That's where you started from.
If you can't understand what I'm saying maybe just trust that I know what I'm talking about.
As posted earlier, if it's not stored and updated as laid out in earlier posts, then you will only ever calculate the total minus the current record.
I don't know of any way of expressing this idea more clearly, I'm sorry.
Dec 8 '06 #26

P: 12
I finally got it to work. I was trying to use the BeforeUpdate Event with a calculated field, however, the calculation did not trigger the event. I put the Me.singleavail = Me.singleleft in teh Oncurrent and this triggered the update when i went to the next record. It is not the best answer, but it works!!!! So now the table gets updated with the information I need.

Thanks everyone for all your help and guidance through this!

I really appreciate it.



Sean,

You may want to reread some of the posts in this thread.
If you don't store both values then the calculation will be wrong!
That's where you started from.
If you can't understand what I'm saying maybe just trust that I know what I'm talking about.
As posted earlier, if it's not stored and updated as laid out in earlier posts, then you will only ever calculate the total minus the current record.
I don't know of any way of expressing this idea more clearly, I'm sorry.
Dec 8 '06 #27

NeoPa
Expert Mod 15k+
P: 31,186
Not a problem.
The solution is the important thing :).
Dec 8 '06 #28

Post your reply

Sign in to post your reply or Sign up for a free account.