Adding row ID to an expression

[JimO]

I have a query with a list of games won and lost. I am trying to sum the wins and losses to show the total of games played.

Using the expression

GmsPlayed: Sum([W16]+[L16])

I get a message about needing the ID but I have not had any success in doing so.

The query is called 'NFLStandingsQry' and the team ID would be 'NFLID' all data is from the source table 'NFLTeamsT'.

I am not sure how to properly include the ID in the formula.

Thanks
Jim O

[orange]

How about showing the SQL of the query you are trying to run?

[JimO]

GmsPlayed: Sum([W16]+[L16])

I have tried

GmsPlayed: Sum([NFLID].[W16]+[NFLID].[L16])

and some other like combinations with no success.

Jim O

[JoeM]

That is not SQL code, that is a calculated field you posted. To get that, just change your query to SQL View and copy and paste the code here.
As Orange requested, if you post your whole query, it might shed all light on the situation.

Also, note that SUM and + do the same thing. You only need one or the other, not both.
I would recommend just sticking to:

GmsPlayed: [W16]+[L16]

as the use of the SUM function is usually used in Aggregate Queries, which involves grouping records (and having a GROUP BY clause in your Query).

Also, is NFLID the name of your table or a field?
If it is the name of a field, then this is an invalid statement:

[NFLID].[W16]+[NFLID].[L16]

as you can only preface field names with object names (like tables).

[JimO]

I think this is what you are requesting.

SELECT NFLTeamsTbl.NFLID, NFLTeamsTbl.Region, NFLTeamsTbl.[Full Name], NFLTeamsTbl.Team, NFLTeamsTbl.W16, NFLTeamsTbl.L16, NFLTeamsTbl.Conference
FROM NFLTeamsTbl
ORDER BY NFLTeamsTbl.W16 DESC;

Jim O

[JoeM]

OK, so your query should look like:

SELECT NFLTeamsTbl.NFLID, NFLTeamsTbl.Region, NFLTeamsTbl.[Full Name], NFLTeamsTbl.Team, NFLTeamsTbl.W16, NFLTeamsTbl.L16, NFLTeamsTbl.Conference, [W16] + [L16] as GmsPlayed
FROM NFLTeamsTbl
ORDER BY NFLTeamsTbl.W16 DESC;

[JimO]

Thanks Joe,

That seems to be working. I was not aware of this approach, thanks for the information.

Jim O

[JoeM]

You can either use the Query Builder, or write the SQL code directly.

You would have gotten pretty much the same SQL code as I posted if you went in to the Query Builder and added a Calculated Field like this:

GmsPlayed: [W16] + [L16]

[JimO]

I tried that and kept getting a prompt for an ID.

Jim O

[JoeM]

I tried that and kept getting a prompt for an ID.

But you were also using the SUM function, right?
Did you actually try doing it without using SUM?

[JimO]

Yes, I tried it both ways.

Jim O

[JoeM]

IDK, that doesn't make much sense to me. I tried setting up your table on Access 2010, and used those exact steps and it worked for me.

If you had to qualify it, you would do that with the table name, i.e.

[NFLTeamsTbl].[W16] + [NFLTeamsTbl].[L16] as GmsPlayed

[JimO]

Thank you very much for the help and tutorial. It is working fine now and if I run into an issue again I will be back on here asking for help.

Thanks
Jim O