Hi Kay,
Are you familiar with Union queries? What kind of chart are you attempting
to create? Typically you need at least two fields to build a chart, one for
the label and one for the counts or results. Union queries require a
placeholder for both fields in each section of the union. The names have to
be identical in each section of the query so it can track the names of the
Job Type and add the count of Driver IDs from each table.
It looks like you are not using the field name 'Driver ID' in your Union
query. You are calling the field 'Expr1'. There is not any other field
identified in your example so the when the code gets to the 'group by' it
doesn't recognize the name 'Driver ID'.
Your syntax is also confusing, I don't use 'UNION ALL' in my union queries.
I'm not sure about your parenthesis use in the first section and around the
dates. It seems you may be missing a closing parend or have an extra opening
parend.
I can share my process for creating union queries. First, create the select
query from your first table in design view and make sure it runs with the
results you need for your chart. Then switch the view to SQL and copy the SQL
code and paste it into WordPad. It should look something like:
SELECT "CurrentJobs" AS JobType, Count(tblCurrentJobs.DriverId) AS DriverId
FROM tblCurrentJobs
GROUP BY "CurrentJobs";
Then go back to your query and change back to design view, import the next
table you want to query, Bookings. Make the changes to your Select query in
design view then set the view to SQL. Copy the SQL code and go back to
WordPad and paste the new code at the end of the original code. You will have
to make a couple of adjustments. Add the word 'Union' in front of the second
select statement and move the semi-colon to the end of the query. It should
look like:
SELECT "CurrentJobs" AS JobType, Count(tblCurrentJobs.DriverId) AS DriverID
FROM tblCurrentJobs
GROUP BY "CurrentJobs"
UNION SELECT "Bookings" AS JobType, Count(tblBookings.DriverId) AS DriverID
FROM tblBookings
GROUP BY "Bookings";
Then do the same thing with the third table - Contract Jobs. Your final
union query SQL code should look something like this:
SELECT "CurrentJobs" AS JobType, Count(tblCurrentJobs.DriverId) AS DriverID
FROM tblCurrentJobs
GROUP BY "CurrentJobs"
UNION SELECT "Bookings" AS JobType, Count(tblBookings.DriverId) AS DriverID
FROM tblBookings
GROUP BY "Bookings"
UNION SELECT "ContractJobs" AS JobType, Count(tblContractJobs.DriverId) AS
DriverID
FROM tblContractJobs
GROUP BY "ContractJobs";
Just substitute your actual table names and run the query and you should get
a result similar to this:
JobType DriverID
Bookings 1811
ContractJobs 822
CurrentJobs 364
Now you should have the data needed to create your chart.
Kay said:
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
