Kevin-
Date: 2015-09-18 21:04Subject: Re: [MS_AccessPros] Group three days to send request, but they should not be actually grouped.Kevin-
I think you can solve your immediate problem by adding a second date to tblServiceItinerary: ServiceDateFrom and ServiceDateTo. For items that occur on one date (like a dinner reservation), the date values will be the same in both fields. For a hotel booking, they would reflect check-in and check-out dates.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
On Sep 18, 2015, at 1:49 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John-
The attachment is the latest table design.
Kevin
Regards,
Kevin Zhao
From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-09-18 18:43
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] Group three days to send request, but they should not be actually grouped.
Kevin-
OK, let me see the latest table design. It's the table design that we need to work on.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
On Sep 17, 2015, at 11:46 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John-
Yes, you are right. Hotel booking will not be identified by tblServicePart. It will be identified by "HotelBookingService (YES/NO)" in tblServiceItinerary, which I forgot to show it in the table uploaded to group files. And it was my fault to list hotelbooking service in the tbleService. Sorry for that. For how to identify which city to stay overnight, I use "Max(ServiceItineraryCityID),"tblServiceItineraryCityID","ServiceItineraryID=ServiceItineraryID (from tblServiceItinerary). Please help.
Kevin
Regards,
Kevin Zhao
From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-09-18 01:05
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] Group three days to send request, but they should not be actually grouped.
Kevin-
OK, I have the database. I see tblItinerary is the root table. It links to tblServicePart that lists services, and in the related tblService, you show Train Ticket, Hotel Booking, and Local Guide. This links next to tblServiceItinerary which contains a date. It's not until you get to tblServiceItineraryCityID that you identify the particular city. And finally, you get to the actual Hotel Booking.
It would seem to me that hotel bookings should link directly off tblServicePart for those services identified as a booking. They need to be in a hotel in a particular city from - to certain dates. Other services might link to details about train tickets or guides in other tables. I think the dates should be in tblServicePart - Hotel service from - to dates. Train ticket on a certain date. Guide on a certain date. Then use detailed tables for travel and guides linked to that. This means that you will not find all ServicePartID values in the tables that list hotel, travel, or guide details - only the ID related to that service.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
On Sep 17, 2015, at 3:29 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John-
I just uploaded the tables with relationship stored to the Assistance Needed folder. Please have a look, and help. Thanks in advance.
Kevin
Regards,
Kevin Zhao
From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-09-17 20:41
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] Group three days to send request, but they should not be actually grouped.
Kevin-
OK, then Itinerary ID should be the "anchor." The hotels and other services should have nothing to do with each other! For Itinerary ABC123, you have a need for hotels in Paris for 3 nights for 10 people and Strasbourg for 2 nights for 10 people. While in Paris (not related to the hotel record), you will book several services.
Itinerary —> Cities —> Hotels
|
+—> Services
Can you upload just the structure of your database (don't need the data) to the Assistance Needed folder?
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
On Sep 17, 2015, at 2:30 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John-
The main thing is the intinerary, it is for the same group of people through all services including booking air tickets, hotels, meals and train etc. If the access application was dealing only one type of service, it won't be a problem to send one request for several days staying in one hotel. But the actual situation is more complicated. So I have to devide the whole itinerary into different parts by following the dates as the main line. In another word, thoes dates for staying in one hotel might be arranged in different parts because of other services, such as booking a local tour guide the first day of staying in the hotel, and booking another visit to a local company for the rest days of staying in the same hotel. This is the scenario I run into. Please help!
Kevin
Regards,
Kevin Zhao
From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-09-17 18:51
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] Group three days to send request, but they should not be actually grouped.
Kevin-
Sorry for taking so long to respond - had house guests all day yesterday.
You need to separate hotels and events and individuals from each other. What is the one common identity? Do all the members of a particular group all follow the same itinerary, events, and stay in the same hotels? If so, then that should be the ID that you use in all the linking tables. It doesn't make sense that individuals in a particular "group" are doing different things or staying in different places. If you have some events that can be booked separately by an individual, then key those to individuals, not the group.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
On Sep 16, 2015, at 12:49 AM, zhaoliqingoffice zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Jone-
To make it clearer, the group may stay in one city for one or two or even longer days, but the may book different services, and may stay in one hotel or may not...so we need show each date for thiese reason. In the case of booking one hotel, it comes the problem on how to group them technically with code or other methods, so that we can send one request as one booking code to the hotel in stead of send requests each day separately with different booking code, this may confuse the hotel...
Kevin
发自我的小米手机
在 "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2015年9月16日 上午3:25写道：
Kevin-
That seems to make sense, but it still doesn't explain why you have three separate records for one hotel booking. Are you generating that from a query? If so, what's the SQL?
Yes, it's possible to arrange city names like that if you write a custom function to return all the city names as a string based perhaps on the Group Code.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
On Sep 15, 2015, at 3:35 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John-
The story line is like this:
First day the group travels by plane, then travel by coach for three days, then take high speed train to another city, followed by two days trip by coach, finally fly back to their country. So in these whole itinerary, as a tour operator, we will book air ticket, coach, train, and hotels for our guests. In order to show all the services we book for this group in a detailed itinerary list, I divide it into different parts for difference service booking. Please see the tables below.
tblGroups – Group Code
tblGroupMembers - Group Code, MemberID, TravellerLastName, TravellerFirstName, etc..
tblPart – PartID, PartService
(we may book coach for the itinerary in the first three days, then booking train, and followed by few days coach itinerary.)
tblPartItinerary – PartineraryID, PartID, Date
tblDailyCityVisit – DailyCityVisitID, PartineraryID, CityID
(Guests may travel three cites per day)
Is it possible to arrange the cities Horizontally like this?
Dreux-Versailles-Paris
If we visit three cites, is it possible to identify the last one as the overnight city?
tblHotelBooking – HotelBookingID,DailyCityVisitID, HotelID, TwinRoom, SingleRoom, RateTwinRoom, RateSingleRoom
This is the situation, I describe it with haste, there might be somewhat inaccuracies, hopefully it shows the clear picture of my situation. Please help!
Regards,
Kevin Zhao
From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-09-15 21:02
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] Group three days to send request, but they should not be actually grouped.
Kevin-
That still doesn't explain why there are three records with three different codes and in/out dates. I would expect to see ONE record for the group in on 1 Jan and out on 4 Jan.
Your original example shows three different group codes, so you can't even match on that.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
On Sep 15, 2015, at 2:37 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John-
You're right, they are under the same group code, but for sending request to each hotel, I use different code (hotelbooking code) other than group code. John, please look again if it is possible to group these three days into one, so that I can send one request to Hotel XX. And when the hotel offers us the rate that we can confirm, how can we sum up the total amount for these three days? John, please help!
HotelBookingCode Overnight City Checkin Checkout HotelID Hotel ... ...
HBCode-001-0050 Paris 1-Jan-16 2-Jan-16 20 Hotel XX
HBCode-001-0058 Paris 2-Jan-16 3-Jan-16 20 Hotel XX
HBCode-001-0066 Paris 3-Jan-16 4-Jan-16 20 Hotel XX
Kevin
Regards,
Kevin Zhao
From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-09-15 20:15
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] Group three days to send request, but they should not be actually grouped.
Kevin-
I suspect you have a table design problem. You haven't completely described your application, but from the information included, I would expect to find these tables:
tblGroups - Group Code, Tour Code, etc.
tblGroupMembers - Group Code, MemberID, TravellerLastName, TravellerFirstName, etc..
tblGroupHotels - Group Code, HotelID, CheckIn, CheckOut, etc.
tblGroupActivities - Group Code, ActivityID, ActivityDate, ActivityLocation, etc..
So, instead of assigning a different Group Code to each activity, you keep the activities separate under a single Group Code.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
On Sep 15, 2015, at 1:37 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Dear All,
Please see my question below this table. Thanks.
Group Code
Overnight City
Checkin
Checkout
HotelID
Hotel
GR-0050
Paris
01-Jan-2016
02-Jan-2016
20
Hotel XX
GR-0058
Paris
02-Jan-2016
03-Jan-2016
20
Hotel XX
GR-0066
Paris
03-Jan-2016
04-Jan-2016
20
Hotel XX
My question is:
In these three days I arrange our guests in the same hotel in Paris, is that possible to group these three days into one, so that I can send one request to Hotel XX. And when the hotel offers us the rate that we can confirm, how can we sum up the total amount for these three days? The reason that I didn't make them one record with three days is that in the main itinerary, the group will visit different area by coach. We need the three group code to arrange other things. Hope I didn't confuse you. Please help.
Best Regards,
Kevin
Regards,
Kevin Zhao
[Non-text portions of this message have been removed]
------------------------------------
Posted by: "zhaoliqingoffice@163.com" <zhaoliqingoffice@163.com>
------------------------------------
------------------------------------
Yahoo Groups Links
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (22) |
Tidak ada komentar:
Posting Komentar