Display count by day

[Kay]

Hi

I have created a query that counts the records between 2 dates that are
entered by the user. I get the correct output as a single number but I
want it to give me an output on a daily basis. For example if I want a
count for the last week, I want it to display the count for Monday,
Tueaday, wednesday........etc
Ive used 2queries. Q1 extracts all data and ive used many queris that
use Q1 to get their results but am having problems with this particular
one. The code in my second query is simple as follows:

SELECT Sum([Q1].[Current]+[Q1].[Bookings]+[Q1].[Contracts]) AS Jobs
FROM Q1;
(This query as I said gets me a single number that is the sum of all
the records(jobs))

Any help is much appreciated

 

[John Spencer]

It would be nice to see query one so we have some idea of your fields and
tables. Generically, the query might look like.

SELECT SomeDateField, Count(SomeDateField) as CountOfRecordsOnDate
FROM YourTable
WHERE SomeDateField Between #1/1/06# and #1/31/06#
GROUP BY SomeDateField

Note that the query uses a where clause, not a having clause to get the date
range.

 

[Kay]

Here is my query one
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]
WHERE [Date] BETWEEN [Start Date (dd/mm/yyyy) ] AND [End Date
(dd/mm/yyyy) ]
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
WHERE [Date] BETWEEN [Start Date (dd/mm/yyyy) ] AND [End Date
(dd/mm/yyyy) ]
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]
WHERE [Date] BETWEEN [Start Date (dd/mm/yyyy) ] AND [End Date
(dd/mm/yyyy) ]
GROUP BY [Driver ID];

Note there is extra code in this that is not required by the query in
question and that is because I have created other queries that lookup
from this Query

 

[Guest]

SELECT [YourDateField], Sum([Q1].[Current]+[Q1].[Bookings]+[Q1].[Contracts])
AS Jobs
FROM Q1;

 

[John Spencer]

Well, that query only returns one date per driver per query and it has three
different dates that are returned. So the question becomes which date do you
want to use to get the count.

SELECT XDATE,
Count(Xdate) as CountXDates,
Sum([Q1].[Current]+[Q1].[Bookings]+[Q1].[Contracts]) AS Jobs
FROM Q1
GROUP BY XDate

 

[Kay]

How can I get it to group x,y and z dates and give the answer like
date 8
date 5
date 14 etc

 

[John Spencer]

You would have to rewrite query one as a revised query. I've actually put
more information in the revised union query than you need. You really only
need Max([Date]) in each of the component queries. For your information,
the column names that are returned are the ones that are in the first
component query. So in subsequent queries, you would be able to use Driver
Id, XDate, DataType, and Current as your field names.

I'm not sure you want Driver ID in these queries. It depends on what you
want to count and how you want to count it. Right now you are counting the
combinations of Driver and Datatypes for each specific date. For instance,
these 3 rowsDate Driver Type
Jan 1 2 Current
Jan 1 2 Bookings
Jan 3 8 Contracts
Would return
Jan 1 2
Jan 3 1


SELECT [Driver ID], MAX([Date]) AS XDate,
"Current" as DataType,
COUNT(*) AS Current
FROM [Current Jobs]
WHERE [Date] BETWEEN [Start Date (dd/mm/yyyy) ] AND [End Date(dd/mm/yyyy) ]
GROUP BY [Driver ID]
UNION ALL
SELECT [Driver ID], MAX([Date]) AS YDate
"Bookings" as DataType,
COUNT(*) AS Bookings
FROM Bookings
WHERE [Date] BETWEEN [Start Date (dd/mm/yyyy) ] AND [End Date
(dd/mm/yyyy) ]
GROUP BY [Driver ID]
UNION ALL
SELECT [Driver ID], MAX([Date]) AS ZDate,
"Contracts" as DataType,
COUNT(*) AS Contracts
FROM [Contract Jobs]
WHERE [Date] BETWEEN [Start Date (dd/mm/yyyy) ] AND [End Date
(dd/mm/yyyy) ]
GROUP BY [Driver ID];

Using the revised query you could get
SELECT XDate, Count(XDate) as CountDates
FROM [RevisedQuery]
GROUP BY XDate
ORDER BY XDate