Problem with UNION Query

[Kay]

Hi

I am creating a database for a taxi company. The company has pickups
arranged into 3 categories, Current Pickups, Bookings and Contract
Pickups. I have created a separate table for each type of pickup.
I want to search pickups by entering the DRIVER ID, FROM DATE and TO
DATE parameters. This should search all 3 tables for pickups made by
the driver id entered and between the dates entered.
I have created a union query that asks me to enter the driver id, from
date and to date but it returns all the records from the 3 table and
not the required records according to the parameters entered. Can
someone please help. The code I have enetered is as follows

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

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

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

WHERE [Driver ID] = [Enter Driver ID]
AND
[Date] between [From Date] and [To Date];

 

[Tom Ellison]

Dear Kay:

May I recommend this:

SELECT "Current Jobs" AS Source, [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 "Bookings" AS Source, [Date], [Time],
[Door No/Name], [Place], [Street], [area],
[Destination Street], [Quote (if any)], [Driver ID],
[Operator ID], [Notes], [Status]
FROM [Bookings]

UNION ALL

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

I have added a new column "Source". Your application may not need this, but
it is still useful in debugging, as it identifies from which table each row
comes.

I have changed UNION to UNION ALL. UNION by itself will attempt to remove
duplicates. This is bad for 2 reasons: it takes time, and I don't think
you would want to remove duplicates if they do exist. Not sure, really, you
decide.

Save the above query, and base a new query on this, applying the filtering
you need. Does this help?

Tom Ellison

Hi

I am creating a database for a taxi company. The company has pickups
arranged into 3 categories, Current Pickups, Bookings and Contract
Pickups. I have created a separate table for each type of pickup.
I want to search pickups by entering the DRIVER ID, FROM DATE and TO
DATE parameters. This should search all 3 tables for pickups made by
the driver id entered and between the dates entered.
I have created a union query that asks me to enter the driver id, from
date and to date but it returns all the records from the 3 table and
not the required records according to the parameters entered. Can
someone please help. The code I have enetered is as follows

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

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

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

WHERE [Driver ID] = [Enter Driver ID]
AND
[Date] between [From Date] and [To Date];

 

[Kay]

Hi Tom

I tried that and it still lists all the records but with the extra
source field

 

[Tom Ellison]

Dear Kay:

Does the query that returns these results read:

Select *
FROM MyUnion
WHERE [Driver ID] = [Enter Driver ID]
AND [Date] between [From Date] and [To Date];

Please show what parameters you entered for the 3 prompts, and what values
you have in the results that do not meet the criteria for [Driver ID] and
[Date] as you expect. Do you have results where both {Driver ID] and [Date]
are not what you expected?

In the above, I have assumed you saved the UNION query I suggested as
MyUnion. Substitute any other name you may have used, placing [] around it
if it contains a space or any other character not a letter or digit.

Tom Ellison

 

[Kay]

Tom,
Youre a genius, Got it to work. I didnt use Select * FROM MyUnion.
Thanks very much mate

 

[Tom Ellison]

Dear Kay:

Or just lucky!

Tom Ellison