Calculate multiple fields in query

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I have a database I am working on. So far, I have used
the table analyzer and split the original table into 3
tables.
The majority of the fields in the tables are questions,
and the answers are either 1 or 0.
What I need to be able to do, is total all of the
questions. For example Q1+Q2+Q3=Total. The problem is,
whenever I attempt to do the calculation { Sum([Q1]+[Q2]+
[Q3]), I receive the error "You tried to execute a query
that does not include the specified expression 'ID' as
part of an aggregate function." ID is just an autonumber
linking to one of the other tables.
I am at a loss.
 
Post the entire SQL statement that is giving the error. Your description is
not showing us how the tables are related.
 
Below is a sample of the SQL. I haven't built the entire
query yet, as I am wanting to resolve this issue first.

SELECT Table1.Record, Sum([pbx]![pbx Rings to Answer]+
[pbx]![pbx Greeting]+[pbx]![pbx If placed on hold]) AS
Pbxttl
FROM res INNER JOIN (pbx INNER JOIN Table1 ON pbx.ID =
Table1.pbx_ID) ON (pbx.ID = res.ID) AND (res.ID =
Table1.res_ID);

-----Original Message-----
Post the entire SQL statement that is giving the error. Your description is
not showing us how the tables are related.

--

Ken Snell
<MS ACCESS MVP>

I have a database I am working on. So far, I have used
the table analyzer and split the original table into 3
tables.
The majority of the fields in the tables are questions,
and the answers are either 1 or 0.
What I need to be able to do, is total all of the
questions. For example Q1+Q2+Q3=Total. The problem is,
whenever I attempt to do the calculation { Sum([Q1]+ [Q2]+
[Q3]), I receive the error "You tried to execute a query
that does not include the specified expression 'ID' as
part of an aggregate function." ID is just an autonumber
linking to one of the other tables.
I am at a loss.
Click to expand...


.
Click to expand...
 
Your SQL syntax is not valid for a totals query because it doesn't have a
GROUP BY clause. Also, [pbx]![pbx Rings to Answer] is not valid syntax --
SQL doesn't use ! as an operator between a table name and a field name, it
uses . operator.

If you use the QBE wizard, you can more easily design queries without having
to initially write SQL.

On the assumption that I'm understanding what you're doing, try this:

SELECT Table1.Record, Sum([pbx].[pbx Rings to Answer]+
[pbx].[pbx Greeting]+[pbx].[pbx If placed on hold]) AS
Pbxttl
FROM res INNER JOIN (pbx INNER JOIN Table1 ON pbx.ID =
Table1.pbx_ID) ON (pbx.ID = res.ID) AND (res.ID =
Table1.res_ID)
GROUP BY Table1.Record;

--

Ken Snell
<MS ACCESS MVP>

Below is a sample of the SQL. I haven't built the entire
query yet, as I am wanting to resolve this issue first.

SELECT Table1.Record, Sum([pbx]![pbx Rings to Answer]+
[pbx]![pbx Greeting]+[pbx]![pbx If placed on hold]) AS
Pbxttl
FROM res INNER JOIN (pbx INNER JOIN Table1 ON pbx.ID =
Table1.pbx_ID) ON (pbx.ID = res.ID) AND (res.ID =
Table1.res_ID);

-----Original Message-----
Post the entire SQL statement that is giving the error. Your description is
not showing us how the tables are related.

--

Ken Snell
<MS ACCESS MVP>

I have a database I am working on. So far, I have used
the table analyzer and split the original table into 3
tables.
The majority of the fields in the tables are questions,
and the answers are either 1 or 0.
What I need to be able to do, is total all of the
questions. For example Q1+Q2+Q3=Total. The problem is,
whenever I attempt to do the calculation { Sum([Q1]+ [Q2]+
[Q3]), I receive the error "You tried to execute a query
that does not include the specified expression 'ID' as
part of an aggregate function." ID is just an autonumber
linking to one of the other tables.
I am at a loss.
Click to expand...


.
Click to expand...
Click to expand...
 
That solved a lot of the problems. Thank you. That
looks like it solved it.
-----Original Message-----
Your SQL syntax is not valid for a totals query because it doesn't have a
GROUP BY clause. Also, [pbx]![pbx Rings to Answer] is not valid syntax --
SQL doesn't use ! as an operator between a table name and a field name, it
uses . operator.

If you use the QBE wizard, you can more easily design queries without having
to initially write SQL.

SELECT Table1.Record, Sum([pbx].[pbx Rings to Answer]+
[pbx].[pbx Greeting]+[pbx].[pbx If placed on hold]) AS
Pbxttl
FROM res INNER JOIN (pbx INNER JOIN Table1 ON pbx.ID =
Table1.pbx_ID) ON (pbx.ID = res.ID) AND (res.ID =
Table1.res_ID)
GROUP BY Table1.Record;

--

Ken Snell
<MS ACCESS MVP>

Below is a sample of the SQL. I haven't built the entire
query yet, as I am wanting to resolve this issue first.

SELECT Table1.Record, Sum([pbx]![pbx Rings to Answer]+
[pbx]![pbx Greeting]+[pbx]![pbx If placed on hold]) AS
Pbxttl
FROM res INNER JOIN (pbx INNER JOIN Table1 ON pbx.ID =
Table1.pbx_ID) ON (pbx.ID = res.ID) AND (res.ID =
Table1.res_ID);

-----Original Message-----
Post the entire SQL statement that is giving the
Click to expand...
error.
Your description is
not showing us how the tables are related.

--

Ken Snell
<MS ACCESS MVP>

I have a database I am working on. So far, I have used
the table analyzer and split the original table into 3
tables.
The majority of the fields in the tables are questions,
and the answers are either 1 or 0.
What I need to be able to do, is total all of the
questions. For example Q1+Q2+Q3=Total. The problem is,
whenever I attempt to do the calculation { Sum([Q1]+ [Q2]+
[Q3]), I receive the error "You tried to execute a query
that does not include the specified expression 'ID' as
part of an aggregate function." ID is just an autonumber
linking to one of the other tables.
I am at a loss.


.
Click to expand...
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top