Running Totals in a query Microsoft article 290136

  • Thread starter Thread starter jale
  • Start date Start date
J

jale

Hi

I am trying to figure out RunningTotals form article.I figure out second
method.But I couldn't figure out first method.

Here is my sql.What is my fault?.

SELECT DatePart("yyyy",[OrderDate]) AS AYear, DatePart("m",[OrderDate]) AS
AMonth, DSum("Freight","Orders","DatePart('m';[OrderDate])<=" & [AMonth] & "
And DatePart('yyyy';[OrderDate])<=" & [AYear] & " ") AS RunTot,
Format([OrderDate],"mmm") AS FDate
FROM Orders
WHERE (((DatePart("yyyy",[OrderDate]))=1997))
GROUP BY DatePart("yyyy",[OrderDate]), DatePart("m",[OrderDate]),
Format([OrderDate],"mmm")
ORDER BY DatePart("yyyy",[OrderDate]), DatePart("m",[OrderDate]),
Format([OrderDate],"mmm");


ayear,amonth and Fdate are OK.But RunTot gives #error.

Any help will be appreciated.
 
jale said:
Hi

I am trying to figure out RunningTotals form article.I figure out second
method.But I couldn't figure out first method.

Here is my sql.What is my fault?.

SELECT DatePart("yyyy",[OrderDate]) AS AYear, DatePart("m",[OrderDate]) AS
AMonth, DSum("Freight","Orders","DatePart('m';[OrderDate])<=" & [AMonth] & "
And DatePart('yyyy';[OrderDate])<=" & [AYear] & " ") AS RunTot,
Format([OrderDate],"mmm") AS FDate
FROM Orders
WHERE (((DatePart("yyyy",[OrderDate]))=1997))
GROUP BY DatePart("yyyy",[OrderDate]), DatePart("m",[OrderDate]),
Format([OrderDate],"mmm")
ORDER BY DatePart("yyyy",[OrderDate]), DatePart("m",[OrderDate]),
Format([OrderDate],"mmm");

ayear,amonth and Fdate are OK.But RunTot gives #error.
Click to expand...

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

Try:

SELECT Year([OrderDate]) AS AYear, Month([OrderDate]) AS AMonth,
DSum("Freight","Orders","Month([OrderDate])<=" & [AMonth] & " And
Year([OrderDate])<=" & [AYear] ) AS RunTot,
Format([OrderDate],"mmm") AS FDate
FROM Orders
WHERE Year([OrderDate])=1997
GROUP BY Year([OrderDate]), Month([OrderDate]),
Format([OrderDate],"mmm")
ORDER BY 1, 2, 4

Use Year() & Month() functions - easier than DatePart().

You didn't need the empty string after [AYear] in the DSum() function.

ORDER BY numbers instead of column names: the numbers represent the
columns' ordinal number in the SELECT list.


- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQFJ1c4echKqOuFEgEQKIagCg/G/3mJ/coi0Lbup3590ut/oO390AniOk
aDVhmT/bKH+64MlWPaQghW5d
=P0Z1
-----END PGP SIGNATURE-----
 
Hi MgFoster

Thank you so much your query is worked.Why not Microsoft using your query in
this article?.Microsoft queries is not worked.

Best Wishes

MGFoster said:
jale said:
Hi

I am trying to figure out RunningTotals form article.I figure out second
method.But I couldn't figure out first method.

Here is my sql.What is my fault?.

SELECT DatePart("yyyy",[OrderDate]) AS AYear, DatePart("m",[OrderDate]) AS
AMonth, DSum("Freight","Orders","DatePart('m';[OrderDate])<=" & [AMonth] & "
And DatePart('yyyy';[OrderDate])<=" & [AYear] & " ") AS RunTot,
Format([OrderDate],"mmm") AS FDate
FROM Orders
WHERE (((DatePart("yyyy",[OrderDate]))=1997))
GROUP BY DatePart("yyyy",[OrderDate]), DatePart("m",[OrderDate]),
Format([OrderDate],"mmm")
ORDER BY DatePart("yyyy",[OrderDate]), DatePart("m",[OrderDate]),
Format([OrderDate],"mmm");

ayear,amonth and Fdate are OK.But RunTot gives #error.
Click to expand...

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

Try:

SELECT Year([OrderDate]) AS AYear, Month([OrderDate]) AS AMonth,
DSum("Freight","Orders","Month([OrderDate])<=" & [AMonth] & " And
Year([OrderDate])<=" & [AYear] ) AS RunTot,
Format([OrderDate],"mmm") AS FDate
FROM Orders
WHERE Year([OrderDate])=1997
GROUP BY Year([OrderDate]), Month([OrderDate]),
Format([OrderDate],"mmm")
ORDER BY 1, 2, 4

Use Year() & Month() functions - easier than DatePart().

You didn't need the empty string after [AYear] in the DSum() function.

ORDER BY numbers instead of column names: the numbers represent the
columns' ordinal number in the SELECT list.


- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQFJ1c4echKqOuFEgEQKIagCg/G/3mJ/coi0Lbup3590ut/oO390AniOk
aDVhmT/bKH+64MlWPaQghW5d
=P0Z1
-----END PGP SIGNATURE-----
Click to expand...
 
Back
Top