Reporting - Breakdown date range output

  • Thread starter Thread starter Jackie Reyes via AccessMonster.com
  • Start date Start date
J

Jackie Reyes via AccessMonster.com

I am trying to create a report from the table below. My goal is to create a
chart that takes a given date, and for each Dept. (e.g., 305, 315, 325,
800, 700, etc., also shown in a column on the chart), checks the range of
data for whether or not that date falls within the "Time In" and "Time Out"
dates, then sums the total for that given date, by Dept. The plot data
would consist of dates (e.g., daily, 5/2004 to present), and a list of how
many days, broken up by Dept:


Dept. TimeIn TimeOut NumberOfDays
305 05/24/04 05/27/04 3
315 05/26/04 05/27/04 1
325 05/28/04 05/30/04 2
800 06/01/05 06/05/05 4
700 06/03/05 06/06/05 3

If I can have an output like below to export in Excel, I then would do an
analysis and be able to easily plot my data into a chart.

Example output:

DATE 305 315 325 800 700
--------- ---------- --------- -------- --------- --------
5/24/04 1 0 0 0 0
5/25/04 1 1 0 0 0
5/26/04 1 1 0 0 0
5/27/04 1 1 0 0 0
5/28/04 0 0 1 0 0
….
6/5/05 0 0 0 1 1
Etc.

Thanking you in advance,
Jackie
 
Create a table of dates (tblDates) with a single field (TheDate) and all
possible dates entered. Then create a crosstab query like:

TRANSFORM Nz(Sum(1),0) AS Expr1
SELECT tblDates.TheDate
FROM tblDates, tblJackie
WHERE (((tblDates.TheDate) Between [TimeIn] And [TimeOut]))
GROUP BY tblDates.TheDate
PIVOT tblJackie.Dept;

This will not show dates where there is no Dept. You would need to use an
outer join to include all dates.
 
Duanne, thank you. Is there an easy way to create tblDate rather than
manually populating it one field at a time?


Jackie
 
I either write a bit of code or just create the dates in an Excel column and
then copy and Paste Append into your table.
 
Back
Top