Calculate multiple fields in query

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.
 
K

Ken Snell

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

Guest

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.


.
 
K

Ken Snell

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.


.
 
G

Guest

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


.


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top