Sum,Sort, Format, and Date Range

[Guest]

I have a DATE/TIME field and a Currency field.

I am formatting the date to "mmmm, yyyy" then GROUPING the by month/year and
SUM the Currency field.

I want to be able to select records between dates. I cannot use criteria in
the formatted date field to select the records bewteen dates. Formatted date
is a String. I also want to sort the formatted Date field but I it will not
sort correctly because it's a String.

SELECT DISTINCTROW Format([DIE HEADINGS].[DATE],'mmmm yyyy') AS [DATE By
Month], Sum([DIE HEADINGS].[COST DIECOST]) AS [Sum Of COST DIECOST]
FROM [DIE HEADINGS]
GROUP BY Format([DIE HEADINGS].[DATE],'mmmm yyyy');

What is the appropriate way to add the criteria and sort the records by
Month/Year?

Thanks,

Bob

 

[MGFoster]

I have a DATE/TIME field and a Currency field.

I am formatting the date to "mmmm, yyyy" then GROUPING the by month/year and
SUM the Currency field.

I want to be able to select records between dates. I cannot use criteria in
the formatted date field to select the records bewteen dates. Formatted date
is a String. I also want to sort the formatted Date field but I it will not
sort correctly because it's a String.

SELECT DISTINCTROW Format([DIE HEADINGS].[DATE],'mmmm yyyy') AS [DATE By
Month], Sum([DIE HEADINGS].[COST DIECOST]) AS [Sum Of COST DIECOST]
FROM [DIE HEADINGS]
GROUP BY Format([DIE HEADINGS].[DATE],'mmmm yyyy');

What is the appropriate way to add the criteria and sort the records by
Month/Year?

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

Use the original [Date] column in a WHERE clause to set the criteria for
the query. BTW, 'Date' is a reserved keyword in most RDBMS and,
therefore, shouldn't be used as a column name.

Usually, it is best to let query's get the raw data & let the display
layer (reports, forms) "pretty it up." Therefore, you'd just let the
query produce numbers & let the display layer apply words to those
numbers. In this case the number->word transform should be on the
month.

Try this:

PARAMETERS [Start Date?] Date, [End Date?] Date;
SELECT DISTINCTROW Year([DATE]) As Yr, Month([Date]) As Mon,
Sum([COST DIECOST]) AS [Sum Of COST DIECOST]
FROM [DIE HEADINGS]
WHERE [Date] Between [Start Date?] And [End Date?]
GROUP BY Year([DATE]), Month([Date])
ORDER BY Year([DATE]), Month([Date])

In Access SQL, the ORDER BY clause is not needed, since the GROUP BY
clause is exactly the same, and, in Access SQL, the GROUP BY orders its
column list in ascending order.

Put the above SQL in a QueryDef and in the QBE design grid on the Mon
column - right click & select Properties. Under the General tab in the
Format property, enter "mmmm" (without the quotes). When the query runs
it will display the month name, and the order will be in chronological
order. The use of the Format property on the QueryDef is an adjustment
of the display layer. The underlying data are still numbers, the Format
property changes the display of the underlying data.

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

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

iQA/AwUBQizPLIechKqOuFEgEQId1wCff7DumRRZ5J/KZ1of54eeWxhCuVoAoP60
ZA8rFz/vOBvETlxyQiigzt+4
=NQD3
-----END PGP SIGNATURE-----

 

[fredg]

I have a DATE/TIME field and a Currency field.

I am formatting the date to "mmmm, yyyy" then GROUPING the by month/year and
SUM the Currency field.

I want to be able to select records between dates. I cannot use criteria in
the formatted date field to select the records bewteen dates. Formatted date
is a String. I also want to sort the formatted Date field but I it will not
sort correctly because it's a String.

SELECT DISTINCTROW Format([DIE HEADINGS].[DATE],'mmmm yyyy') AS [DATE By
Month], Sum([DIE HEADINGS].[COST DIECOST]) AS [Sum Of COST DIECOST]
FROM [DIE HEADINGS]
GROUP BY Format([DIE HEADINGS].[DATE],'mmmm yyyy');

What is the appropriate way to add the criteria and sort the records by
Month/Year?

Thanks,

Bob

To add emphasis to MGFoster's reply regarding your use of Date as a
field name:

See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'