Sum + what was "Sum"ed

[Alireza355]

I have a table, with 3 columns:

column1 column2 column 3
10 some numbers here some text here
11 some numbers here some text here
12 some numbers here some text here
10 some numbers here some text here
20 some numbers here some text here
10 some numbers here some text here
20 some numbers here some text here
12 some numbers here some text here
...

this is quite a large table (arount 300 records)

I want to have a report, that first gives me the sum of each item, and then the items themselves. Just like this:

sum of 10: xxxxxxxxx
10 some numbers here some text here
10 some numbers here some text here
10 some numbers here some text here

sum of 11: xxxxxxxxxxxx
11 some numbers here some text here

sum of 12: xxxxxxxxxxxxx
12 some numbers here some text here

sum of 20: xxxxxxxxxxxx
20 some numbers here some text here
20 some numbers here some text here


Anyone has an idea? Thx.

[Stewart Ross]

Hi. Access reports are divided into sections reflecting how you group your data. Each of these sections can have a Group Header and a Group Footer.

To accomplish what you ask you need to specify the grouping for your report on whatever field is appropriate and ensure you have a Group Header for that section in your report. Place an unbound text box into the Group Header section of your report and then set its control source to be something like

=Sum([the control you need summed])

If you are using the word 'sum' to mean 'count' (which you may be from the poor choice of example rows you have posted, even though 'sum' is a very different function) you can simply set the control source of the unbound control in the Group Header to

=Count(*)

instead.

For the detail rows you simply place the textboxes representing each individual field in the Detail section of the report.

For Sum or any other calculation other than Count to work the control you are summing must be placed somewhere on the report itself. This should not be a problem if you are including that field in the detail rows that follow. In some cases this can mean placing controls on a report and then setting their visible property false to hide the detail value, but I don't think this will apply in your case.

The simplest approach to get you going is to use the Report Wizard to create a basic report with totals, and then adjust it manually afterwards to move the totals to the Group Header from the Group Footer (the normal location of such totals).

-Stewart

[Alireza355]

Not yet getting the results needed

Dear Stewart,

Thank you for giving me the clues on what to do.

I tried grouping on a field, and adding a textbox of "=sum([filedtosum]) to that group header, and the rest of the information in the Details section.

This is fine, but not enough. Let me explain a bit more:

I want the grouping to be done in 2 levels. First level is for example on column1, and the second grouping is on the records with the same information in column 1, but different information on another column. So all the records, having same info in column1 and 2 are grouped in one group, and all the records with the same info in column 1 and 3 are grouped in another group. (column 1 should be the same in both groups)

:(

I hope you could undrestand what I want, because I don't undrestant what I want either!

joking

Best regards,
Alireza355

[Stewart Ross]

Hi. I suggest that you look again at the report grouping facilities; you can group on several different fields at once, using the group headers to show the elements for that group once only for as long as that value is unchanged within the group, like this

Outer Grouping Value 1
      Next Level Group Value 1         Sum of Records
           Detail Row 1
           Detail Row 2
      Next Level Group Value 2         Sum of Records
           Detail Row 3
           Detail Row 4
           Detail Row 5
Outer Grouping Value 2
      Next Level Group Value 1         Sum of Records
...

and so on. Your unbound Sum textboxes would go within the innermost group header just before the detail section.

Access reports are very flexible in their ability to group data. If you really do not have any experience in using them I'd suggest that you work your way through some examples in Access teach-yourself books, and that you also have a look at the NorthWind sample database supplied with Access as it contains useful examples of product reports with multi-level groupings.

-Stewart

[Alireza355]

Thanx a lot

Dear Stewart,

Thank you soooooooooooooo oooooooooooo much

Best Regards,
Alireza355