Calculated Field multiplying two fields after totals have been summed

[UnclePaul]

I'm working on a project to store football stats in a database, right now I want to combine single game stats into season stats using a query. I've been able to successfully add up my stats for individual players, but now I want to do some math to calculate per game and per statistic numbers. I'm having trouble getting my Yards/Carry Average to work.
I am adding up all the fields that add together with the Totals box in the Design view of my query, and it messes up my calculated Average field. No matter what I select in the Total field my averages don't work out properly. What I want the field to do is to run after the other fields are summed, so that I am taking to total amount of Yards and Carries for each player, instead of calculating the Average for each record individually and then trying to Sum or Avg or any of the other Total choices. I'm guessing this can only be done with some SQL code, which I have no experience or knowledge in, so I was hoping I could find some help here. Anyone have any advice for me?

[June7]

Build a report. Use its Grouping & Sorting features with aggregate calcs in textboxes.

[CJ_London]

all the query builder does is generate sql code - click on the sql tab to see it, it is not an alternative type of query.

You have not provided the sort of information we need to be able to help you, please provide some example data and the result you want to see.

At the moment my best guess is you will need to use a number of queries and then combine them in a final query

[UnclePaul]

all the query builder does is generate sql code - click on the sql tab to see it, it is not an alternative type of query.

You have not provided the sort of information we need to be able to help you, please provide some example data and the result you want to see.

At the moment my best guess is you will need to use a number of queries and then combine them in a final query

I have fields PlayerName, Carries, Yards, and Average. Average = [Yards]/[Carries]. For example I will have two entries for one player, Player1, 10 Carries, 50 Yards. Player1, 20 Carries 75 Yards. I want to combine these into my SeasonStats Query so that I have one record that reads Player1, 30 Carries, 125 Yards, 4.16 Average.
When I use the Totals box, I can sum up the Carries and Yards fields like I want, but I am unable to figure out how to get my average to calculate after the records have been summed. Right now I can also sum the Averages, but that will take the Average of each record and add those together, which will give me the wrong number.

[CJ_London]

you need to do a weighted average - two queries. The first to sum the yards and carries, the second working of the first query to divide total yards by total carries - a basic math process.

If you want to get into sql code, this can be done in one query - one of the limitations of the query builder is you have to use two queries

[UnclePaul]

you need to do a weighted average - two queries. The first to sum the yards and carries, the second working of the first query to divide total yards by total carries - a basic math process.

If you want to get into sql code, this can be done in one query - one of the limitations of the query builder is you have to use two queries

I'm working on this project mostly to learn Access for work, would you be able to help me solve this issue using SQL code so that I have that knowledge moving forward?

[CJ_London]

sure, but don't threat this thread to cover any specific topic, start a new one. With regards this issue there is a cheat you can use so you can see what it looks like

1. create your two queries
2. open the first query, go to the sql window
3. copy the whole code, but exclude the semi colon at the end
4. open the second query and go to the sql window
5. part of the code will be something like

FROM Query1

6. Insert double brackets between the two words

FROM () Query1

7. Now paste the code from query1 between the two brackets

8. run the query - if all OK, you can delete the first query

With regards the query code generated by Access there are lots and lots of brackets. Most are not required and their removal will have no impact on performance but a huge improvement in readability

access all also always include the table name, these can usually be removed because all field names should be unique. e.g.

SELECT Table1.Field1
FROM Table1

can just be

SELECT Field1
FROM Table1

There are some potential exceptions depending on your naming style - having ID as the name of the autonumber field in all tables means you then need to identify which table ID you are referring to.

[ssanfu]

I hope your "Average" field in the table is NOT a calculated field.
Calculations should be done in a query (or possibly a control on a form).


My $0.03........ (I got a raise)

[UnclePaul]

I hope your "Average" field in the table is NOT a calculated field.
Calculations should be done in a query (or possibly a control on a form).


My $0.03........ (I got a raise)

No, any average values I have saved in my table is calculated in the form and saved as just a number.

[UnclePaul]

sure, but don't threat this thread to cover any specific topic, start a new one. With regards this issue there is a cheat you can use so you can see what it looks like

1. create your two queries
2. open the first query, go to the sql window
3. copy the whole code, but exclude the semi colon at the end
4. open the second query and go to the sql window
5. part of the code will be something like

FROM Query1

6. Insert double brackets between the two words

FROM () Query1

7. Now paste the code from query1 between the two brackets

8. run the query - if all OK, you can delete the first query

With regards the query code generated by Access there are lots and lots of brackets. Most are not required and their removal will have no impact on performance but a huge improvement in readability

access all also always include the table name, these can usually be removed because all field names should be unique. e.g.

SELECT Table1.Field1
FROM Table1

can just be

SELECT Field1
FROM Table1

There are some potential exceptions depending on your naming style - having ID as the name of the autonumber field in all tables means you then need to identify which table ID you are referring to.

This seems to be exactly what I was looking for. Thank you for your help.