K
Kay
Hi
I have vreated 2 queries that help me count the amount of records from
3 tables assigned to each driver. The first query is as follows:
SELECT [Driver ID], COUNT(*) AS Current, 0 AS Bookings, 0 AS Contracts
FROM [Current Jobs]
GROUP BY [Driver ID]
UNION ALL
SELECT [Driver ID], 0 AS Current, COUNT(*) AS Bookings, 0 AS Contracts
FROM Bookings
GROUP BY [Driver ID]
UNION ALL SELECT [Driver ID], 0 AS Current, 0 AS Bookings, COUNT(*) AS
Contracts
FROM [Contract Jobs]
GROUP BY [Driver ID];
and second query:
SELECT [Q1].[Driver ID],
Sum([Q1].[Current]+[Q1].[Bookings]+[Q1].[Contracts]) AS Jobs
FROM Q1
GROUP BY [Q1].[Driver ID]
ORDER BY [Q1].[Driver ID];
The second query uses the first to get the following results:
Driver ID Jobs
P01 1
P04 3
P05 1
P06 2
P07 1
P08 2
P10 3
The problem Ive got is I need to select records between 2 dates. Iv
tried the sql statement 'between [start date] and [to date] but cannot
get it to work.
Can any genius help please
I have vreated 2 queries that help me count the amount of records from
3 tables assigned to each driver. The first query is as follows:
SELECT [Driver ID], COUNT(*) AS Current, 0 AS Bookings, 0 AS Contracts
FROM [Current Jobs]
GROUP BY [Driver ID]
UNION ALL
SELECT [Driver ID], 0 AS Current, COUNT(*) AS Bookings, 0 AS Contracts
FROM Bookings
GROUP BY [Driver ID]
UNION ALL SELECT [Driver ID], 0 AS Current, 0 AS Bookings, COUNT(*) AS
Contracts
FROM [Contract Jobs]
GROUP BY [Driver ID];
and second query:
SELECT [Q1].[Driver ID],
Sum([Q1].[Current]+[Q1].[Bookings]+[Q1].[Contracts]) AS Jobs
FROM Q1
GROUP BY [Q1].[Driver ID]
ORDER BY [Q1].[Driver ID];
The second query uses the first to get the following results:
Driver ID Jobs
P01 1
P04 3
P05 1
P06 2
P07 1
P08 2
P10 3
The problem Ive got is I need to select records between 2 dates. Iv
tried the sql statement 'between [start date] and [to date] but cannot
get it to work.
Can any genius help please