Total competitors points

[Hugh self taught]

Now I've read a lot in these forums but can't quite get my head around
adapting a query to suit my needs.

In my points table I have competitors as a field & the points they've scored
as a field. Each competition/event is a new record. So the competitor has
many records each representing the points earned that event.

I now need a query to total their points per competitor. I want to do 2
things from there. 1 is to dump that query with all the source table data
into a new table for analysis & checking & 2 is to send it to a report which
I'll want to ouput in pdf format. (that part will be an interesting time I'm
sure) From there the report will ultimately be emailed & published on the
web. Then this database is almost almost done.

Who will be kind enough to try help me with some of these issues?

 

[John W. Vinson]

Now I've read a lot in these forums but can't quite get my head around
adapting a query to suit my needs.

In my points table I have competitors as a field & the points they've scored
as a field. Each competition/event is a new record. So the competitor has
many records each representing the points earned that event.

I now need a query to total their points per competitor. I want to do 2
things from there. 1 is to dump that query with all the source table data
into a new table for analysis & checking & 2 is to send it to a report which
I'll want to ouput in pdf format. (that part will be an interesting time I'm
sure) From there the report will ultimately be emailed & published on the
web. Then this database is almost almost done.

Who will be kind enough to try help me with some of these issues?

Storing the totals in another table is almost certainly NOT necessary. If you
need to correct somebody's points for one event, your table of totals will be
WRONG and will need to be recalculated. Just use a Totals Query (create a
query based on the table and click the Greek Sigma icon; Group By the
competitor and Sum the points). This query can be used as the basis of further
queries for your analysis and checking, and can be used as the basis of a
Report which can be printed using a PDF writer as the "printer". This
capability comes with 2007 and is readily available otherwise (e.g. if you
have Adobe Acrobat see www.mvps.org/access/reports/rpt0011.htm, or use the
versatile screen capture utility SnagIt, or do a Google search for Access PDF
Output.

 

[Hugh self taught]

Hello John,

I have tried that but end up with an error msg when trying to view in
datasheet view.

Below is the current sql:-

SELECT DISTINCTROW tblPtsPerComp.PtsPerComp_Idx, tblPtsPerComp.PtsCplID,
tblPtsPerComp.PtsStructID, tblPtsPerComp.PtsCompID,
tblPtsPerComp.PtsEvtNumber, tblPtsPerComp.PtsPlaced, tblPtsPerComp.PtsFinal,
Sum(tblPtsPerComp.PtsPoints) AS [Sum Of PtsPoints]
FROM tblPtsPerComp
GROUP BY tblPtsPerComp.PtsPerComp_Idx, tblPtsPerComp.PtsCplID,
tblPtsPerComp.PtsStructID, tblPtsPerComp.PtsCompID,
tblPtsPerComp.PtsEvtNumber, tblPtsPerComp.PtsPlaced, tblPtsPerComp.PtsFinal
ORDER BY tblPtsPerComp.PtsCplID, tblPtsPerComp.PtsStructID,
tblPtsPerComp.PtsCompID;

My problems - The moment I take any "GROUP BY" field out I get the error msg
"You tried to execute a query that does not include the specified expression
'PtsPerComp_Idx' as part of an aggregate function"

This tells me that I can only select the fields that are going to be
totalled & grouped. Everything else will have to be brought back in
afterwards with another query. Am I correct there?

The other issue is that the 3rd sort column didn't sort.

I've just tried the pdf writer via another database I've done & it works
beautifully so that won't be an issue. I have Acrobat 8 Pro so I'll also be
able to convert to Html for publishing to the web.

Your further comments & guidance is greatly appreciated.

 

[Hugh self taught]

My Objective is to generate a report that will give a running total per
PtsCplID / PtsStructID / PtsCompID

What that is is Competitor - They can compete in 2 disciplines (PtsStructID)
per Competition (PtsCompID) {these are all ID references to PK in other
tables}

It should look something like this

Competitor Discipline Competition Points Total
47 1 1 25
47 1 2 10
47 1 3 15
50

47 2 1 20
47 2 2 5
25

If you can set me on the correct direction to achieve this I'd be grateful.