Query by days

  • Thread starter Thread starter Naz
  • Start date Start date
N

Naz

Hello all

I have a 2 tables that contains the fields
Table1
EmployeID
EmployeeName

Table2
EmployeeID
AbsenceStartDate
AbsenceEndDate
NoOfDaysAbsent
Reason (Sickness, dependecy)

How can i create a query that when i enter a start date and end date i can
get the following output, assuming the date i chose were 01/01/2008 -
07/01/08, the dates need to show even if there is nothing recorded for the
date.

01/01/08 02/01/08 03/01/08
........etc
EmployeeID1 & Name S D
EmployeeID2 & Name S
EmployeeID3 & Name S

I need to be able to output it to excel in the same format which i can do,
but i can't get my head round the query, i've tried a query, a crosstab, but
with no luck


Any help would be greatly appreciated
 
I would create a table of date tblDates with a single date field [TheDate].
Add all the dates you will ever need to this table.

Create a query with Table2 and tblDates with no joins and set the criteria
under TheDate to:
Between [AbsenceStartDate] AND [AbsenceEndDate]

Then create a crosstab query with TheDate as the Column Heading, Employee as
the Row Heading, and Min of Reason as the Value.
 
Back
Top