aggregate query error 1

[Stuartp]

I have successfully written the following query that calculates the difference between two fields (deliveries and invoices) and divides this figure by deliveries/5 (for the week):

Days to Invoice: (Sum([deliveries value]-[invoices value]))/(Sum([deliveries value]/5))

I now want the same total for the month, where an entry exists for each month. Entering 30 instead of 5 works fine, but since not all months have 30 days I had the idea of counting by the number of entries, hence I wrote:

Days to Invoice: (Sum([deliveries value]-[invoices value]))/(Sum([deliveries value]/Count([deliveries value])))

But this now just produces a "Cannot have aggregate function in expression" error.

Can someone please help me to make this right?

Many thanks,

Stuart

 

[tlbroadbent]

Could you post the entire query, please? It is available in the SQL View of the query designer.

 

[Stuartp]

Here is the full SQL:

SELECT (Sum([deliveries value]-[invoices value]))/(Sum([deliveries value]/Count([deliveries value]))) AS [Days to Invoice]
FROM dailyTransactions;

Thanks

Stuart

 

[tlbroadbent]

The query has the Count included in the Sum. Move one of the right parentheses so the query will perform the Sum and then divide by the Count.

Currently, the query reads...
(Sum([deliveries value]/Count([deliveries value])))

It should be...
(Sum([deliveries value])/Count([deliveries value]))

 

[Stuartp]

Thanks. this has solved it!