K
Kay
Hi
I have 2 queries. The second query uses the first query to select the
total jobs for each driver. What I cannot do is to get the query to
prompt for start and end date so that the selected records fall between
the two dates. My first query is as follows:
SELECT [Driver ID], MAX([Date]) AS XDate, 0 AS YDate, 0 AS ZDate,
COUNT(*) AS Current, 0 AS Bookings, 0 AS Contracts
FROM [Current Jobs]
GROUP BY [Driver ID]
UNION ALL
SELECT [Driver ID], 0 AS XDate, MAX([Date]) AS YDate, 0 AS ZDate,
0 AS Current, COUNT(*) AS Bookings, 0 AS Contracts
FROM Bookings
GROUP BY [Driver ID]
UNION ALL SELECT [Driver ID], 0 AS XDate, 0 AS YDate, MAX([Date]) AS
ZDate,
0 AS Current, 0 AS Bookings, COUNT(*) AS Contracts
FROM [Contract Jobs]
GROUP BY [Driver ID];
and second:
SELECT [Q1].[Driver ID], [Q1].[Date],
Sum([Q1].[Current]+[Q1].[Bookings]+[Q1].[Contracts]) AS Jobs
FROM Q1
WHERE Date Between [Start] and [End]
GROUP BY [Q1].[Driver ID]
ORDER BY [Q1].[Driver ID];
I cannot get the second table to recognise the dates from the first.
Any help will be much appreciated
I have 2 queries. The second query uses the first query to select the
total jobs for each driver. What I cannot do is to get the query to
prompt for start and end date so that the selected records fall between
the two dates. My first query is as follows:
SELECT [Driver ID], MAX([Date]) AS XDate, 0 AS YDate, 0 AS ZDate,
COUNT(*) AS Current, 0 AS Bookings, 0 AS Contracts
FROM [Current Jobs]
GROUP BY [Driver ID]
UNION ALL
SELECT [Driver ID], 0 AS XDate, MAX([Date]) AS YDate, 0 AS ZDate,
0 AS Current, COUNT(*) AS Bookings, 0 AS Contracts
FROM Bookings
GROUP BY [Driver ID]
UNION ALL SELECT [Driver ID], 0 AS XDate, 0 AS YDate, MAX([Date]) AS
ZDate,
0 AS Current, 0 AS Bookings, COUNT(*) AS Contracts
FROM [Contract Jobs]
GROUP BY [Driver ID];
and second:
SELECT [Q1].[Driver ID], [Q1].[Date],
Sum([Q1].[Current]+[Q1].[Bookings]+[Q1].[Contracts]) AS Jobs
FROM Q1
WHERE Date Between [Start] and [End]
GROUP BY [Q1].[Driver ID]
ORDER BY [Q1].[Driver ID];
I cannot get the second table to recognise the dates from the first.
Any help will be much appreciated