If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Hotel Booking Project Delemma
I am having a dilemma with a Hotel Booking Project which is
a small part of a larger project. If someone can help me with this, or refer me to a sample DB it would be seriously appreciated! I have my tables set up as follows. tblMembers one to many tblHotelBookingsToMembers tblHotelBookings one to many tblHotelBookingsToMembers (This is setup in the same manner as the Products, Order Details, Orders tables in the Northwind DB) In the tblHotelBookings I have fields to capture Number of Nights, & Persons for each booking If two members are staying in the same room, I book them each individually under the same HotelBooingID in tblHotelBookings So let's say that HotelBookingID 1001 has 3 Nights & 2 Persons. Because each member (John & Mary) is linked to HotelBookingID 1001, when I try to create a report or query, that sums the number of room nights and persons, I get duplicate values. 6 nights & 4 persons Essentially I am trying to figure out how I can book two Members into the same hotel (specific room #s aren't captured) so that each member has a booking in my DB and I can design a report that will show the number of persons & nights for each booking without duplicates. This info has to be passed on to the Hotel. Lets see if I can be more clear... If one member (John) is booked, but has a second person (i.e. Spouse) staying with him who is NOT a Member, then I do NOT need to show a booking for that second person in my DB. I simply indicate 2 persons. So I only have ONE booking. No problem. But if his spouse is a member then I must also book that person into the DB, which creates 2 members for the same HotelBookingID Any advice or help is welcome! Thank You! PS: If more clarity is needed, feel free to indicate so. |
#2
|
|||
|
|||
Contact me at my email address below if you would like some help with your
database. My fees are very reasonable. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "Abe" wrote in message ... I am having a dilemma with a Hotel Booking Project which is a small part of a larger project. If someone can help me with this, or refer me to a sample DB it would be seriously appreciated! I have my tables set up as follows. tblMembers one to many tblHotelBookingsToMembers tblHotelBookings one to many tblHotelBookingsToMembers (This is setup in the same manner as the Products, Order Details, Orders tables in the Northwind DB) In the tblHotelBookings I have fields to capture Number of Nights, & Persons for each booking If two members are staying in the same room, I book them each individually under the same HotelBooingID in tblHotelBookings So let's say that HotelBookingID 1001 has 3 Nights & 2 Persons. Because each member (John & Mary) is linked to HotelBookingID 1001, when I try to create a report or query, that sums the number of room nights and persons, I get duplicate values. 6 nights & 4 persons Essentially I am trying to figure out how I can book two Members into the same hotel (specific room #s aren't captured) so that each member has a booking in my DB and I can design a report that will show the number of persons & nights for each booking without duplicates. This info has to be passed on to the Hotel. Lets see if I can be more clear... If one member (John) is booked, but has a second person (i.e. Spouse) staying with him who is NOT a Member, then I do NOT need to show a booking for that second person in my DB. I simply indicate 2 persons. So I only have ONE booking. No problem. But if his spouse is a member then I must also book that person into the DB, which creates 2 members for the same HotelBookingID Any advice or help is welcome! Thank You! PS: If more clarity is needed, feel free to indicate so. |
#3
|
|||
|
|||
hi,
you can add the DISTINCT clause in the query serving data to the report. It omits records that contain duplicate data in the selected fields. To be included in the results of the query, the values for each field listed in the SELECT statement must be unique. For example, several employees listed in an Employees table may have the same last name. If two records contain Smith in the LastName field, the following SQL statement returns only one record that contains Smith: SELECT DISTINCT LastName FROM Employees; If you omit DISTINCT, this query returns both Smith records. If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results. Kind regards, ------ Haris http://www.manage-systems.com ----------------------------------------- "Abe" wrote: I am having a dilemma with a Hotel Booking Project which is a small part of a larger project. If someone can help me with this, or refer me to a sample DB it would be seriously appreciated! I have my tables set up as follows. tblMembers one to many tblHotelBookingsToMembers tblHotelBookings one to many tblHotelBookingsToMembers (This is setup in the same manner as the Products, Order Details, Orders tables in the Northwind DB) In the tblHotelBookings I have fields to capture Number of Nights, & Persons for each booking If two members are staying in the same room, I book them each individually under the same HotelBooingID in tblHotelBookings So let's say that HotelBookingID 1001 has 3 Nights & 2 Persons. Because each member (John & Mary) is linked to HotelBookingID 1001, when I try to create a report or query, that sums the number of room nights and persons, I get duplicate values. 6 nights & 4 persons Essentially I am trying to figure out how I can book two Members into the same hotel (specific room #s aren't captured) so that each member has a booking in my DB and I can design a report that will show the number of persons & nights for each booking without duplicates. This info has to be passed on to the Hotel. Lets see if I can be more clear... If one member (John) is booked, but has a second person (i.e. Spouse) staying with him who is NOT a Member, then I do NOT need to show a booking for that second person in my DB. I simply indicate 2 persons. So I only have ONE booking. No problem. But if his spouse is a member then I must also book that person into the DB, which creates 2 members for the same HotelBookingID Any advice or help is welcome! Thank You! PS: If more clarity is needed, feel free to indicate so. |
#4
|
|||
|
|||
I was under the impression that commercial solicitations
were not allowed on these Groups. But I could be wrong So, being that its ThanksGiving wekend here in Canada, I will say thanks, but I'm not interested. "PC Datasheet" wrote in message link.net... Contact me at my email address below if you would like some help with your database. My fees are very reasonable. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "Abe" wrote in message ... I am having a dilemma with a Hotel Booking Project which is a small part of a larger project. If someone can help me with this, or refer me to a sample DB it would be seriously appreciated! I have my tables set up as follows. tblMembers one to many tblHotelBookingsToMembers tblHotelBookings one to many tblHotelBookingsToMembers (This is setup in the same manner as the Products, Order Details, Orders tables in the Northwind DB) In the tblHotelBookings I have fields to capture Number of Nights, & Persons for each booking If two members are staying in the same room, I book them each individually under the same HotelBooingID in tblHotelBookings So let's say that HotelBookingID 1001 has 3 Nights & 2 Persons. Because each member (John & Mary) is linked to HotelBookingID 1001, when I try to create a report or query, that sums the number of room nights and persons, I get duplicate values. 6 nights & 4 persons Essentially I am trying to figure out how I can book two Members into the same hotel (specific room #s aren't captured) so that each member has a booking in my DB and I can design a report that will show the number of persons & nights for each booking without duplicates. This info has to be passed on to the Hotel. Lets see if I can be more clear... If one member (John) is booked, but has a second person (i.e. Spouse) staying with him who is NOT a Member, then I do NOT need to show a booking for that second person in my DB. I simply indicate 2 persons. So I only have ONE booking. No problem. But if his spouse is a member then I must also book that person into the DB, which creates 2 members for the same HotelBookingID Any advice or help is welcome! Thank You! PS: If more clarity is needed, feel free to indicate so. |
#5
|
|||
|
|||
Thanks for the reply Haris,
I think I understand what you are saying, however the problem arises when there are two different people assigned to One Hotel Booking, as you indicate about the 2 Smiths. For Example: Smith and Jones booked into the same Hotel as a double occupancy So the HotelBookingID record shows 2 Persons 4 Nights When I try to design a report (or Query) that will sum all the Nights & Persons, I get duplicate values. Here is a more detailed example. This is a Double Occupancy. Smith Sheraton Hotel In Oct 10 Out Oct 14 4 Nights 2 Persons Jones Sheraton Hotel In Oct 10 Out Oct 14 4 Nights 2 Persons These two people are staing in the same room So Smith and Jones come from the Member Table & are linked to the HotelBookings via the tblHotelBookingsToMembers. The HotelBooking Detail is only one record, but when I design a query to show the people staying at that hotel I get the 2 records as shown in the above example. That's OK I understand why. But, Here's the problem; I have to give a report to the Hotel that sums All the bookings (which includes others). When I design my report to sum the Nights and Persons to give to the hotel I get 8 Nights & 4 Persons for the above booking I'm beginning to think that I am going about this with a faulty method. That's why im looking for some advice. Sorry if I confused you even more. Thanks "Haris Rashid" wrote in message ... hi, you can add the DISTINCT clause in the query serving data to the report. It omits records that contain duplicate data in the selected fields. To be included in the results of the query, the values for each field listed in the SELECT statement must be unique. For example, several employees listed in an Employees table may have the same last name. If two records contain Smith in the LastName field, the following SQL statement returns only one record that contains Smith: SELECT DISTINCT LastName FROM Employees; If you omit DISTINCT, this query returns both Smith records. If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results. Kind regards, ------ Haris http://www.manage-systems.com ----------------------------------------- "Abe" wrote: I am having a dilemma with a Hotel Booking Project which is a small part of a larger project. If someone can help me with this, or refer me to a sample DB it would be seriously appreciated! I have my tables set up as follows. tblMembers one to many tblHotelBookingsToMembers tblHotelBookings one to many tblHotelBookingsToMembers (This is setup in the same manner as the Products, Order Details, Orders tables in the Northwind DB) In the tblHotelBookings I have fields to capture Number of Nights, & Persons for each booking If two members are staying in the same room, I book them each individually under the same HotelBooingID in tblHotelBookings So let's say that HotelBookingID 1001 has 3 Nights & 2 Persons. Because each member (John & Mary) is linked to HotelBookingID 1001, when I try to create a report or query, that sums the number of room nights and persons, I get duplicate values. 6 nights & 4 persons Essentially I am trying to figure out how I can book two Members into the same hotel (specific room #s aren't captured) so that each member has a booking in my DB and I can design a report that will show the number of persons & nights for each booking without duplicates. This info has to be passed on to the Hotel. Lets see if I can be more clear... If one member (John) is booked, but has a second person (i.e. Spouse) staying with him who is NOT a Member, then I do NOT need to show a booking for that second person in my DB. I simply indicate 2 persons. So I only have ONE booking. No problem. But if his spouse is a member then I must also book that person into the DB, which creates 2 members for the same HotelBookingID Any advice or help is welcome! Thank You! PS: If more clarity is needed, feel free to indicate so. |
#6
|
|||
|
|||
You aren't wrong. Commerical solicitatation is not welcome in these groups.
Steve, the poster making the offer, has been told many times by many people that his solicitations in these newsgroups are inappropriate, but he ignores us all. Glad to see you're not interested in using a service that has to attract business using such questionable practices. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Abe" wrote in message ... I was under the impression that commercial solicitations were not allowed on these Groups. But I could be wrong So, being that its ThanksGiving wekend here in Canada, I will say thanks, but I'm not interested. "PC Datasheet" wrote in message link.net... Contact me at my email address below if you would like some help with your database. My fees are very reasonable. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "Abe" wrote in message ... I am having a dilemma with a Hotel Booking Project which is a small part of a larger project. If someone can help me with this, or refer me to a sample DB it would be seriously appreciated! I have my tables set up as follows. tblMembers one to many tblHotelBookingsToMembers tblHotelBookings one to many tblHotelBookingsToMembers (This is setup in the same manner as the Products, Order Details, Orders tables in the Northwind DB) In the tblHotelBookings I have fields to capture Number of Nights, & Persons for each booking If two members are staying in the same room, I book them each individually under the same HotelBooingID in tblHotelBookings So let's say that HotelBookingID 1001 has 3 Nights & 2 Persons. Because each member (John & Mary) is linked to HotelBookingID 1001, when I try to create a report or query, that sums the number of room nights and persons, I get duplicate values. 6 nights & 4 persons Essentially I am trying to figure out how I can book two Members into the same hotel (specific room #s aren't captured) so that each member has a booking in my DB and I can design a report that will show the number of persons & nights for each booking without duplicates. This info has to be passed on to the Hotel. Lets see if I can be more clear... If one member (John) is booked, but has a second person (i.e. Spouse) staying with him who is NOT a Member, then I do NOT need to show a booking for that second person in my DB. I simply indicate 2 persons. So I only have ONE booking. No problem. But if his spouse is a member then I must also book that person into the DB, which creates 2 members for the same HotelBookingID Any advice or help is welcome! Thank You! PS: If more clarity is needed, feel free to indicate so. |
#7
|
|||
|
|||
From Abe :
snap! Any advice or help is welcome! Thank You! PS: If more clarity is needed, feel free to indicate so. Well, one quick and simple way would be to add a field to the tblHotelBookings indicating it is a 'combined' booking (or something like that). Simple yes/no field would do. Smith has this value set to No and Jones set to Yes. Then, sum up all the fields that have this field set to no. Another approach would be to create a query to sum the totals. If your reservations table has fields for room (x), date (y) in and date (z) out, you could query it and use distinct to get x, y and z only one time. However, problems might rise when you have mister Smith from Thursdat till Sunday and miss Smith for only Saturdaynight because you will have to have more logic in the query. Not undoable though. I think the first solution would be the easiest since that will show mister Smith for 3 nights and miss Smith for 1 night. With the combined booking field set to Yes for miss Smith and thus leaving her out of the totals. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Project and Employee Relationship | Jason | General Discussion | 3 | March 30th, 2005 06:45 AM |
How do I create a fairly simple hotel booking system? | law | Database Design | 1 | February 19th, 2005 12:27 PM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
Signing a VBA mde/mdb Access 2003 | John Buckett | General Discussion | 3 | July 3rd, 2004 09:14 PM |
Table design for a booking system | Brian C | Database Design | 2 | April 27th, 2004 03:11 AM |