mattsteele8
Registered User.
- Local time
- Today, 11:39
- Joined
- Mar 18, 2009
- Messages
- 11
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
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