creating report

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:


I am asked to generate a report that says, by day, how many people were out
for various reasons (we have 9). I do not 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

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

--
Build a little, test a little.

First thing I would do is create a small calendar table listing all the

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
== Under EndDate enter
== 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?

Thank you!

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

Thanks again!

The first thing you will need to do is introduce a means of returning every

The first thing you will need to do is introduce a means of returning every day
over a period of time as these data are not included in the table as it
stands.  This means creating a table Calendar with a date/time column calDate
which as one row for every day over a period of time.  As it happens I have
written a function to do this, so paste this into a standard module in the
database:

Public Function MakeCalendar_DAO(strtable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)

' Accepts:  Name of calendar table to be created: String.
'           Start date for calendar: DateTime.
'           End date for calendar: DateTime.
'           Days of week to be included in calendar
'           as value list, e,g 2,3,4,5,6 for Mon-Fri
'           (use 0 to include all days of week)

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long

Set dbs = CurrentDb

' does table exist? If so get user confirmation to delete it
For Each tdf In dbs.TableDefs
If tdf.Name = strtable Then
If MsgBox("Replace existing table: " & _
strtable & "?", vbYesNo + vbQuestion, _
strSQL = "DROP TABLE " & strtable
dbs.Execute strSQL
Exit For
Else
Exit Function
End If
End If
Next tdf

' create new table
strSQL = "CREATE TABLE " & strtable & _
dbs.Execute strSQL

' refresh database window
Application.RefreshDatabaseWindow

If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strtable & "(calDate) " & _

dbs.Execute strSQL
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strtable & "(calDate) " & _
dbs.Execute strSQL
End If
Next varDay
Next dtmDate
End If

End Function

Make sure you have a reference to the Microsoft DAO Object Library (Tools |
References on the VBA menu bar ??? select the one with the highest version
number).  If you have put the function in a new module save the module under a
different name from the function e.g. mdlCalendar.

To create and fill the table call the function from the debug window (aka
immediate window) ??? press Ctrl-G to open this.  To create a calendar from
2005 to 2015 say, with all days of the week, enter:

MakeCalendar_DAO "Calendar",#2005-01-01#,#2015-12-31#,0

and press Enter.

You can now create a query which joins the Calendar table to your table,
using a LEFT OUTER JOIN so that all dates are returned even if there are no
absences on a day.  The query would be grouped by the date and reason and
would count the number per date/reason.

This query cannot be designed in design view however as it joins the tables
on the calendar date falling between the start and end dates, which cannot be
represented in design view.  So its necessary to create it in SQL view.  To
do this open the query designed and switch to SQL view and then paste in the
following, and change each instance of YourTable in the SQL statement to your
real table name.  If the table name has spaces or other special characters in

Create a SELECT query using your table and open in design view, click on VIEW-

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.

Note that Karl's solution gives you the total number of people/days absenceper

Note that Karl's solution gives you the total number of people/days absence
per reason, whereas John's and mine (which are near identical)  give the
number of absentees per day per reason.  Which is it you want?

Ken Sheridan
Stafford, England


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200911/1