Grouping

[m17347047711116]

I have a table
[ID]pk,[Name],[Score1],[Score2]

Sample
[1],[Shaun],[6],[7]
[2],[Shaun],[7],[5]
[3],[Andrew],[5],[6]
[4],[Andrew],[6],[6]

When I query for report I want the report to be as follows:

Name Total
Shaun 25
Andrew 23

I am missing something in my query or report not sure which one but this is what I see:
Shaun
6, 7
7, 5
Andrew
5, 6
6, 6

I do not want to see each record sub under the name i want to see only the total of all records for each name.

I will continue to read the forum if you can help pls do.
thanx

 

[statsman]

You can approach this in one of two ways.

If you choose to go the query route, create a calculated field we'll call totscore. In a blank cell in the query (in design view) enter:
totscore:Sum([Score1]+[Score2])
Then create your report from the query rather than the table.

OR

In your report design view (report created from table), you should have the following fields in the detail section:

Name Score1 Score2
Enter a blank textbox from the toolbox into the detail section.
In the text box enter:
=([Score1]+[Score2]).

If you wish to have the layout in the report like the one you stated in your message, you should not have any grouping in the report at all.

 

[ScottGem]

Part of your problem is that you have a repeating group (Score1, Score2) which violates normalization rules.

Instead your table should be:

ID (PK Autonumber)
Person (Name is a reserved word in Access)
ScoreType (1,2)
Score

You could then use a GroupBy query to sum the scores by ID.

 

[m17347047711116]

Report of Sum Totals

O.K this does help Scott,

I was able to now get the report of the information correct. I am seeing the sum of the scores by Name of Contestant perfectly. I now need to order the report by this sum total. But I can't find a method of doin so in the grouping or sorting as the sum of the total score for a Contestant Name is a formula on the report and is not available for sorting or grouping.

 

[Rich]

Use a totals query, GroupBy the person, Sum the score, base your Report on that query

 

[Rich]

Use a totals query, GroupBy the person, Sum the score, base your Report on that query

 

[ScottGem]

As Rich said, you can do your sorting in a report. or you can create another query based on the group by then sort that.