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];
> Hi
> Im creating a query for my chart to lookup a count of records from 3
[quoted text clipped - 20 lines]
>
> Any help will be much appreciated
Kay - 20 Mar 2006 18:00 GMT
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 - 20 Mar 2006 18:10 GMT
The sub queries have already done the counting, the top query should just
add these up.
> 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