trying to sort based on a summary item

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working with table that has entries that has more than one entry for
some people, but not all. I am grouping by last name and first name.

BUT I want to put the people that have more than 1 entry at the top, and
with a summary(totals),
and those with a single entry I do not need a summary

How do I accomplish this?
 
You can create a totals query similar to your report's record source that
calculates the values you want to sort by. Then add this query to your
report's record source and link the appropriate fields. You can now use your
summary information in the report for sorting.
 
I guess I'm doing a poor job of explaining what I want.
Example:
name (split) games wins losses year
Bryce Douglas 120 70 50 2005
Bryce Douglas 80 50 30 2006
Jon Cross 50 30 20 2005
Jon Cross 70 60 10 2006
Mike Grindle 100 70 30 2005
Mike Grindle 50 40 10 2006

The above is the data info in the tables
I want a report to print (when ranked by total wins):

Name games wins losses year
Douglas, Bryce
120 70 50 2005
80 50 30 2006
sum 200 120 80

Grindle, Mike
100 70 30 2005
50 40 10 2006
sum 150 110 40

Cross, Jon
50 30 20 2005
70 60 10 2006
sum 120 90 30

This report is grouped by names, Last and then First BUT the order of the
report is determined because Douglas had 120 wins, Grindle 110 and Cross had
90.
I have sorted in that year 2005 is before 2006.

I hope you understand what I am trying to do.
 
I guess I did a poor job of explaining what I wanted you to do ;-)

Create a query
SELECT [Name], Sum([Wins]) As WinTotal
FROM tblNoNameGiven
GROUP BY [Name];

Save the above query and add it to your report's record source query. Join
the [Name] fields and add the WinTotal field to your report's field list.
This will allow you to sort by WinTotal.
 
When I thought about what you said, it was obvious that putting a Summary
Querry with a detai querry would give me exactly what I needed. Thank you
very much. By using a Summary querry I can easily sort by total wins, or
total games or whatever else I want. By using a criteria for total years I
can easily set this as > 3000 to only look at the multi-year participants.

Again, thank you very much for a simple solution that I had not even thought
about!

Duane Hookom said:
I guess I did a poor job of explaining what I wanted you to do ;-)

Create a query
SELECT [Name], Sum([Wins]) As WinTotal
FROM tblNoNameGiven
GROUP BY [Name];

Save the above query and add it to your report's record source query. Join
the [Name] fields and add the WinTotal field to your report's field list.
This will allow you to sort by WinTotal.

--
Duane Hookom
MS Access MVP
--

[email protected] said:
I guess I'm doing a poor job of explaining what I want.
Example:
name (split) games wins losses year
Bryce Douglas 120 70 50 2005
Bryce Douglas 80 50 30 2006
Jon Cross 50 30 20 2005
Jon Cross 70 60 10 2006
Mike Grindle 100 70 30 2005
Mike Grindle 50 40 10 2006

The above is the data info in the tables
I want a report to print (when ranked by total wins):

Name games wins losses year
Douglas, Bryce
120 70 50 2005
80 50 30 2006
sum 200 120 80

Grindle, Mike
100 70 30 2005
50 40 10 2006
sum 150 110 40

Cross, Jon
50 30 20 2005
70 60 10 2006
sum 120 90 30

This report is grouped by names, Last and then First BUT the order of the
report is determined because Douglas had 120 wins, Grindle 110 and Cross
had
90.
I have sorted in that year 2005 is before 2006.

I hope you understand what I am trying to do.
Click to expand...
Click to expand...
 
Back
Top