more query help

G

Guest

im trying to total the amounts of a field named LikelyDuration in a query.
This is the code i have:

Expr1: Sum(LikelyDuration])

this does not seem to work however. i get this error message:

"You tried to execute a query that does not include the specified expression
'AbsenceLineID' as part of an aggregate function".

What am i doing wrong?
 
G

Guest

SELECT tblAbsenceLine.AbsenceLineID, tblAbsenceLine.AbsenceID,
tblAbsenceLine.AbsenceReasonID, tblAbsenceLine.Date,
tblAbsenceLine.LikelyDuration, tblAbsenceLine.WorkRelated,
tblAbsenceLine.NotifiedBy, Sum([LikelyDuration]) AS Length
FROM tblAbsenceLine;


yeah, i suppose that would be useful!

Chris2 said:
Joe Robbo said:
im trying to total the amounts of a field named LikelyDuration in a query.
This is the code i have:

Expr1: Sum(LikelyDuration])

this does not seem to work however. i get this error message:

"You tried to execute a query that does not include the specified expression
'AbsenceLineID' as part of an aggregate function".

What am i doing wrong?
Click to expand...

Joe Robbo,

Please include the SQL for the query(ies) involved.


Sincerely,

Chris O.
Click to expand...
 
C

Chris2

Joe Robbo said:
im trying to total the amounts of a field named LikelyDuration in a query.
This is the code i have:

Expr1: Sum(LikelyDuration])

this does not seem to work however. i get this error message:

"You tried to execute a query that does not include the specified expression
'AbsenceLineID' as part of an aggregate function".

What am i doing wrong?
Click to expand...

Joe Robbo,

Please include the SQL for the query(ies) involved.


Sincerely,

Chris O.
 
C

Chris2

Joe Robbo said:
Joe Robbo said:
im trying to total the amounts of a field named LikelyDuration
Click to expand...
in a
query.
This is the code i have:

Expr1: Sum(LikelyDuration])

this does not seem to work however. i get this error message:

"You tried to execute a query that does not include the
Click to expand...
specified
expression
'AbsenceLineID' as part of an aggregate function".

What am i doing wrong?
Click to expand...

Joe Robbo,

Please include the SQL for the query(ies) involved.


Sincerely,

Chris O.
Click to expand...
Click to expand...
yeah, i suppose that would be useful!

:
Click to expand...

Joe Robbo,

SELECT tblAbsenceLine.AbsenceLineID
,tblAbsenceLine.AbsenceID
,tblAbsenceLine.AbsenceReasonID
,tblAbsenceLine.Date
,tblAbsenceLine.LikelyDuration
,tblAbsenceLine.WorkRelated
,tblAbsenceLine.NotifiedBy
,Sum([LikelyDuration]) AS Length
FROM tblAbsenceLine;

The GROUP BY clause is missing.

Change the query to:

SELECT tblAbsenceLine.AbsenceLineID
,tblAbsenceLine.AbsenceID
,tblAbsenceLine.AbsenceReasonID
,tblAbsenceLine.Date
,tblAbsenceLine.LikelyDuration
,tblAbsenceLine.WorkRelated
,tblAbsenceLine.NotifiedBy
,Sum([LikelyDuration]) AS Length
FROM tblAbsenceLine
GROUP BY tblAbsenceLine.AbsenceLineID
,tblAbsenceLine.AbsenceID
,tblAbsenceLine.AbsenceReasonID
,tblAbsenceLine.Date
,tblAbsenceLine.LikelyDuration
,tblAbsenceLine.WorkRelated
,tblAbsenceLine.NotifiedBy;


I can't tell without your seeing the structure of your tables and the
data in them and the appearance of your expected output, but just from
the way this Query is done, I'm thinking that it won't produce the
output you are looking for (if I'm wrong, then don't worry about what
I just said).


Sincerely,

Chris O.
 
G

Guest

thanks, that is kindof along the lines, but it isn't actually giving me a
total. i need 1 total for the cumulative set of data. i have seen it done by
using a text box on a form before - is that the only possible way of
achieving this? or can i use a new field to make a cumulative total and then
use last(expr) to extract whatever value is there? thanks

Chris2 said:
Joe Robbo said:
im trying to total the amounts of a field named LikelyDuration in a
query.
This is the code i have:

Expr1: Sum(LikelyDuration])

this does not seem to work however. i get this error message:

"You tried to execute a query that does not include the specified
expression
'AbsenceLineID' as part of an aggregate function".

