Monthly Report Summaries

[Prufrock]

Hi,
I have quite a few fields in a table. The table holds the data on each individual. Many of these fields have a Yes or No response. One has a range of about 10 possible categories where one is appropriate for each individual.
e.g.

Work Done Boss Notified Field rating
Yes Yes Am1
No No Am2
Am3
Am4 etc

What I am trying to do is to develop one report that provides a summary of all the data for a set date period.
I have a form linked to the query so dates can be entered. Then the query runs to a report. I need some guidance as to how to collect all the summarised material for the month and feature it in the report.


Can I simply run the one query that features all the necessary fields and does summing and counting? Or do I need multiple queries so I get the total Yes, Total No, total field rating listed by rating?

Any help appreciated.

 

[randysmid]

Hi,
You may need a Crosstab report, but am not really sure. Take a look at my FAQ on this at [faq703-2868] and see if the sample matches what you are looking for. If this isn't what you want, can you give us a small example of what you would like the report to look like?

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[Prufrock]

You may be right with Crosstab but this may help.
Report would look like.

Work Done:
Yes 123
No 121

Boss Notified:
Yes 43
No 76

Field Rating:
Am1 23
Am2 34
Am3 56
Am4 23

and so on. I am fine with the layout just trying to sort out the most efficient way to get these areas counted and summed on the separate possible choices made when data is entered.

Thanks

 

[randysmid]

Ok,
I think I understand. This format is used for each person, right? That would be a Group Header. Your next Group level should be Category (e.g., Work Done, Boss Notified, and Field Rating), but these are not alphabetical order. What I have done is to create another table that contains these three categories, with a ranking that they appear in (Work Done = 3, Boss Notified = 6, Field Rating = 9). This lets you add additional categories in the future. By linking this table to your query (and using the "ranking" field as a sort), you can now use this as a Grouping level in your report.

Was this info useful to you? In any event, try to work backward from the basic layout of the data you want to appear in the report. With this in mind, you may need to create one or more queries to help accomplish this task.

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association