Query for a report (or any other solution)

[Guest]

I have a Main table that houses records with the following ID’sâ€

AW001 AW002 AW003
CA001 CA002 CA003
CG001 CG002 CG003
FP001 FP002 FP003
SC001 SC002 SC003

Each one has a financial value for 2005 and a financial value for 2006

I need a report that will provide the following information

Header row: |Focus area| |# of Projects| |Total 2005| |Total 2006| |Total
Both|

Data Row: AW 3 $x
$y $ x+y
CA 3 $x
$y $x+y
CG 3 $x
$y $x+y
FP 3 $x
$y $x+y
SC 3 $x
$y x+y

Footer row Total Projects total 2005 total 2006 grand
total

 

[Guest]

Each ID has a status (Active-closed-Rejected..etc)

I want to extract the same report below for each status

Is one query for all this too much to ask?

 

[SusanV]

Not understanding your table structure or data, I can't help with the query
itself, but once you *have* the query and report, showing all status
together, you can open the report using a where condition to filter each
criteria

DoCmd.OpenReport "YourReport",,"status = 'Active'"
DoCmd.OpenReport "YourReport",,"status = 'Closed'"
DoCmd.OpenReport "YourReport",,"status = 'Rejected'"