How to put many data rows in one report row ?

[dordije2000]

I am new in ACCESS & SQL and need solution for next problem:

Suppose we have table T1 with next values

Person Date Status

Smith 07/23/2007 A
Regan 04/12/2007 B
Cole 07/24/2007 C
Gigs 01/13/2007 D
Smith 08/14/2007 B
Regan 05/06/2007 C
Gigs 09/03/2007 A
Smith 12/23/2007 A

This table can conatin any Status (among A,B,C,D,E,F, ...), for any Person in any Date in year 2007.
If row exists for person it must have status (A,B,C ...).
One person can have only one row in one date.

Not all dates in year 2007 are in T1, some can be without any data.

I want to generate next report for one month, for example July (07) 2007:

July 2007

Person Day01 Day02 ... Day23 Day24 ... Day31

Smith - - - A - ... -
Regan - - - - - ... -
Cole - - - - C ... -
Gigs - - - - - ... -

In real life there can be more data for other persons in july this is only example.

I need SELECT or other statement(s) which will produce data as input for this report. Or it must be done using some temp table, or ... ?

Thanks in advance.
Dordije.

 

[SkipVought]

Hi,

Check out Crosstab query wizard.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[dhookom]

Check out faq703-5466. You should be able to modify this solution to change the increments from one month to one day.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]