Display Counts of records per day

[Kay]

Hi
I need to display the total count of records added to 3 different
tables and display the results by day(how many were added each day). I
have created 2 queries as shown below. The second query (q22) looks at
the first (q2) to get its results. The problem is when searching
between say a period of last 3 days (from 19th to 21st) it displays the
following results:
XDate Jobs
0 18
19/03/2006 4
20/03/2006 8
21/03/2006 6

The 18 jobs next to xdate(0) are meant to be part of the 3 days (19th
to 21st). I think(not sure) that it is only looking up the count from
the one table and the remaining 18 are from the other 2 tables.

Any help is very much appreciated!

First query (q2)
SELECT MAX([Date]) AS XDate,
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 [Date]

UNION ALL
SELECT MAX([Date]) AS XDate,
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 [Date]

UNION ALL SELECT MAX([Date]) AS XDate,
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 [Date];

Second query (q22)

SELECT MIN([Q2].[XDate]) AS Dates,
Sum([Q2].[Current]+[Q2].[Bookings]+[Q2].[Contracts]) AS Jobs
FROM Q2
GROUP BY [Q2].[XDate]
ORDER BY [Q2].[XDate]

 

[Vincent Johns]

Kay,

I wasn't able to reproduce your problem. Although I didn't have your
sample data available, I generated some of my own, as shown in the
following Tables:

[Current Jobs] Table Datasheet View:

Current Jobs_ID Date
--------------- ---------
-1959288129 3/21/2006
-1938201336 3/21/2006
-1785019958 3/20/2006
-1607286812 3/19/2006
-1536575993 3/20/2006
-1461143374 3/19/2006
-1447086700 3/21/2006
-983668770 3/20/2006
-876951951 3/20/2006
-645999056 3/20/2006
-602148247 3/21/2006
-111647925 3/20/2006
202098573 3/20/2006
435417718 3/21/2006
1179816725 3/19/2006
1456442499 3/21/2006
1915066447 3/19/2006
2128562492 3/20/2006

[Bookings] Table Datasheet View:

Current Jobs_ID Date
--------------- ---------
-983668770 3/20/2006
-876951951 3/20/2006
-645999056 3/20/2006
-602148247 3/21/2006
-111647925 3/20/2006
202098573 3/20/2006
435417718 3/21/2006
1179816725 3/19/2006
1456442499 3/21/2006
1915066447 3/19/2006
2128562492 3/20/2006

[Contract Jobs] Table Datasheet View:

Current Jobs_ID Date
--------------- ---------
-1959288129 3/21/2006
-1938201336 3/21/2006
-1785019958 3/20/2006
-1607286812 3/19/2006
-1536575993 3/20/2006
-1461143374 3/19/2006
-1447086700 3/21/2006
-983668770 3/20/2006
-876951951 3/20/2006

Running your [q22] Query gave me what looked like correct totals. (I
was using Access 2000; perhaps you have a different version.) I think
that your Max() function was kind of nullified by the GROUP BY on the
same field, however. If Max() gets only one value, or three copies of
one value, that's the value it will return.

[q22] Query Datasheet View:

Dates Jobs
---------- ----
3/19/2006 8
3/20/2006 18
3/21/2006 12

Anyway, looking at your Queries, I thought that there was some
redundancy there (same expression popping up multiple times), so that if
you ever wanted to edit, say the prompt for one of the parameters, you'd
have to edit it several times. So I trimmed down the Union Query to
look like this:

[Q_110_CombinedBookings] SQL:

SELECT [Date] AS XDate,
"J" as TableName, COUNT(*) AS CountOfRecords
FROM [Current Jobs]
GROUP BY [Date]

UNION ALL
SELECT [Date], "B", COUNT(*)
FROM Bookings
GROUP BY [Date]

UNION ALL
SELECT [Date], "T", COUNT(*)
FROM [Contract Jobs]
GROUP BY [Date]

ORDER BY [XDate], TableName ASC;


Running this Query produces the following results. Instead of using a
separate field for each Table, I just added a field that identifies
which Table a given [CountOfRecords] comes from.

[Q_110_CombinedBookings] Query Datasheet View:

XDate TableName CountOfRecords
--------- --------- --------------
3/19/2006 B 2
3/19/2006 J 4
3/19/2006 T 2
3/20/2006 B 6
3/20/2006 J 8
3/20/2006 T 4
3/21/2006 B 3
3/21/2006 J 6
3/21/2006 T 3

Having collected the totals (which I assume you'd never want to look at,
at this stage), I used another Query to select only those in the desired
date range.

[Q_120_FilteredByDate] SQL:

SELECT Q1.*
FROM Q_110_CombinedBookings AS Q1
WHERE (((Q1.XDate)
Between [Start Date (dd/mm/yyyy) ]
And [End Date (dd/mm/yyyy) ]));

Since I used dates of 1 March and 31 March, the results were the same as
before, but they can vary if you use other dates.

[Q_120_FilteredByDate] Query Datasheet View:

XDate TableName CountOfRecords
--------- --------- --------------
3/19/2006 B 2
... (same as above)

Having counted all the records, I used Sum() to add those occurring on
each date.

[Q_130_Summary] SQL:

SELECT Q2.XDate AS Dates,
Sum(Q2.CountOfRecords) AS Jobs
FROM Q_120_FilteredByDate AS Q2
GROUP BY Q2.XDate
ORDER BY Q2.XDate;

I changed the format of [Dates] to "d/m/yyyy" for this Query, to match
the format in your message. With suitable system settings, of course,
all of your dates will be displayed in this format.

[Q_130_Summary] Query Datasheet View:

Dates Jobs
--------- ----
19/3/2006 8
20/3/2006 18
21/3/2006 12

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Hi
I need to display the total count of records added to 3 different
tables and display the results by day(how many were added each day). I
have created 2 queries as shown below. The second query (q22) looks at
the first (q2) to get its results. The problem is when searching
between say a period of last 3 days (from 19th to 21st) it displays the
following results:
XDate Jobs
0 18
19/03/2006 4
20/03/2006 8
21/03/2006 6

The 18 jobs next to xdate(0) are meant to be part of the 3 days (19th
to 21st). I think(not sure) that it is only looking up the count from
the one table and the remaining 18 are from the other 2 tables.

Any help is very much appreciated!

First query (q2)
SELECT MAX([Date]) AS XDate,
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 [Date]

UNION ALL
SELECT MAX([Date]) AS XDate,
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 [Date]

UNION ALL SELECT MAX([Date]) AS XDate,
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 [Date];

Second query (q22)

SELECT MIN([Q2].[XDate]) AS Dates,
Sum([Q2].[Current]+[Q2].[Bookings]+[Q2].[Contracts]) AS Jobs
FROM Q2
GROUP BY [Q2].[XDate]
ORDER BY [Q2].[XDate]

 

[Kay]

Hi Vincent

This query is in a totally different format from mine but as long as it
does the job, who cares.
Thanks very much mate beleive me this help is really appreciated.
Thanks very much for your time and effort..........THANKYOU

 

[Vincent Johns]

Kay,

Yes, mine was definitely different. I figured that, since your Query,
in its existing format, seemed to work OK, there was no real need to fix
it! But my revised versions were intended to do (about) the same thing,
but be easier to maintain and modify if necessary.

Good luck with your project!

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.