What am i doing wrong?

Joe Robbo,

Please include the SQL for the query(ies) involved.


Sincerely,

Chris O.
Click to expand...
Click to expand...
yeah, i suppose that would be useful!

:
Click to expand...

Joe Robbo,

SELECT tblAbsenceLine.AbsenceLineID
,tblAbsenceLine.AbsenceID
,tblAbsenceLine.AbsenceReasonID
,tblAbsenceLine.Date
,tblAbsenceLine.LikelyDuration
,tblAbsenceLine.WorkRelated
,tblAbsenceLine.NotifiedBy
,Sum([LikelyDuration]) AS Length
FROM tblAbsenceLine;

The GROUP BY clause is missing.

Change the query to:

SELECT tblAbsenceLine.AbsenceLineID
,tblAbsenceLine.AbsenceID
,tblAbsenceLine.AbsenceReasonID
,tblAbsenceLine.Date
,tblAbsenceLine.LikelyDuration
,tblAbsenceLine.WorkRelated
,tblAbsenceLine.NotifiedBy
,Sum([LikelyDuration]) AS Length
FROM tblAbsenceLine
GROUP BY tblAbsenceLine.AbsenceLineID
,tblAbsenceLine.AbsenceID
,tblAbsenceLine.AbsenceReasonID
,tblAbsenceLine.Date
,tblAbsenceLine.LikelyDuration
,tblAbsenceLine.WorkRelated
,tblAbsenceLine.NotifiedBy;


I can't tell without your seeing the structure of your tables and the
data in them and the appearance of your expected output, but just from
the way this Query is done, I'm thinking that it won't produce the
output you are looking for (if I'm wrong, then don't worry about what
I just said).


Sincerely,

Chris O.
Click to expand...
 
C

Chris2

Joe Robbo said:
Joe Robbo said:
im trying to total the amounts of a field named
Click to expand...
LikelyDuration
in a
query.
This is the code i have:

Expr1: Sum(LikelyDuration])

this does not seem to work however. i get this error message:

"You tried to execute a query that does not include the specified
expression
'AbsenceLineID' as part of an aggregate function".

What am i doing wrong?

Joe Robbo,

Please include the SQL for the query(ies) involved.


Sincerely,

Chris O.
Click to expand...
yeah, i suppose that would be useful!

:
Click to expand...

Joe Robbo,

SELECT tblAbsenceLine.AbsenceLineID
,tblAbsenceLine.AbsenceID
,tblAbsenceLine.AbsenceReasonID
,tblAbsenceLine.Date
,tblAbsenceLine.LikelyDuration
,tblAbsenceLine.WorkRelated
,tblAbsenceLine.NotifiedBy
,Sum([LikelyDuration]) AS Length
FROM tblAbsenceLine;

The GROUP BY clause is missing.

Change the query to:

SELECT tblAbsenceLine.AbsenceLineID
,tblAbsenceLine.AbsenceID
,tblAbsenceLine.AbsenceReasonID
,tblAbsenceLine.Date
,tblAbsenceLine.LikelyDuration
,tblAbsenceLine.WorkRelated
,tblAbsenceLine.NotifiedBy
,Sum([LikelyDuration]) AS Length
FROM tblAbsenceLine
GROUP BY tblAbsenceLine.AbsenceLineID
,tblAbsenceLine.AbsenceID
,tblAbsenceLine.AbsenceReasonID
,tblAbsenceLine.Date
,tblAbsenceLine.LikelyDuration
,tblAbsenceLine.WorkRelated
,tblAbsenceLine.NotifiedBy;


I can't tell without your seeing the structure of your tables and the
data in them and the appearance of your expected output, but just from
the way this Query is done, I'm thinking that it won't produce the
output you are looking for (if I'm wrong, then don't worry about what
I just said).


Sincerely,

Chris O.
Click to expand...
thanks, that is kindof along the lines, but it isn't actually giving me a
total. i need 1 total for the cumulative set of data. i have seen it done by
using a text box on a form before - is that the only possible way of
achieving this? or can i use a new field to make a cumulative total and then
use last(expr) to extract whatever value is there? thanks
Click to expand...

I refer you to my previous hint (seen above):


Sincerely,

Chris O.
 

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

Similar Threads

Access Query problem 1
subquery in group by query 3
Creating a total query 4
Sum different fields in a query 4
Computing total sales with a query 2
can't use an aggregate function in an expression? 4
Invalid use of null in query expression 3
"Aggregate" error when running query 3

Top