creating report

  • Thread starter Thread starter Ashley
  • Start date Start date
A

Ashley

Hello, I am very new at Access so please forgive me.

I have a data table with the following fields to record unproductivity of
workforce:

"SID" "StartDate" "StartTime" "EndDate" "EndTime" "Reason"

I am asked to generate a report that says, by day, how many people were out
for various reasons (we have 9). I don't even know where to begin. Can
someone point me in the right direction, please!?

Thank you~~!!
 
Try this --
SELECT [Reason], Sum(DateDiff("d", [StartDate], [EndDate])+1) AS Days_Absent
FROM YourTable
GROUP BY [Reason];
 
First thing I would do is create a small calendar table listing all the dates
in the range of dates then use that in your query

SELECT C.TheDate, U.Reason, Count(Reason) as TheCount
FROM tblCalendar as C LEFT JOIN Unproductivity as U
ON C.TheDate <= U.EndDate and C.TheDate>=StartDate
WHERE C.TheDate Between #2009-10-01# and #2009-10-31#
GROUP BY C.TheDate, U.Reason

That query CANNOT be built in QUERY design view.

You can build something similar in design view (not as efficient)
== Add both tables to your quiery
== Add TheDate, Reason, SID, StartDate, and EndDate fields to the list of fields
== Add TheDate a second time
== Under the second instance set the criteria
Between #2009-10-01# and #2009-10-31#
== Under StartDate enter
<=TheDate
== Under EndDate enter
=TheDate
Click to expand...
== Select View: Totals from the menu
== Change GROUP BY To WHERE under StartDate, EndDate and second instance of
TheDate
== Change GROUP BY to Count under SID

Hopefully I got all that correct

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thank you!

Where do I put this information? I literally do not know where to even
begin! I'm just sitting here with a database. No idea what to click on.

Thanks again!

KARL DEWEY said:
Try this --
SELECT [Reason], Sum(DateDiff("d", [StartDate], [EndDate])+1) AS Days_Absent
FROM YourTable
GROUP BY [Reason];

--
Build a little, test a little.


Ashley said:
Hello, I am very new at Access so please forgive me.

I have a data table with the following fields to record unproductivity of
workforce:

"SID" "StartDate" "StartTime" "EndDate" "EndTime" "Reason"

I am asked to generate a report that says, by day, how many people were out
for various reasons (we have 9). I don't even know where to begin. Can
someone point me in the right direction, please!?

Thank you~~!!
Click to expand...
Click to expand...
 
Create a SELECT query using your table and open in design view, click on VIEW
- SQL View, paste my post, edit 'YourTable' to your table name.

Save, run.

--
Build a little, test a little.


Ashley said:
Thank you!

Where do I put this information? I literally do not know where to even
begin! I'm just sitting here with a database. No idea what to click on.

Thanks again!

KARL DEWEY said:
Try this --
SELECT [Reason], Sum(DateDiff("d", [StartDate], [EndDate])+1) AS Days_Absent
FROM YourTable
GROUP BY [Reason];

--
Build a little, test a little.


Ashley said:
Hello, I am very new at Access so please forgive me.

I have a data table with the following fields to record unproductivity of
workforce:

"SID" "StartDate" "StartTime" "EndDate" "EndTime" "Reason"

I am asked to generate a report that says, by day, how many people were out
for various reasons (we have 9). I don't even know where to begin. Can
someone point me in the right direction, please!?

Thank you~~!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top