Trying to create monthly report

[oharab]

I have 4 tables:
tblSites:SiteID(PK),ForceName,SiteName
tblViper:VRN(PK),SiteID(FK)
Requests:RequestID(PK),VRN(FK),RequestDate
Compilations:CompilationID(PK),RequestID(FK),CompilationName

Each are linked to the other using the appropriate keys.

Thanks to SwampBoogie I am able to the total number of Requests & The total number of Compilations.

What I am trying to do now is create the monthly report.

It should show for each site, for each month, the total number of Compilations and Requests. It seems so easy and I can do it if we wanted to have 2 seperate reports, but te powers that be want everything on 1 sheet "Like Excel".

You know the sort of thing I mean:

Jan Feb Mar Apr May...
| C R | C R | C R | C R | C R |
Bradford 5 2 9 5 5 1 9 9 7 3
....


I just can't get my head round it.

Please help!!
Ben
----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[randysmid]

Hi,
This is a crosstab report, and I wrote an FAQ on this topic. You can view and print it from: faq703-2868 HTH,
Randy Smith
California Teachers Association

 

[67peshawar294]

ben,
create a new query selecting the fields you need for the
data;
[site]
[date]
[compilations]
[requests]
In a blank QBE "field" add the [c] and [r]'s
[compilations]+[requests]
change the "exp1;" to C+Ror whatever you want)

Then, create a crosstab query
"row" is site
"columns" is date, month
data is [C+R}, sum
That should do it.
jim

 

[oharab]

I couldn't do it as a cross tab report as I want 2 fields for the "value" ie Compilations and Requests, seperate.
What I have done is create a sub report with siteID, month, compilations and requests as fields, then formatted it with 13 columns (12 months, and the grand total). Then put it on the main report using siteID as the link.
All works really well now.


Thanks for your thoughts.

B ----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[AccessBEginner]

Hi there,

You manage to get this :-

Jan Feb Mar Apr May...
| C R | C R | C R | C R | C R |
Bradford 5 2 9 5 5 1 9 9 7 3
....


I need to get a report like this as well... can you please share your knowledge with me ?

Thanks and regards,
BEGINNER