Running Total Error

[solsen via AccessMonster.com]

Hi,

I am trying to calculate a running total on an order field using subqueries
but every time I run the code below Access shuts down. Any idea as to why
this is happening?


SELECT a.CUSTOMER_N, a.MATERIAL_N, a.FISCAL_PER, a.SumOfTotal_QU, a.
SumOfNET_REVENU, a.[Period Calc], a.Orders

(SELECT Sum([Orders])
FROM [SGI_1_Pinnacle Sales] AS B
WHERE a.CUSTOMER_N = b.CUSTOMER_N
AND a.FISCAL_PER<= b.FISCAL_PER) as RunSum

FROM [SGI_1_Pinnacle Sales] as A;

 

[MGFoster]

Hi,

I am trying to calculate a running total on an order field using subqueries
but every time I run the code below Access shuts down. Any idea as to why
this is happening?


SELECT a.CUSTOMER_N, a.MATERIAL_N, a.FISCAL_PER, a.SumOfTotal_QU, a.
SumOfNET_REVENU, a.[Period Calc], a.Orders

(SELECT Sum([Orders])
FROM [SGI_1_Pinnacle Sales] AS B
WHERE a.CUSTOMER_N = b.CUSTOMER_N
AND a.FISCAL_PER<= b.FISCAL_PER) as RunSum

FROM [SGI_1_Pinnacle Sales] as A;

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're missing a comma after the a.Orders in the SELECT clause. There's
a space between a. and SumOfNet_Revenu. I don't believe these syntax
errors would cause Access to crash. The crash might be caused by a
corrupt QueryDef. After you make the corrections save the query w/o
closing it and copy the SQL and put it in new QueryDef. Run it to see
if the new QueryDef runs OK - assuming a new QueryDef will not be
corrupted.

You could also create a new query that uses the DLookUp() function.
That will eliminate the sub-query in the SELECT clause. I've had
problems w/ Reports not accepting queries with sub-queries in the SELECT
clause. MS recommends use of the domain aggregate functions in that
case.

SELECT a.CUSTOMER_N, a.MATERIAL_N, a.FISCAL_PER, a.SumOfTotal_QU,
a.SumOfNET_REVENU, a.[Period Calc], a.Orders,

DLookup("Orders", "[SGI_1_Pinnacle Sales]", "CUSTOMER_N=" & A.CUSTOMER_N
& " AND FISCAL_PER>=" & A.FISCAL_PER) As RunSum

FROM [SGI_1_Pinnacle Sales] As A

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSd5oVYechKqOuFEgEQIwgwCgnDWcbYwRyNavN2EcXcNrvW9zvL4AoLxo
U61U12rJxppoM0suq7LyM5U/
=waZb
-----END PGP SIGNATURE-----

 

[solsen via AccessMonster.com]

Thanks fr the quick response - I think the domain aggregates will work better.
I want to use the Dsum function but I am only getting the total number of
orders, not the individual customers orders. Customer_N field is text, not
sure if that is influencing this or not.

SELECT A.CUSTOMER_N, A.MATERIAL_N, A.FISCAL_PER, A.SumOfTotal_QU, A.
SumOfNET_REVENU, A.[Period Calc], A.Orders, DSum("Orders","[SGI_1_Pinnacle
Sales]","[CUSTOMER_N] = ' " & [A].[CUSTOMER_N] & " ' " And "FISCAL_PER>=" &
[A].[FISCAL_PER]) AS RunSum
FROM [SGI_1_Pinnacle Sales] AS A;

Hi,

[quoted text clipped - 11 lines]

FROM [SGI_1_Pinnacle Sales] as A;

You're missing a comma after the a.Orders in the SELECT clause. There's
a space between a. and SumOfNet_Revenu. I don't believe these syntax
errors would cause Access to crash. The crash might be caused by a
corrupt QueryDef. After you make the corrections save the query w/o
closing it and copy the SQL and put it in new QueryDef. Run it to see
if the new QueryDef runs OK - assuming a new QueryDef will not be
corrupted.

You could also create a new query that uses the DLookUp() function.
That will eliminate the sub-query in the SELECT clause. I've had
problems w/ Reports not accepting queries with sub-queries in the SELECT
clause. MS recommends use of the domain aggregate functions in that
case.

SELECT a.CUSTOMER_N, a.MATERIAL_N, a.FISCAL_PER, a.SumOfTotal_QU,
a.SumOfNET_REVENU, a.[Period Calc], a.Orders,

DLookup("Orders", "[SGI_1_Pinnacle Sales]", "CUSTOMER_N=" & A.CUSTOMER_N
& " AND FISCAL_PER>=" & A.FISCAL_PER) As RunSum

FROM [SGI_1_Pinnacle Sales] As A

HTH,