Loses Information When Grouped

[Guest]

I have created a database that tracks the daily attendace of employees. The
information in the Table resembles something like this:

NAME DATE STATUS DAY
John, Allen 3-jan-2005 P 3
John, Allen 4-jan-2005 P 4
John, Allen 5-jan-2005 V 5
John, Allen 6-jan-2005 P 6
James, Al 3-jan-2005 X 3
James, Al 4-jan-2005 P 4
James, Al 5-jan-2005 S 5
James, Al 6-jan-2005 P 6

Status describes their attendance eg S- Sick, P - Present etc.

At the end of the month I want to generate a report that looks like this:

NAME 1 2 3 4
5 6
Allen, John P P
V P
James, Al X P
S P

Where the Number Column Headings reprsent the days of the month. My problem
is that it only reads in the STATUS for the first date and leaves all the
others blank. The text box under each number has a fomula similar to this:
IIf([DAY]=1,[STATUS]) (the number one is only used for the example, the colum
with 3 above it would say the same thing except the 1 is change to a 3). I
have a group header on that groups the names together, but for some reason
the status does not change after it reads in the STATUS for the first date.
If I take off my group header it shows all the records (just as you see in
the table format above) with everything correct.

Can someone tell me what I am doing wrong?

 

[Duane Hookom]

Have you tried a crosstab query where the Name is the Row Heading, Day is
the Column Heading, and First of Status is the Value?