Reporting - Breakdown date range output

[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

 

[Duane Hookom]

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.

 

[Jackie Reyes via AccessMonster.com]

Duanne, thank you. Is there an easy way to create tblDate rather than
manually populating it one field at a time?


Jackie

 

[Duane Hookom]

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.

 

[Jackie Reyes via AccessMonster.com]

Thank you so much...worked perfectly!