Problem with aggregate query

  • Thread starter Thread starter klr
  • Start date Start date
K

klr

I have a query that has 4 fields - Officer; Team; Finalcost; Date
completed (charged).

Officer is set to Group By
Team - criteria reads <> "DI"
Cost is set to Sum

The field I am having problems with is the Date charged field - I need
to pull through all records dated previous month. I am using Between
DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0) in the criteria field. The
only fields I actually need to display in the query result are Officer
and Total (grouped by Officer).

When I run the query without Date field, the officer totals are
grouped. When I add the Date field, the records from August are
displayed, however the officer grouping is no longer applied.

I am working in Design view of Access as not au fait with SQL however
SQL expression reas as follows:-

SELECT tEnq_main.officer, Sum(tEnq_main.finalcost) AS SumOffinalcost,
tEnq_main.[date completed (charged)]
FROM tEnq_main
GROUP BY tEnq_main.officer, tEnq_main.team, tEnq_main.[date completed
(charged)]
HAVING ((Not (tEnq_main.team)="Data Insight") AND ((tEnq_main.[date
completed (charged)]) Between
DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0)));

Can anyone advise on where I am going wrong? Many thanks.

KLR
 
Under the date field change GROUP BY to WHERE. To increase efficiency, you
should also change Group By to WHERE under the Team field.

Your SQL statement should look something like

SELECT tEnq_main.officer, Sum(tEnq_main.finalcost) AS SumOffinalcost
FROM tEnq_main

WHERE((Not (tEnq_main.team)="Data Insight")
AND ((tEnq_main.[date completed (charged)]) Between
DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0)))
GROUP BY tEnq_main.officer


If you apply criteria to a group by field, the criteria gets applied AFTER
the grouping takes place. If you apply the criteria with a where clause the
criteria gets applied before the grouping takes place. For efficiency, you
should only apply criteria to aggregated values when you you need to check
the aggregated value (Sum, Min, Max). When you are only grouping by a
field, use an additional copy of the field (in the grid), use WHERE and
apply the criteria there.
 
Thanks very much for your post - that has really helped explain SQL
more clearly to me and solved my problem to boot. Great!

John said:
Under the date field change GROUP BY to WHERE. To increase efficiency, you
should also change Group By to WHERE under the Team field.

Your SQL statement should look something like

SELECT tEnq_main.officer, Sum(tEnq_main.finalcost) AS SumOffinalcost
FROM tEnq_main

WHERE((Not (tEnq_main.team)="Data Insight")
AND ((tEnq_main.[date completed (charged)]) Between
DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0)))
GROUP BY tEnq_main.officer


If you apply criteria to a group by field, the criteria gets applied AFTER
the grouping takes place. If you apply the criteria with a where clause the
criteria gets applied before the grouping takes place. For efficiency, you
should only apply criteria to aggregated values when you you need to check
the aggregated value (Sum, Min, Max). When you are only grouping by a
field, use an additional copy of the field (in the grid), use WHERE and
apply the criteria there.


klr said:
I have a query that has 4 fields - Officer; Team; Finalcost; Date
completed (charged).

Officer is set to Group By
Team - criteria reads <> "DI"
Cost is set to Sum

The field I am having problems with is the Date charged field - I need
to pull through all records dated previous month. I am using Between
DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0) in the criteria field. The
only fields I actually need to display in the query result are Officer
and Total (grouped by Officer).

When I run the query without Date field, the officer totals are
grouped. When I add the Date field, the records from August are
displayed, however the officer grouping is no longer applied.

I am working in Design view of Access as not au fait with SQL however
SQL expression reas as follows:-

SELECT tEnq_main.officer, Sum(tEnq_main.finalcost) AS SumOffinalcost,
tEnq_main.[date completed (charged)]
FROM tEnq_main
GROUP BY tEnq_main.officer, tEnq_main.team, tEnq_main.[date completed
(charged)]
HAVING ((Not (tEnq_main.team)="Data Insight") AND ((tEnq_main.[date
completed (charged)]) Between
DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0)));

Can anyone advise on where I am going wrong? Many thanks.

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