crosstab query failed emergency help

[balu]

dears,
here is the table and report requirement any one please help as im going to
face the exam on 10/02/08 13.00 hrs please .b'cse its my employment .
here is the table Employeename . text
employeeid . number
fromdate . date/ time format (dd/mm/yy),
todate . date/time format(dd/mm/yy)
duty lookup >( absent or present)
REPORT: Required in the manner is say for eg,in excel format
month in one column(a3) as jan,feb,march,april,..til december
date in top row(b2) that is 31 days like 01,02,03,04,...31
employee present or absent is required for individual day
for the perticular month and date right below the perticular day & aginest
month
i beg all my community friends to do best for me please
thanking you.

 

[Duane Hookom]

How far have you gotten with this problem? Have you looked up crosstab in
help? Do you know how to return the month name and day of month based on a
date value?

 

[balu]

yes sir i know that functions to get monthname() and date . im formating in
the query property as mm/yy and dd it is simply giving me in the required
format .
(2)when im doing in select query it is giving all necessary data under 3
fields as month/year , date , attendence.
(3) to get every date of the month i used sql maketable query
dateserial(year(date(),1,serialnumber) serial is the one table with field
serialnumber containing numbers from 1 to 365 its giving me full year dates
in the format dd/mm/yy and innerjoin with above table
where im failing is to transpose the date and attendence fields aginest
groupby of month/year field
the requirement of report is like this
DATE>> 01|02|03|04|05|06|07|08|09|10|....|31|
MONTH!|
10/08 |P|AB|P|P|P|.......
11/08 |A|AB|P|........
sir im going for exam now bless me, i decided to create an excel object
and copy the month/date , and attendence data to it using copyfromrecordset
method and copy and transpose the column there and then hide or delet the
transposed attendence column however im doing absolutly rubbish bless me good
luck next time
how to do in access?

 

[Duane Hookom]

I don't care for giving out possible solutions for test questions but since
the test is over (I assume) and others might benefit...

This can be done in Access fairly efficiently. I expect you want to fill in
the dates between the from and to dates. I generally do this with a table of
all unique dates containing a single date field with date values as required
(tblDates.CalendarDate). You can then create a "cartesian" query of both
tables (no joins) with a criteria under [CalendarDate] field set to Between
[FromDate] and [ToDate].

Then use this query as a crosstab where the CalendarDate field is converted
to a day of the month value for the Column Heading. The Row Headings would be
the Employee and the Month of CalendarDate. The Value in the crosstab would
be the FirstOfDuty to display "Absent" or "Present".

TRANSFORM First(tblSchedFromTo.Duty) AS FirstOfDuty
SELECT tblSchedFromTo.EmployeeID, Month([CalendarDate]) AS Mth
FROM tblCalendar, tblSchedFromTo
WHERE (((tblCalendar.CalendarDate) Between [FromDate] And [ToDate]))
GROUP BY tblSchedFromTo.EmployeeID, Month([CalendarDate])
PIVOT Day([CalendarDate]);