Re: Sum,Sort, Format, and Date Range
From: MGFoster (me_at_privacy.com)
Date: 03/07/05
- Next message: Jeffiner11: "Military Time Conversion"
- Previous message: Kirk P.: "Error 3001 Invalid Argument"
- In reply to: finster26: "Sum,Sort, Format, and Date Range"
- Next in thread: fredg: "Re: Sum,Sort, Format, and Date Range"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 07 Mar 2005 22:01:04 GMT
finster26 wrote:
> 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-----
- Next message: Jeffiner11: "Military Time Conversion"
- Previous message: Kirk P.: "Error 3001 Invalid Argument"
- In reply to: finster26: "Sum,Sort, Format, and Date Range"
- Next in thread: fredg: "Re: Sum,Sort, Format, and Date Range"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
