Travel Itinerary Report (1 Viewer)

Danick

Registered User.
Local time
Today, 14:39
Joined
Sep 23, 2008
Messages
351
Hello,
I'm trying to make a simple Customer Meeting Travel Itinerary database. I've attached a screenshot of the relationships to get an idea. It's pretty simple where you create meeting, flight, hotel, taxi dates and times. The form with the subform is working very well.

The problem I'm having is putting it all in a report and trying to Group/Sort the report by dates and times from different tables. I would like the report to group by day of the week (Starting on the first day of earliest event) and sort those days by the date time of the events.

The events days times from different tables are meeting date and time, flight date and time, hotel date and check in time, etc.

Is it possible to put it all this together in one report?
 

Attachments

  • Travel Itinerary Relationships.jpg
    Travel Itinerary Relationships.jpg
    68.9 KB · Views: 89

Ranman256

Well-known member
Local time
Today, 14:39
Joined
Apr 9, 2015
Messages
4,339
you can always use queries to put the data into a 'report' table, then run the report off that. Use a macro to:
1. empty rpt table
2. append queries to add this customer data to the report table.
3. report will sort and display this data.
 

Danick

Registered User.
Local time
Today, 14:39
Joined
Sep 23, 2008
Messages
351
The problem with that is that the names of the fields are different. In the Meeting table, the event would be [mtgDate] and [mtgTime]. In the Transportation Table, the departure events are [tDeptDate] and [tDeptTime].

So I would have to someone put those fields (ie mtgDate and tDeptDate) into the same field so I can sort them. Then do the same for the time fields. Not sure how to do that...

This is to some how have a report that looks like this:

Monday 13, 2018 (this comes from transportation table)
7:30 AM tDeptTime - Flight XYZ - From - To (this comes from transportation table)
10:00 mtg Time - Customer (this comes from meeting table)
12:00 Taxi YellowCab (this comes from transportation table)
2:00 PM tDeptTime - Flight ZYX - From - To (this comes from transportation table)
4:00 PM Hotel Z (this comes from hotel table)
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 18:39
Joined
Sep 21, 2011
Messages
14,046
When you retrieve data, you can rename the fields.

Code:
Select Meeting.mtgDate AS TranDate, Meeting.mtgTime AS TranTime
Select Transportation.tDeptDate AS TranDate, Transportation.tDeptTime AS TranTime

HTH
 

Danick

Registered User.
Local time
Today, 14:39
Joined
Sep 23, 2008
Messages
351
When you retrieve data, you can rename the fields.

Code:
Select Meeting.mtgDate AS TranDate, Meeting.mtgTime AS TranTime
Select Transportation.tDeptDate AS TranDate, Transportation.tDeptTime AS TranTime

HTH

So correct me if I'm wrong. But since the information is coming from different tables, I would have to make three different append queries, call them the fields the same name and then run the three append queries to append to the one report table so I can view that table in a report. Is that right?
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:39
Joined
Sep 21, 2011
Messages
14,046
So correct me if I'm wrong. But since the information is coming from different tables, I would have to make three different append queries, call them the fields the same name and then run the three append queries to append to the one report table so I can view that table in a report. Is that right?

If that is your table structure, then Yes, though you could use a UNION statement to combine them all into one query. You do not necessarily need a table for the report.

If you were to post your relationship diagram, no doubt the experts will be able to advise better.
 
Last edited:

Danick

Registered User.
Local time
Today, 14:39
Joined
Sep 23, 2008
Messages
351
If you were to post your relationship diagram, no doubt the experts will be able to advise better.

It's in the first post
 

Danick

Registered User.
Local time
Today, 14:39
Joined
Sep 23, 2008
Messages
351
OK - I've got it working. But it sure does look like a lot of steps to produce an itinerary type report in chronological order. Maybe I'm missing something.

I created 3 Append Queries to a report table called "TranReportTABLE"

One for the hotel
Code:
INSERT INTO TranReportTABLE ( TAID, TranDate, TranTime, TranName, TranLocation )
SELECT tblHotel.TAID, tblHotel.hArrivalDate AS TranDate, tblHotel.hChkInTime AS TranTime, tblHotel.hName AS TranName, tblHotel.hAddress
FROM tblHotel;

One of the transportation:
Code:
INSERT INTO TranReportTABLE ( TAID, TranDate, TranTime, TranName, FltNo, [From], TranLocation, [Arrival Time] )
SELECT tblTransportation.TAID, tblTransportation.tDeptDate AS TranDate, tblTransportation.tDeptTime AS TranTime, tblTransportation.tCarrier, tblTransportation.tRef, tblTransportation.tFrom, tblTransportation.tTo, tblTransportation.tArrivalTime
FROM tblTravelAuthorization LEFT JOIN tblTransportation ON tblTravelAuthorization.TAID = tblTransportation.TAID;

And one for the meeting:
Code:
INSERT INTO TranReportTABLE ( TAID, TranDate, TranTime, TranName, TranLocation )
SELECT tblMeeting.TAID, tblMeeting.mtgDate AS TranDate, tblMeeting.mtgTime AS TranTime, tblCompany.CompanyName AS TranName, tblMeeting.mtgLocation AS TranLoc
FROM tblCompany INNER JOIN tblMeeting ON tblCompany.CompanyID = tblMeeting.CompanyID;

Then I have a query to sort the data by date and time and the report is created based on this query. Now I have to create a button to:
1) Turn all warning messages off
2) Delete all the data in the table
3) Run 3 append queries to that report table
4) Preview the report.

Anyone have a better, shorter way to do this?
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:39
Joined
Sep 21, 2011
Messages
14,046
Don't bother with the last query as you can do all that in the report.
 

Users who are viewing this thread

Top Bottom