1. Create a query using this table.
2. In a fresh column in the Field row, enter the expression like this:
TeamTotal: DSum("[Team Score]", "Table1", "[Team Number] = " & [Team
Number])
Replace Table1 with the name of your table.
3. Save the query. Close.
4. Open the report in design view.
Open the Properties box.
The first item on the Data tab is Record Source.
Set it to the name of the query you just saved.
(If you don't see RecordSource in the Properties box, you are looking at the
properties of a text box, not the Report itself.)
5. Open the Sorting And Grouping box (View menu.)
Insert a row above any others.
In this new row, choose the TeamTotal field.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Dave said:
I am relatively new to access and have not used the DSum function before.
I'm
not sure how or where you want me to use it. In the end I would like my
reort
to look like this:
Team Number Team Score First Name Last Name Pro
1
230 John Smith
250 Jack Jones
Yes
480
3
290 Joe Anderson
280 Dick Johnson
Yes
470
etc.......
I actually have the report looking this way now. The problem is it does
not
sort them by the total score of each team. In the above example 480 then
470
etc.....
on down the line. You are probably telling me exactly what I need to do to
accomplish this but I am not understanding what I am to do. I played
around
with it quite a bit last night but was not successful.
:
Dave, you must get the total into the query that supplies the data to the
report if you wish to sort by it.
If the total is a sum of scores, Access won't know the total until it has
placed the different rows on the report. It is then too late to go back
and
place them somewhere else (i.e. sort them into another position.)
The solution will be to use a query as the Record Source for your report.
The query will need to include a DSum() expression that calculates the
team
total, or you might be able to design another Totals query as an input
"table" for that query.
(A third alternative might be a subquery, but that's likely to give you a
"Multi-level Group-By error.")
I have set up a report to total the scores of a team. I shows the team
number, the name and score of each individual of the team and then adds
the
individual scores together to show the teams total score. I am not able
to
get the report ot sort by the team total. How do I do this?