Combining Information from multiple queries

[gavlap]

I'm not an advanced user of MS Access but have managed to set up a database which carrys out most of the processes we need it for.

My company is a Travel Agency, and the database is used to manage client enquiries. We upload the enquiries from our website in to an Enquiry_Table. From there a Consultant will put their name against a new enquiry and it will then assign that enquiry to them. I have a query where they can view all the enquiries they're working on until it either becomes a 'booked' enquiry or 'closed' enquiry.

I have created other tables which are connected to that enquiry so consultants can add notes which are stored in the Notes_Table, they can add passengers that are travelling on that holiday which are stored in a Pssengers_Table, and the they can create quotes for the client which are stored in the Quotes_Table.

Now the quotes is where I'm having the problem and I just can't get my head around it. I have come up with a solution which works but it isn't ideal so I'm hoping someone can help.

Basically when we create a quote it can have several flights, hotels, trains and car transfers all on one quote. At the moment they load them on seperately so load all the flights on which go in to a Flights_Table which is connected to the quote by a unique Quote_Ref, hotels are loaded on in to a Hotels_Table etc.

My problem is that when they email a Quote to a client using a report the only way I've found to do it is spliting the information up. Ideally we would like it to look like an itinerary so it's all in order. eg


12/03/2012 Flight - Depart London 07:00 / Arrive New York 15:00 (Flight BA235)

12/03/2012 Hotel - 5 Nights Hilton Time Square (Double Room)

17/03/2012 Train - Depart New York to Washington (First Class)

17/03/2012 Hotel - 3 Nights Sheraton Downtown (Double Room)

20/03/2012 Flight - Depart Washington 19:00 / Arrive London 07:00 (Flight BA231)


Instead on our quotes we have all the flights at the top of the quote, then a seperate section showing all the hotels, then all the trains as I can't combine the different tables/queries. Is there a way around this?

I thought about using one table to store the information however the different table I use at the moment have different fields so not sure how thi would work.



Any help would be appreciated.

Thanks in advance

[June7]

Not easy.

Would need a field in tables for a number value that can be used as criteria to order the records. The first record from flights would be 1, the first record from hotels would be 2, the first record from trains would be 3, the second record from hotels would be 4, and the second record from flights would be 5, etc. Then need a UNION query to get the various tables arranged into one dataset.

[gavlap]

you're a star - it worked :-)Sorry for late reply but be trying various things to get it working the best way but it's perfect.

Added a time field too so pickup time for car, depart time for flight, train etc, hotels done at 23:59 as always the last thing which puts it all in to order.

Had to do lots of expressions to combine fields in to sentances so all the querys had the same number of columns but its great so pleased so thanks :-)