Sorting and Date Order in Crosstab

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Two questions: See query text belo

1: "Can't order by aggregate field" I want to sort my crosstab results by the row totals. How can I do that

2: My column headers are dates and they display for example 10/1/04, 11/1/04, 9/1/04 instead of in sequential order from left to right. Can I force a sequential order

Query:
TRANSFORM Sum(qryTmp.bps) AS SumOfbp
SELECT qryTmp.ticker, Sum(qryTmp.bps) AS [Total Of bps
FROM qryTm
GROUP BY qryTmp.ticke
PIVOT Format(qryTmp.ddate,'Short Date')

Thanks for any help
 
From a posting yesterday in the Reports NG:
If the dates are your column headings then you don't need to create dynamic
headings. Consider creating a report that has a specific number of dates.
Assuming you want 14 dates that end on the date entered by a user in
Forms!frmDate!txtEndDate.
Your column heading expression would be
ColHead:"Day" & DateDiff("d",[DateFld], Forms!frmDate!txtEndDate)
Make sure you set the Query|Parameters
Forms!frmDate!txtEndDate DateTime
and then set the Column Headings property of the query to:
"Day0","Day1",..."Day9"
Day0 will have the values from the EndDate and Day9 will be 9 days earlier.
Your report columns/fields will always be the same names. The dates they
represent will change. To create column headings in your report, use text
boxes with control sources like:
=DateAdd("d",-0,Forms!frmDate!txtEndDate)
=DateAdd("d",-1,Forms!frmDate!txtEndDate)
=DateAdd("d",-2,Forms!frmDate!txtEndDate)
etc


--
Duane Hookom
MS Access MVP


Bob Ewers said:
Two questions: See query text below

1: "Can't order by aggregate field" I want to sort my crosstab results by
Click to expand...
the row totals. How can I do that?
2: My column headers are dates and they display for example 10/1/04,
Click to expand...
11/1/04, 9/1/04 instead of in sequential order from left to right. Can I
force a sequential order?
Query:
TRANSFORM Sum(qryTmp.bps) AS SumOfbps
SELECT qryTmp.ticker, Sum(qryTmp.bps) AS [Total Of bps]
FROM qryTmp
GROUP BY qryTmp.ticker
PIVOT Format(qryTmp.ddate,'Short Date');

Thanks for any help
Click to expand...
 
Back
Top