Descending last 12 month sort

[Dave]

I am doing a crosstab query with month year in the results. I want to sort
by a descending month\year across the top. Ex Feb 06, Jan 06, Dec 05, Nov
05 ....

Is this possible?

Thanks

Dave

 

[Vincent Johns]

I can think of 2 or 3 ways to do that.

(1) In Query Design View on your Crosstab Query, you could right-click
on the upper window and open the Properties sheet. Specify the desired
headings, in order, in the "Column Headings" property. Or, in the SQL,
specify the headings in the PIVOT clause.

Disadvantage: You'll have to redo this as new months appear. This
Query may be complex enough that it will be a pain to maintain.

(2) In the Query on which your Crosstab Query is based, prefix a
(calculated) sequential number to each date, and use this calculated
field for the column headings. Example: "(1) Feb 06", "(2) Jan 06",
"(3) Dec 05", etc.

Disadvantages: Column headers are longer than you might want. The
extra numbers are ugly, since they're not needed by the user.

(3) Base a Select Query on the results of your Crosstab Query and put
the fields in the order that you want to use. For example, the [Feb 06]
field will precede the [Jan 06] field.

Disadvantage: You'll have to redo this one each month, too, as in
choice (1), but it'll be simpler than the Crosstab Query to maintain --
mostly just a list of fields, in the order you choose.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Duane Hookom]

Set the column heading expression to something like:

ColHead: "M" & DateDiff("m",[YourDateField], Date())
Then set the column headings property to: "M0","M1","M2",..."M11"
M0 will show the current month with M11 showing 11 months ago. You can
easily display the actual months as column labels when you get to your
report or form.

 

[Dave]

This is pretty slick.

I don't have data over 3 months at this time, will this continue to build
after 12 months.

I will need to create a solution that only uses 12 months.

Thanks to both of you for your responses.

Dave

Set the column heading expression to something like:

ColHead: "M" & DateDiff("m",[YourDateField], Date())
Then set the column headings property to: "M0","M1","M2",..."M11"
M0 will show the current month with M11 showing 11 months ago. You can
easily display the actual months as column labels when you get to your
report or form.

--
Duane Hookom
MS Access MVP
--

I am doing a crosstab query with month year in the results. I want to
sort by a descending month\year across the top. Ex Feb 06, Jan 06, Dec
05, Nov 05 ....

Is this possible?

Thanks

Dave

 

[Duane Hookom]

Setting the Column Headings property will restrict the columns to the last
12 months.

--
Duane Hookom
MS Access MVP
--

This is pretty slick.

I don't have data over 3 months at this time, will this continue to build
after 12 months.

I will need to create a solution that only uses 12 months.

Thanks to both of you for your responses.

Dave

Set the column heading expression to something like:

ColHead: "M" & DateDiff("m",[YourDateField], Date())
Then set the column headings property to: "M0","M1","M2",..."M11"
M0 will show the current month with M11 showing 11 months ago. You can
easily display the actual months as column labels when you get to your
report or form.

--
Duane Hookom
MS Access MVP
--

I am doing a crosstab query with month year in the results. I want to
sort by a descending month\year across the top. Ex Feb 06, Jan 06, Dec
05, Nov 05 ....

Is this possible?

Thanks

Dave

 

[Dave]

Thanks

Setting the Column Headings property will restrict the columns to the last
12 months.

--
Duane Hookom
MS Access MVP
--

This is pretty slick.

I don't have data over 3 months at this time, will this continue to build
after 12 months.

I will need to create a solution that only uses 12 months.

Thanks to both of you for your responses.

Dave

Set the column heading expression to something like:

ColHead: "M" & DateDiff("m",[YourDateField], Date())
Then set the column headings property to: "M0","M1","M2",..."M11"
M0 will show the current month with M11 showing 11 months ago. You can
easily display the actual months as column labels when you get to your
report or form.

--
Duane Hookom
MS Access MVP
--

I am doing a crosstab query with month year in the results. I want to
sort by a descending month\year across the top. Ex Feb 06, Jan 06, Dec
05, Nov 05 ....

Is this possible?

Thanks

Dave