2 Aggregate function

[Jigs]

Hi All,

I am using the following query to retrieve sum of gross total and nettotal :

SELECT BOOKINGNO, AGENT, CLIENT, DATE, ARRIVAL_DATE, NUMOFDAYS, GROSSTOTAL, TOTAL, SUM(GROSSTOTAL) AS SUMGROSS, SUM(TOTAL) AS SUMTOTAL FROM BOOKINGS
MONTH(ARRIVAL_DATE) = PICKMONTH
AND
YEAR(ARRIVAL_DATE) = PICKYEAR

where PICKMONTH and PICKYEAR are the pickup month and year of the travel.
The above query doesn't execute and give error as following

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

Please help me

Jigs

 

[Jibbadiah]

Aren't you missing a Where clause in this?!

 

[Jigs]

yea ofcourse Sorry,

SELECT BOOKINGNO, AGENT, CLIENT, DATE, ARRIVAL_DATE, NUMOFDAYS, GROSSTOTAL, TOTAL, SUM(GROSSTOTAL) AS SUMGROSS, SUM(TOTAL) AS SUMTOTAL FROM BOOKINGS
WHERE
MONTH(ARRIVAL_DATE) = PICKMONTH
AND
YEAR(ARRIVAL_DATE) = PICKYEAR

Jigs

 

[Jibbadiah]

From one of your previous posts I can see that ACH_REF is a field in your database... but not part of this current query... try adding it to your select statement.

 

[Jigs]

HI Jibbadiah,

ACH_REF field is give name "BOOKINGNO"

Jigs

 

[Jibbadiah]

Can you post a sample database? I am only able to use access 97 from work. But at least other folk can understand your problem and may be able to help. Don't include any private data.

 

[Jigs]

Hi ,

These might give u an idea

I had attached JigsReport.xls which might give u an idea about how I want the report to be generate.



Jigs

 

[Jibbadiah]

You are attempting to do a group by for the totals, but at the same time you are wanting to display the individual records. Remove the sum calculations from your query. In your report set the totals controls as unbound and set the control source as =Sum([Bookings]![Nett])