Transposing rows into columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

anthony.pierdominici@lmco.com - 20 Dec 2006 17:54 GMT

I have data generated from a query in the format below:

Hours | Month_#
------------------------
534 | 0
723 | 1
341 | 2
625 | 3
...
876 | 72

I need to get the data into the following format:

0 | 1 | 2 | 3 | ... | 72
---------------------------
534 | 723 | 341 | ... | 876

A crosstab query almost works when I use the actual date value (instead
of the month #) but forces the output to be consolidated into 12 months
(all March hours regardless of year are clumped together under Mar)
instead of the 72 I need. When I try to select a different date format
("mm/yy" or "short date") than "mmm" to force the query to display all
72 months it gives me an error message saying "Property not found".
When I try to use the month # it also gives me the property not found
error.

I have also tried to use a UNION query but can't seem to get the
formating right.

Can someone please tell me how to do this in an access query or point
me in the right direction if I will have to use code?

Thank you,

Anthony

Reply to this Message

Marshall Barton - 20 Dec 2006 18:12 GMT

>I have data generated from a query in the format below:
>
[quoted text clipped - 21 lines]
>When I try to use the month # it also gives me the property not found
>error.

Use an expression something like:
DateDiff("m", datefield, date())

Are you sure you can fit 72 text boxes within the report's
Width?

Signature

Marsh
MVP [MS Access]

Reply to this Message

anthony.pierdominici@lmco.com - 20 Dec 2006 18:23 GMT

"Use an expression something like:
DateDiff("m", datefield, date())"

I used the DateDiff function to derive the Month# field already, so the
data is good. I am just having trouble making it transpose from rows
to columns.

"Are you sure you can fit 72 text boxes within the report's
Width?"

No, unfortuantely it doesn't fit. But I still need to be able to
display all 72 months to the user. I plan on implementing some type of
control that slides the time frame forward and backward so that all of
the data can be viewed, even if it isn't all at once.

Reply to this Message

Marshall Barton - 20 Dec 2006 19:48 GMT

>"Use an expression something like:
> DateDiff("m", datefield, date())"
>
>I used the DateDiff function to derive the Month# field already, so the
>data is good. I am just having trouble making it transpose from rows
>to columns.

I thought you said that a crosstab query worked, except for
the month issue. Using DateDiff in the crosstab should
resolve that issue.

>"Are you sure you can fit 72 text boxes within the report's
>Width?"
[quoted text clipped - 3 lines]
>control that slides the time frame forward and backward so that all of
>the data can be viewed, even if it isn't all at once.

That sounds interesting, let us know how you accomplish it.

Signature

Marsh
MVP [MS Access]

Reply to this Message

anthony.pierdominici@lmco.com - 20 Dec 2006 20:52 GMT

> >"Use an expression something like:
> > DateDiff("m", datefield, date())"
[quoted text clipped - 20 lines]
> Marsh
> MVP [MS Access]

Thanks for the response Marshall, I must have missed it earlier...

I tried to use the DateDiff function in the PIVOT statement of the
query and can't get it to work. Here is the line of the query I am
working on:

PIVOT DateDiff("m", [ACCT_MTH], date());

When I try to run it, I get this error message:

"The Microsoft jet database engine does not recognize
"Forms!frmFilter!cboSub_Group" as a valid field name or expression."

"Forms!frmFilter!cboSub_Group" is a combo box that contains different
criteria that filters the query I am working on.

Reply to this Message

Marshall Barton - 21 Dec 2006 01:33 GMT

>> >"Use an expression something like:
>> > DateDiff("m", datefield, date())"
[quoted text clipped - 32 lines]
>"Forms!frmFilter!cboSub_Group" is a combo box that contains different
>criteria that filters the query I am working on.

That probably means that the ACCT_MTH field contain a value
that DateDiff can not deal with. Possible a text string or
Null

Signature

Marsh
MVP [MS Access]

Reply to this Message

anthony.pierdominici@lmco.com - 20 Dec 2006 20:52 GMT

> >"Use an expression something like:
> > DateDiff("m", datefield, date())"
[quoted text clipped - 20 lines]
> Marsh
> MVP [MS Access]

Reply to this Message