Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-10-2018, 05:59 AM   #1
Danick
Newly Registered User
 
Join Date: Sep 2008
Posts: 224
Thanks: 35
Thanked 2 Times in 2 Posts
Danick is on a distinguished road
Travel Itinerary Report

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?
Attached Images
File Type: jpg Travel Itinerary Relationships.jpg (68.9 KB, 20 views)

Danick is offline   Reply With Quote
Old 08-10-2018, 06:56 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 2,995
Thanks: 0
Thanked 657 Times in 642 Posts
Ranman256 will become famous soon enough
Re: Travel Itinerary Report

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.
Ranman256 is offline   Reply With Quote
Old 08-10-2018, 07:24 AM   #3
Danick
Newly Registered User
 
Join Date: Sep 2008
Posts: 224
Thanks: 35
Thanked 2 Times in 2 Posts
Danick is on a distinguished road
Re: Travel Itinerary Report

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 by Danick; 08-10-2018 at 07:33 AM.
Danick is offline   Reply With Quote
Old 08-10-2018, 09:13 AM   #4
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,134
Thanks: 268
Thanked 317 Times in 302 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Travel Itinerary Report

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
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 08-13-2018, 06:33 AM   #5
Danick
Newly Registered User
 
Join Date: Sep 2008
Posts: 224
Thanks: 35
Thanked 2 Times in 2 Posts
Danick is on a distinguished road
Re: Travel Itinerary Report

Quote:
Originally Posted by Gasman View Post
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?
Danick is offline   Reply With Quote
Old 08-13-2018, 06:49 AM   #6
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,134
Thanks: 268
Thanked 317 Times in 302 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Travel Itinerary Report

Quote:
Originally Posted by Danick View Post
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.
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Last edited by Gasman; 08-13-2018 at 08:08 AM.
Gasman is offline   Reply With Quote
Old 08-13-2018, 08:35 AM   #7
Danick
Newly Registered User
 
Join Date: Sep 2008
Posts: 224
Thanks: 35
Thanked 2 Times in 2 Posts
Danick is on a distinguished road
Re: Travel Itinerary Report

Quote:
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 is offline   Reply With Quote
Old 08-13-2018, 10:06 AM   #8
Danick
Newly Registered User
 
Join Date: Sep 2008
Posts: 224
Thanks: 35
Thanked 2 Times in 2 Posts
Danick is on a distinguished road
Re: Travel Itinerary Report

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?
Danick is offline   Reply With Quote
Old 08-13-2018, 10:29 AM   #9
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,134
Thanks: 268
Thanked 317 Times in 302 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Travel Itinerary Report

Don't bother with the last query as you can do all that in the report.

__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Itinerary WhiskyLima Reports 1 07-18-2016 02:34 AM
Add sub form in Report to calculate (discount, Travel allowance...) Zinger Reports 1 10-22-2015 12:58 PM
How to create an itinerary DB? merika General 4 06-20-2012 10:04 AM
Travel Itinerary Database xraive Theory and practice of database design 2 06-02-2009 04:38 PM
Travel KenHigg The Watercooler 25 03-23-2008 06:20 AM




All times are GMT -8. The time now is 08:09 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World