Transposing rows into columns

[anthony.pierdominici]

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

 

[Marshall Barton]

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.

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

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

 

[anthony.pierdominici]

"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.

 

[Marshall Barton]

"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?"

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.

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

 

[anthony.pierdominici]

Try:

PIVOT FORMAT(YourDateColumn, "yyyy mm");

The crosstab query will be based on the table(s), not your query of course,
and will use whatever aggregation operator is appropriate (SUM?) in the
TRANSFORM clause.

Ken Sheridan
Stafford, England

Thanks for the tip Ken, I tried your suggestion and here is what I came
up with:

PIVOT Format([ACCT_MTH],"mmm yy") In ("Jan 07","Feb 07","Mar 07","Apr
07","May 07","Jun 07","Jul 07","Aug 07","Sep 07","Oct 07","Nov 07","Dec
07");

This gives me accurate data for all of 2007 and I could use this method
to show the next 72 months but there is one problem. The first month
in the 72 month span is always the current month so every month the
span will be different.

 

[anthony.pierdominici]

Try:

PIVOT FORMAT(YourDateColumn, "yyyy mm");

The crosstab query will be based on the table(s), not your query of course,
and will use whatever aggregation operator is appropriate (SUM?) in the
TRANSFORM clause.

Ken Sheridan
Stafford, England

Thanks for the tip Ken, I tried your suggestion and here is what I came
up with:

PIVOT Format([ACCT_MTH],"mmm yy") In ("Jan 07","Feb 07","Mar 07","Apr
07","May 07","Jun 07","Jul 07","Aug 07","Sep 07","Oct 07","Nov 07","Dec
07");

This gives me accurate data for all of 2007 and I could use this method
to show the next 72 months but there is one problem. The first month
in the 72 month span is always the current month so every month the
span will be different.

 

[anthony.pierdominici]

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




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

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.

 

[anthony.pierdominici]

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




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

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.

 

[Marshall Barton]

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




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

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.

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

 

[anthony.pierdominici]

Thanks Ken.

I will give that a shot tomorrow or when I get back to work next year.
I appreciate both your and Marshall's advice on this.

Happy Holidays!

 

[anthony.pierdominici]

Thanks, Ken. I had never tried to modify a query via code before, it
worked perfectly...

---------------------------------------------------------------------------------------

Public Function GetMonthList(intMonths As Integer, _
Optional varStartDate As Variant) As String

Dim n As Integer

' start from now if no start date specified
If IsMissing(varStartDate) Then
varStartDate = VBA.Date
End If

For n = 1 To intMonths
GetMonthList = GetMonthList & _
",""" & Format(varStartDate, "mmm yy") & """"
varStartDate = DateAdd("m", 1, varStartDate)
Next n

' remove leading comma
GetMonthList = Mid$(GetMonthList, 2)

End Function

Public Function ChangeSQL()

Dim db As DAO.QueryDef
Dim strSQL As String
Dim strQryName As String
Dim strMonthList As String

strQryName = "qryBudget3"

strMonthList = GetMonthList(73, DateSerial(Year(Date), Month(Date),
1))

strSQL = ("TRANSFORM Sum(qryBudget2.BCWS_HRS) AS SumOfBCWS_HRS " &
_
"SELECT qryBudget2.[X-Tab Value]" & _
"FROM qryBudget2 " & _
"GROUP BY qryBudget2.[X-Tab Value] " & _
"PIVOT Format([ACCT_MTH],""mmm yy"") In(" & strMonthList &
");")

Set db = CurrentDb.QueryDefs(strQryName)
db.SQL = strSQL
db.Close

End Function

 

[anthony.pierdominici]

If you are going to use the crosstab query as the RecordSource of a report
you will have to use a report with dynamic column headings of course. You'll
find an example in the Solutions database, which originally shipped with
Access 97, but can now be downloaded from:

http://www.mvps.org/access/resources/downloads.htm

Its 'Orders.mdb and Solutions.mdb' in the list of downloads.

Ken Sheridan
Stafford, England

I downloaded the database you mentioned above but can't seem to find
the dynamic column headings example you are refering to.

It turns out that all I need to do to make this work is to take the
column names that I got from the crosstab query and convert them to
numbers (0,1,2,3,... 72). Anyone have any ideas on how to do that?

Thanks for your help!