Report that is totals only

[prn]

Hi folks,

I'm looking for a little advice here. I need to create a report that is totals only, with no detail records. I have a database with a lot of individuals (people) and the report has to have some overall totals, totals per state, totals by gender, race/ethnicity, and several other criteria. This report does not need to (and should not) report on each individual, just the totals.

I can create expressions for each of the items I need, but the question I'm looking for advice on is how to organize the overall report. I'm guessing that I should do it as multiple subreports included in a master report. Does that seem like the best procedure? Or should I just put them all into a single master report? Are there any good rules of thumb for when one is a better idea than the other?

Also, when I go to create the (sub)reports (e.g., in "Design View") Access gives me only sections for "Headers/footers" and "Details". If I put the total section in "Details", then the result repeats the totals several hundred times, i.e., once for each individual, which I do not want. How best to print these totals once for the entire report/subreport? Logically, I don't want to consider the entire report to fall into a "Report Header" section, but I suppose that's what I may need to do to get them only once. Does anyone have a better idea?

Thanks for any thoughts you might have for me.

Paul

[istya]

Does it have to be a report? You could do something like

SELECT state, race, sum(vlaue to be totaled)
FROM table
GROUP BY state, race;
 

Which would give you totals for each state and race but nothing else.

[mlcampeau]

Hi folks,

I'm looking for a little advice here. I need to create a report that is totals only, with no detail records. I have a database with a lot of individuals (people) and the report has to have some overall totals, totals per state, totals by gender, race/ethnicity, and several other criteria. This report does not need to (and should not) report on each individual, just the totals.

I can create expressions for each of the items I need, but the question I'm looking for advice on is how to organize the overall report. I'm guessing that I should do it as multiple subreports included in a master report. Does that seem like the best procedure? Or should I just put them all into a single master report? Are there any good rules of thumb for when one is a better idea than the other?

Also, when I go to create the (sub)reports (e.g., in "Design View") Access gives me only sections for "Headers/footers" and "Details". If I put the total section in "Details", then the result repeats the totals several hundred times, i.e., once for each individual, which I do not want. How best to print these totals once for the entire report/subreport? Logically, I don't want to consider the entire report to fall into a "Report Header" section, but I suppose that's what I may need to do to get them only once. Does anyone have a better idea?

Thanks for any thoughts you might have for me.

Paul

In report design view, click on the sorting and grouping button on the Report Design toolbar at the top of your screen. Choose your headers/footers sections that you would like to see from the field list in the drop down box (i.e. State, Gender, Race, etc.) then where it says Group Footer, change it to 'Yes'. If you're wanting the header as well, change that to 'Yes' also. Put your totals expressions in the footers of your groupings. You do not need to have anything in the details section, so just shrink that section down all the way so you don't end up with unwanted white space on your report.

[prn]

Does it have to be a report? You could do something like

SELECT state, race, sum(vlaue to be totaled)
FROM table
GROUP BY state, race;
 

Which would give you totals for each state and race but nothing else.

Well, Yes, I think I do need a report, not just a dozen or so ad-hoc queries. I need something that I can (have my client) print giving a lot of different statistics. I don't think just a bunch of queries would be acceptable. I may be able to use this SQL anyway for some of the items, though, so thanks. I appreciate any hints.

Paul

[prn]

In report design view, click on the sorting and grouping button on the Report Design toolbar at the top of your screen. Choose your headers/footers sections that you would like to see from the field list in the drop down box (i.e. State, Gender, Race, etc.) then where it says Group Footer, change it to 'Yes'. If you're wanting the header as well, change that to 'Yes' also. Put your totals expressions in the footers of your groupings. You do not need to have anything in the details section, so just shrink that section down all the way so you don't end up with unwanted white space on your report.

Thanks, I'll try this and see what I can do to organize it this way. I take it that you intend this to go essentially for each of the subreports?

Thanks,
Paul

[mlcampeau]

Thanks, I'll try this and see what I can do to organize it this way. I take it that you intend this to go essentially for each of the subreports?

Thanks,
Paul

I tend to try to stay away from subreports, mainly cuz I'm fairly new to this and it just seems to get messy that way and I can usually accomplish what I'm trying to do with one report. If you are able to get all the info you are looking for in one query, try to use just one main report and set up the grouping that way. Depending on how the results output, you may need to turn to subreports to get the correct totals you are looking for.