No idea how to get the query I want

[news.microsoft.com]

I have a table that keeps track event. In a given year each person (25
total) can do multiple events, and at each event they get a place and
points. What I need is to add (sum) all the points for the current year by
each person and sort them by their total points.

I am able to get the sum of a single person on a query, but what I want is a
query that has every member's total points to find out who is leading in
point for year 2004, then 2005.

My table has, Name, Event Date, Event Name, Place, Points, Team.

Thanks for any advice. I have looked in many books and tutorials and can't
seem to find the answer.

Jose

 

[Marshall Barton]

I have a table that keeps track event. In a given year each person (25
total) can do multiple events, and at each event they get a place and
points. What I need is to add (sum) all the points for the current year by
each person and sort them by their total points.

I am able to get the sum of a single person on a query, but what I want is a
query that has every member's total points to find out who is leading in
point for year 2004, then 2005.

My table has, Name, Event Date, Event Name, Place, Points, Team.

Use a Totals type query (Totals on View menu)

Set the Name field Group By and the Points field to Sum.

For the year part, add a calculated field:

EventYear: Year([Event Date])
Set its Total to Group By and Criteria to >=2004

In SQL it would be:

SELECT [Name], Year([Event Date]) AS EventYear,
Sum(Points) As TotalPoints
FROM thetable
WHERE Year([Event Date]) >= 2004
GROUB BY [Name], Year([Event Date])
ORDER BY [Name], Year([Event Date])

 

[John Spencer (MVP)]

Sounds as if you need a Totals query. In SQL text that would look something like:

SELECT [Name], Sum([Points]) as TotalPoints
FROM YourTable
WHERE [Event Date] Between #1/1/2004# and #12/31/2004#
GROUP BY [Name]
ORDER BY Sum([Points]) Desc

IF you are doing this in the query grid, you will need to set the Total cell for
Event Date to WHERE and then enter the criteria for your date range.

 

[news.microsoft.com]

Is this suppose to be an Action Query?

It works, but comes up as an action query.

Jose

I have a table that keeps track event. In a given year each person (25
total) can do multiple events, and at each event they get a place and
points. What I need is to add (sum) all the points for the current year by
each person and sort them by their total points.

I am able to get the sum of a single person on a query, but what I want is a
query that has every member's total points to find out who is leading in
point for year 2004, then 2005.

My table has, Name, Event Date, Event Name, Place, Points, Team.

Use a Totals type query (Totals on View menu)

Set the Name field Group By and the Points field to Sum.

For the year part, add a calculated field:

EventYear: Year([Event Date])
Set its Total to Group By and Criteria to >=2004

In SQL it would be:

SELECT [Name], Year([Event Date]) AS EventYear,
Sum(Points) As TotalPoints
FROM thetable
WHERE Year([Event Date]) >= 2004
GROUB BY [Name], Year([Event Date])
ORDER BY [Name], Year([Event Date])

 

[news.microsoft.com]

Thanks for the help everyone. I had it working and after recreating it from
your posts realized that somehow I changed my original query's "total" cell
under the year to an "expression" rather than "group by".


Jose