Strange Results with Aggregate functions (1 Viewer)

[wmphoto]

Hi, I hope for a little advice on an issue I'm having with an query, where the results of aggregate functions are clearly incorrect. I have a query which is supposed to take transactions (records which are used to create a pivot table which shows my cash-flow) with the status 'Sales Forecast' and combine them with values calculated from my sales forecast tables so that sales forecasts are included in my cash-flow.

To avoid writing too much about the design of the query, there is a screenshot of the offending query in design view, also here is the SQL code:

SELECT Transactions.[Transaction ID], Transactions.[Transaction Status], Transactions.[Forecast Date], [Transaction Serial Months].[Forecast Serial Month], Sum(IIf(IsNull([Forecast Deposits]),0,[Forecast Deposits])) AS [Month Forecast Deposits], Sum(IIf(IsNull([Forecast Balances]),0,[Forecast Balances])) AS [Month Forecast Balances]
FROM ((Transactions LEFT JOIN [Transaction Serial Months] ON Transactions.[Transaction ID] = [Transaction Serial Months].[Transaction ID]) LEFT JOIN [Sales Figures - Forecast Balances] ON [Transaction Serial Months].[Forecast Serial Month] = [Sales Figures - Forecast Balances].Month) LEFT JOIN [Sales Figures - Forecast Deposits] ON [Transaction Serial Months].[Forecast Serial Month] = [Sales Figures - Forecast Deposits].Month
GROUP BY Transactions.[Transaction ID], Transactions.[Transaction Status], Transactions.[Forecast Date], [Transaction Serial Months].[Forecast Serial Month]
HAVING (((Transactions.[Transaction Status])="Sales Forecast"));

I have highlighted the fields which will be aggregated, first I checked what the query returned without aggregating anything. Under month number 21 there are 3 items (forecasts for 3 different types of service which are to be added together so I get a single figure for the month to attach to the transaction representing the sales forecast for that month), the sum of the rightmost column should be £340 (screenshot below, datasheet - no aggregate)

However when I change the totals for the two rightmost columns from Group By to Sum, I see the following (screenshot below, datasheet - sum), the total is £1360, which is four times what it should be.

If I change the totals to Count, then I see the following (screenshot below, datasheet - count), which counts 12 items instead of 3 (I didn't bother changing the number formatting).

Does anyone know what's going on?

 

[Brianwarnock]

I would change your Having clause to a Where as logically you want to do your selection before the aggregation.

If you do this in the SQL remember that it comes before the Groupby else in the design grid drag the Transaction Status field into the grid a second time select where from the Totals drop down and uncheck Show

Don't know if this will solve it but logically it is more correct.

Brian

 

[wmphoto]

Thanks, well it doesn't fix the original problem, but it does make the SQL make more sense for me (as I don't know much SQL and was unfamiliar with 'Having'). I also tried removing all the fields which aren't essential as well, leaving only transaction ID, status and the two sales forecasts, just in case, but no luck there either.

 

[wmphoto]

Ahh, got it!

Well I don't know how, but I have worked out why. It is something to do with the fact that I have two different totals (one for balances received and one for deposits recieved, because they are calculated slightly differently). If I remove one and use the query purely to calculate balances then hey presto.

I should have thought more of the golden rule of queries. If your query doesn't work then try doing the same thing with two separate queries.

 

[Brianwarnock]

Thanks for coming back, I did wonder about the query setup but could not get my rusty old grey matter round it. Glad you solved it.

Brian