Running total issue

[mattsteele8]

hi,

trying to get a running total at the end of the following query with not much luck

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, ([2008_NSLSP_NF_P_rates]![2007_shopper_population]/[qryShopperPopTotal]![SumOf2007_Shopper_Population])*100 AS PercentTotalShoppers, DSum("PercentTotalShopper","qryFSP_Catchment_Definitions","[PenetrationPercent]>=" & [PenetrationPercent]) AS RunningSum
FROM 2008_NSLSP_NF_P_RATES LEFT JOIN qryShopperPopTotal ON [2008_NSLSP_NF_P_RATES].NSLSP_id = qryShopperPopTotal.NSLSP_id
WHERE ((([2008_NSLSP_NF_P_RATES].NSLSP_id)=963))
ORDER BY ([2007_shopper_population]/[postal_sector_population])*100 DESC;

all fields on the running total just come up with an #Error

cheers

 

[MarkK]

What I'd do is copy that expression to the immediate window and evaluate it there. Then you'll get more detailed error messages than SQL provides.

? DSum("PercentTotalShopper","qryFSP_Catchment_Definitions","[PenetrationPercent]>=" & [I]<hard code something here>[/I])

In addition, since PenetrationPercent is a calculation in the query, you may not be able to use it as criteria in your DSum(). You might have to restate the expression.
Also for readability you can alias your table names like this...

[FONT="Tahoma"][SIZE="1"]SELECT
  t1.NSLSP_id,
  t1.Postal_Sector,
  t1.Postal_Sector_Population,
  t1.2007_Shopper_Population,
  t1.2007_shopper_population/postal_sector_population * 100 AS PenetrationPercent,
  [2008_NSLSP_NF_P_rates]![2007_shopper_population] / q1.SumOf2007_Shopper_Population * 100 AS PercentTotalShoppers,
  DSum("PercentTotalShopper", "qryFSP_Catchment_Definitions", "[PenetrationPercent]>=" & t1.2007_shopper_population/postal_sector_population) As RunningSum
FROM 2008_NSLSP_NF_P_RATES AS t1 LEFT JOIN qryShopperPopTotal AS q1 ON t1.NSLSP_id = q1.NSLSP_id
WHERE t1.NSLSP_id = 963
ORDER BY ([2007_shopper_population]/[postal_sector_population])*100 DESC;[/SIZE][/FONT]

Finally, you might run the DSum() as a subquery instead, with syntax like

SELECT t1.Field1, t1.Field2, 
  (SELECT Sum(t2.Field2) AS Sum2
  FROM MyTable AS t2 
  WHERE t2.Field2 < t1.Field2) AS RunningSumField2
FROM MyTable AS t1

And that would probably run faster, if that matters.