Sorting in a Report

[Guest]

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?

 

[Allen Browne]

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.")

 

[Guest]

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.

 

[Allen Browne]

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.

 

[Guest]

I feel like I'm real close. I get an error message that says "Syntax error
(missing operator) in query expression '[TeamNumber] ='" when I try to open
the report. I also get a "#Error" message on the query in the TeamTotal
column I just created itself for any entrant that isn't yet assigned a team
number. Should the team number be treated as a number or text?

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.

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.

 

[Guest]

I got it. I had to enter a number for the ones that were not assigned one
yet. When I put a zero there everything worked to perfection. Thank you so
much for all your help.

I feel like I'm real close. I get an error message that says "Syntax error
(missing operator) in query expression '[TeamNumber] ='" when I try to open
the report. I also get a "#Error" message on the query in the TeamTotal
column I just created itself for any entrant that isn't yet assigned a team
number. Should the team number be treated as a number or text?

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.

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?

 

[Guest]

I have everythiing working well enough to use now. I however does not show
the team number if I have not entered a score for two people on the same
team. I would like it to list the team numbers even if there is only one
person assigned to it and no scores have been posted for that team number yet.

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.

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.

 

[Allen Browne]

The solution for this will depend on your data structure.

Presumably you have set up a Team table, as well as the TeamPerson table. If
so, you can use an outer join in the query so the Team is included, even if
no persons have been assigned to the team yet.

Details in:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

 

[Guest]

I had this all working good a few days ago. Now when I went to open the query
I get an error message of - Syntax error (missing operator) in query
exprssion '[TeamNumber]=' - > I don't know what would have changed to cause
this. I am desperate because I am to use this database on Friday dso I only
have one day to get it working. Can you help?

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.

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.

 

[Allen Browne]

Does this happen in the rows where Team Number is null?

Try:
TeamTotal: DSum("[Team Score]", "Table1",
"[Team Number] = " & Nz([Team Number],0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I had this all working good a few days ago. Now when I went to open the
query
I get an error message of - Syntax error (missing operator) in query
exprssion '[TeamNumber]=' - > I don't know what would have changed to
cause
this. I am desperate because I am to use this database on Friday dso I
only
have one day to get it working. Can you help?

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.

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?