count from multiple tables

[Kay]

Hi
Im creating a query for my chart to lookup a count of records from 3
different tables using the Driver ID field. I have got the following
code but it comes up with the following error 'You tried to execute a
query that does not include the specified expression 'Driver ID' as
part of an aggregate funtion'. My code is a follows:

SELECT Count(*) AS Expr1
FROM (SELECT Count(*) AS Expr1, [Driver ID]
FROM [Current Jobs]


UNION ALL
SELECT COUNT(*) AS Expr1, [Driver ID]
FROM [Bookings]


UNION ALL
SELECT COUNT(*) AS Expr1, [Driver ID]
FROM [Contract Jobs]) AS query1


WHERE ((([Date]) Between [From Date] And [To Date]))
GROUP BY [Driver ID]
ORDER BY [Driver ID];


Any help will be much appreciated

 

[Darren]

First, Driver id is not selected in your top query to group by.
Second Driver Id I believe needs to have a group by in all of your sub
queries
Third, I think you actually want to sum the top level, not count

SELECT Sum(Expr1) AS Expr1, [Driver ID]
FROM
(
SELECT Count(*) AS Expr1, [Driver ID]
FROM [Current Jobs]
WHERE ((([Date]) Between [From Date] And [To Date]))
GROUP BY [Driver ID]

UNION ALL
SELECT COUNT(*) AS Expr1, [Driver ID]
FROM [Bookings]
WHERE ((([Date]) Between [From Date] And [To Date]))
GROUP BY [Driver ID]

UNION ALL
SELECT COUNT(*) AS Expr1, [Driver ID]
FROM [Contract Jobs]
WHERE ((([Date]) Between [From Date] And [To Date]))
GROUP BY [Driver ID]
)
GROUP BY [Driver ID]
ORDER BY [Driver ID];

 

[Kay]

Hi
Doesnt the sum option give you a total. The driver ID refers to many
drivers. So I need to display each driver ID and a number next to it
showing the no of records booked under that ID

Any Suggestions

 

[Darren]

The sub queries have already done the counting, the top query should just
add these up.