Sum of column in query

[mattsteele8]

hi,

just wondering if anyone could help with a small issue i have. i am trying to run the following query

SELECT [2008_NSLSP_NF_P_RATES].NSLSP_id, [2008_NSLSP_NF_P_RATES].Postal_Sector, [2008_NSLSP_NF_P_RATES].Postal_Sector_Population, [2008_NSLSP_NF_P_RATES].[2007_Shopper_Population], ([2007_shopper_population]/[postal_sector_population])*100 AS PenetrationPercent, ([2007_shopper_population]/SUM)*100 AS PercentTotalShoppers
FROM 2008_NSLSP_NF_P_RATES
WHERE ((([2008_NSLSP_NF_P_RATES].NSLSP_id)=963))
ORDER BY ([2007_shopper_population]/[postal_sector_population])*100 DESC;

where SUM is the sum of the [2007_shopper_population] field but i can't find any way of doing it. i have tried doing the sum with another query and then linking to it but i think that is quite a dirty way to do it.

if i try and do it this way

SELECT [2008_NSLSP_NF_P_RATES].NSLSP_id, [2008_NSLSP_NF_P_RATES].Postal_Sector, [2008_NSLSP_NF_P_RATES].Postal_Sector_Population, [2008_NSLSP_NF_P_RATES].[2007_Shopper_Population], ([2007_shopper_population]/[postal_sector_population])*100 AS PenetrationPercent, ([2007_shopper_population]/Sum([2007_shopper_population]))*100 AS PercentTotalShoppers
FROM 2008_NSLSP_NF_P_RATES
WHERE ((([2008_NSLSP_NF_P_RATES].NSLSP_id)=963))
ORDER BY ([2007_shopper_population]/[postal_sector_population])*100 DESC;

i get the error "You tried to execute a query that does not include the specified expression 'NSLSP_ID' as part of the aggregate function"

also once this has been fixed i will also need to keep a running total of the 'PercentTotalShoppers' as a separate field

any help would be much appreciated

many thanks

 

[ajetrumpet]

please fix the SQL so we can read it...

 

[jardiamj]

It's kind of hard for me to read that SQL also but if I understand well what you want to do, the SQL statement for your query should look like this:

SELECT [2008_NSLSP_NF_P_RATES].NSLSP_id, [2008_NSLSP_NF_P_RATES].Postal_Sector, [2008_NSLSP_NF_P_RATES].Postal_Sector_Population, [2008_NSLSP_NF_P_RATES].[2007_Shopper_Population], ([2007_shopper_population]/[postal_sector_population])*100 AS PenetrationPercent, ([2007_shopper_population]/(SELECT sum([2007_shopper_population]) FROM 2008_NSLSP_NF_P_RATES))*100 AS PercentTotalShoppers
FROM 2008_NSLSP_NF_P_RATES
WHERE ((([2008_NSLSP_NF_P_RATES].NSLSP_id)=963))
ORDER BY ([2007_shopper_population]/[postal_sector_population])*100 DESC;

But I would do it my way, building the SQL using VBA. I would get the total of the 2007_shopper_population by using Dsum and then just pass the value to the sql statement. I don't know if is better or not I just like it.
If any one here can tell me if it has some disadvantages, I would appreciate an advice.

this is how I would do it:
Create and empty query and name it, let's say "DynamicQry"... I like this name...lol
And then in the event from where you want to run the Query I would write my code, something like this:

Dim strSQL As String, qryName As String, qdf As DAO.QueryDef
Dim ShoppersSum As Integer

queryName = "DynamicQry" '----> the name of the query will get the SQL statement passed to

ShopperSum = DSum("[2007_shopper_population]", "[2008_NSLSP_NF_P_RATES]")

strSQL = "SELECT [2008_NSLSP_NF_P_RATES].NSLSP_id, [2008_NSLSP_NF_P_RATES].Postal_Sector, [2008_NSLSP_NF_P_RATES].Postal_Sector_Population, [2008_NSLSP_NF_P_RATES].[2007_Shopper_Population], ([2007_shopper_population]/[postal_sector_population])*100 AS PenetrationPercent, ([2007_shopper_population]/" & ShopperSum & ")*100 AS PercentTotalShoppers" & _
" FROM 2008_NSLSP_NF_P_RATES" & _
" WHERE ((([2008_NSLSP_NF_P_RATES].NSLSP_id) = 963))" & _
"ORDER BY ([2007_shopper_population]/[postal_sector_population])*100 DESC;"

'---Set the qry and run it--------------------
Set qdf = CurrentDb.QueryDefs(qryName)
qdf.SQL = strSQL
DoCmd.OpenQuery (qryName)
'---Empty the Object we have created----------
Set qdf = Nothing

I hope it will help!
Have a nice day. Cheers!