which type of query

[Kay]

Hi

Ive created a database for a taxi company. They have 3 types of
bookings, Current bookings, later bookings and contract bookings. Each
job booking is stored in a different table. I want to create a
parameter query so the user can select 2 date(from and to) and I want
to display all the records from these 3 tables that fall between those
date.

Any help will be appreciated

 

[Allen Browne]

Kay, you will certainly be better off if you can put all the bookings into
one table. It should be easy enough to add an extra field so you can tell
which of the 3 types the record is (if that's appropriate.)

If you cannot do that, you can create a UNION query that combines the
records from multiple tables. See help on UNION: it's basically 3 SELECT
query statements combined into 1, e.g.:
SELECT BookingDate, CustomerID FROM Table1
UNION ALL
SELECT BookingDate, CustomerID FROM Table2
UNION ALL
SELECT BookingDate, CustomerID FROM Table3;

 

[John Spencer]

If your three tables are the same structure then you should have them all in
one table with a field to allow you to distinguish the type of bookings.

To work with your tables as they exist, you need to use a UNION query to
combine the three into one. Generic SQL follows
SELECT * FROM
(SELECT <<your Field list>>
FROM CurrentBookings
UNION ALL
SELECT <<your Field list>>
FROM LaterBookings
UNION ALL
SELECT <<your Field list>>
FROM ContractBookings
UNION ALL) as qAllBookings
WHERE [TheDateField] Between [Start Date] and [End Date]

You may have to do this in stages.
First stage, make the union query and save it as qAllBookings
Use qAllBookings as the "table" in a second query and apply your criteria to
that.

 

[Kay]

Thanks very much guys, managed to get it working with the following
code

SELECT [Date],[Time],[Door
No/Name],[Place],[Street],[area],[Destination Street],[Quote (if
any)],[Driver ID],[Operator ID],[Notes],[Status]
FROM [Current Jobs]


UNION ALL
SELECT [Date],[Time],[Door
No/Name],[Place],[Street],[area],[Destination Street],[Quote (if
any)],[Driver ID],[Operator ID],[Notes],[Status]
FROM [Bookings]


UNION ALL SELECT [Date],[Time],[Door
No/Name],[Place],[Street],[area],[Destination Street],[Quote (if
any)],[Driver ID],[Operator ID],[Notes],[Status]
FROM [Contract Jobs]
WHERE [Date] between [From Date] and [To Date];