This Report Should Be Easy... Right???

[NyQuiLlama]

Hopefully I can get some help here with my first post, go easy on me, I'm a relative SQL newb.

Here's the situation, I need to output a weekly report for a College that requires data from one table and two queries. I need it to simply report some totals or "head counts." I need to know how to make a report that just spits out the total number of fields in one table and does some simple math with some other fields. It needs to be sorted accross 4 campuses and look like this (I put comments in parenthatsis for more detail)...

Weekly Enrollmet Report
------------------------------------
Campus 1: (sorted by [CampusCode])

Current Students: 174 (this is just the total number in the table [CurrentStudents] by student ID [SSN])

Enrollments: 320 (sum of numerical field [TotalCredits] in same table [CurrentStudents] divided by 4)

Orientation total: 22 (this is the total number in a query [qOrientation] by student ID [SSN])

Accepted total: 13 (this is the total number in the same query [qOrientation] with a [DecCode] that reads "10" which means accepted)

Marketing Totals: (this is from a field in the 2nd query [qRecruits] that asks "How did you hear about us?"

Web = 15 (this is the total number of students with "Web" in the [Source] field)

Paper = 3 (this is the total number of students with "Paper" in the [Source] field)

etc....

then on to the next campus...
---------------------------------------------

Any suggestions or help in any way would be VERY much appreciated.

Thanks!!

 

[NyQuiLlama]

No one has any idea how to do this? Please help if you can. Thanks!

 

[randysmid]

Hi,
It appears as though you have an exact number of campuses. So, with this thought in mind, I would use the following strategy to make it work:
First, create a report that contains the basic outline (page header, page footer, etc.) of what you want, but don't base the report on any recordsource. Your design view will include your own "groups" (e.g., "Campus1", etc.) as labels.
Second, create a report for each of the different types of data, but without any page headers, report headers, etc. You just want a single field appearing, such as "fldCampus1NumberOfStudents".
Third, with each of these tiny reports, you can place them on your master report as a "subreport".

Please respond to this post if you need any more help with any of these techniques. HTH,
Randy Smith
California Teachers Association

 

[NyQuiLlama]

Thanks Randy! I don't have the time right now to attempt implementation of your suggestions. On Monday I'll try this and let you know if it works/if I have any questions.

Thank you!!