multiple table query

[spminba]

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
nmbsnglebedsava il
Nmbrdoulbedsava il
nmbrtriplebedsa vail

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 nmbrsnglebedsav ail 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!

[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?

[spminba]

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?

[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?

[NeoPa]

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.

[spminba]

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.

[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?

[NeoPa]

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 :(.

[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 :(.

[spminba]

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?

[NeoPa]

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.

[spminba]

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.

[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!

[NeoPa]

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.

[spminba]

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!TotalAva il - Reservation!Typ eofBed with a running total..but the Family!TotalAva il 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.