Need to transform Access columns to rows

[Susan L]

I have two monthly reports that need be replicated for our customer in the
current (manual) format. For both reports, there is data in separate columns
that needs to be reported in rows, with totals across the page for each month.

Here's an example of some raw data, after a query that sums numerous monthly
entries.
Date LO1 CSI MSC etc.
1/31/2008 2,544 2,346 2,136
2/28/2008 2,554 2,360 2,178
3/31/2008 2,576 2,382 2,206

Here's an example of the desired report output.
Function Code Jan Feb Mar etc.
LO1 2,544 2,554 2,576
CSI 2,346 2,360 2,382
MSC 2,136 2,178 2,206

I can't get a cross-tab to work, but there must be a way to transform the
data structure for a report? I've searched for a solution, but have had no
luck. Is a union query required here? (Yikes! -- am pretty much a novice on
them). Thanks for any help you can provide.

 

[Bob Barrows [MVP]]

I have two monthly reports that need be replicated for our customer
in the current (manual) format. For both reports, there is data in
separate columns that needs to be reported in rows, with totals
across the page for each month.

Here's an example of some raw data, after a query that sums numerous
monthly entries.
Date LO1 CSI MSC etc.
1/31/2008 2,544 2,346 2,136
2/28/2008 2,554 2,360 2,178
3/31/2008 2,576 2,382 2,206

Was this a crosstab query? If so, you may be starting at the wrong
point. What does the raw data actually look like?
If not, then you may have a database design issue.

Here's an example of the desired report output.
Function Code Jan Feb Mar etc.
LO1 2,544 2,554 2,576
CSI 2,346 2,360 2,382
MSC 2,136 2,178 2,206

I can't get a cross-tab to work, but there must be a way to transform
the data structure for a report? I've searched for a solution, but
have had no luck. Is a union query required here? (Yikes! -- am
pretty much a novice on them). Thanks for any help you can provide.

The first step is to use (you guessed it!) a union query to "fold" the
data. At the same time, calculating the monthname from the date:

SELECT Format([Date],"MMM") As Mth, 'LO1' as Function Code,
[LO1] as MonthTotal From queryname
UNION ALL
SELECT Format([Date],"MMM"), 'CSI' ,[CSI] as MonthTotal
From queryname
UNION ALL
etc.

Save that query and use it as the source of a crosstab query

 

[Susan L]

Bob, thanks so much for your fast response. I'll try this out right away.

And no, the data was not generated by a crosstab; this is the way the table
is structured, unfortunately. The raw data consists of daily entries of the
data in each of the columns. (Table is "cast in stone" for the US
government...what more can I say...).

Will try this out immediately and post back.
--
susan

I have two monthly reports that need be replicated for our customer
in the current (manual) format. For both reports, there is data in
separate columns that needs to be reported in rows, with totals
across the page for each month.

Here's an example of some raw data, after a query that sums numerous
monthly entries.
Date LO1 CSI MSC etc.
1/31/2008 2,544 2,346 2,136
2/28/2008 2,554 2,360 2,178
3/31/2008 2,576 2,382 2,206

Was this a crosstab query? If so, you may be starting at the wrong
point. What does the raw data actually look like?
If not, then you may have a database design issue.

Here's an example of the desired report output.
Function Code Jan Feb Mar etc.
LO1 2,544 2,554 2,576
CSI 2,346 2,360 2,382
MSC 2,136 2,178 2,206

I can't get a cross-tab to work, but there must be a way to transform
the data structure for a report? I've searched for a solution, but
have had no luck. Is a union query required here? (Yikes! -- am
pretty much a novice on them). Thanks for any help you can provide.

The first step is to use (you guessed it!) a union query to "fold" the
data. At the same time, calculating the monthname from the date:

SELECT Format([Date],"MMM") As Mth, 'LO1' as Function Code,
[LO1] as MonthTotal From queryname
UNION ALL
SELECT Format([Date],"MMM"), 'CSI' ,[CSI] as MonthTotal
From queryname
UNION ALL
etc.

Save that query and use it as the source of a crosstab query



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Susan L]

Hi, Bob.

I've started the union query, but am getting an error about the SELECT
statement either uses a reserved word, an argument name mispelled, or
incorrect punctuation.

Here's what I've done so far:

SELECT Format([RPT_DT], "MMM") As Mth, 'LO1' as Function Code, [LO1] as
Month Total From qry_EXA_Count2_SumTotals
UNION ALL
SELECT Format([RPT_DT], "MMM") As Mth, 'CSI' as Function Code, [CSI] as
Month Total From qry_EXA_Count2_SumTotals
UNION ALL;
--
susan

I have two monthly reports that need be replicated for our customer
in the current (manual) format. For both reports, there is data in
separate columns that needs to be reported in rows, with totals
across the page for each month.

Here's an example of some raw data, after a query that sums numerous
monthly entries.
Date LO1 CSI MSC etc.
1/31/2008 2,544 2,346 2,136
2/28/2008 2,554 2,360 2,178
3/31/2008 2,576 2,382 2,206

Was this a crosstab query? If so, you may be starting at the wrong
point. What does the raw data actually look like?
If not, then you may have a database design issue.

Here's an example of the desired report output.
Function Code Jan Feb Mar etc.
LO1 2,544 2,554 2,576
CSI 2,346 2,360 2,382
MSC 2,136 2,178 2,206

I can't get a cross-tab to work, but there must be a way to transform
the data structure for a report? I've searched for a solution, but
have had no luck. Is a union query required here? (Yikes! -- am
pretty much a novice on them). Thanks for any help you can provide.

The first step is to use (you guessed it!) a union query to "fold" the
data. At the same time, calculating the monthname from the date:

SELECT Format([Date],"MMM") As Mth, 'LO1' as Function Code,
[LO1] as MonthTotal From queryname
UNION ALL
SELECT Format([Date],"MMM"), 'CSI' ,[CSI] as MonthTotal
From queryname
UNION ALL
etc.

Save that query and use it as the source of a crosstab query



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[KARL DEWEY]

Try this ---
SELECT Format([RPT_DT], "MMM") As Mth, "LO1" as [Function Code], [LO1] as
[Month Total]
FROM qry_EXA_Count2_SumTotals
UNION ALL SELECT Format([RPT_DT], "MMM") As Mth, "CSI" as [Function Code],
[CSI] as [Month Total]
FROM qry_EXA_Count2_SumTotals
UNION ALL SELECT Format([RPT_DT], "MMM") As Mth, "MSC" as [Function Code],
[MSC] as [Month Total]
FROM qry_EXA_Count2_SumTotals;
--
KARL DEWEY
Build a little - Test a little

Hi, Bob.

I've started the union query, but am getting an error about the SELECT
statement either uses a reserved word, an argument name mispelled, or
incorrect punctuation.

Here's what I've done so far:

SELECT Format([RPT_DT], "MMM") As Mth, 'LO1' as Function Code, [LO1] as
Month Total From qry_EXA_Count2_SumTotals
UNION ALL
SELECT Format([RPT_DT], "MMM") As Mth, 'CSI' as Function Code, [CSI] as
Month Total From qry_EXA_Count2_SumTotals
UNION ALL;
--
susan

I have two monthly reports that need be replicated for our customer
in the current (manual) format. For both reports, there is data in
separate columns that needs to be reported in rows, with totals
across the page for each month.

Here's an example of some raw data, after a query that sums numerous
monthly entries.
Date LO1 CSI MSC etc.
1/31/2008 2,544 2,346 2,136
2/28/2008 2,554 2,360 2,178
3/31/2008 2,576 2,382 2,206

Was this a crosstab query? If so, you may be starting at the wrong
point. What does the raw data actually look like?
If not, then you may have a database design issue.

Here's an example of the desired report output.
Function Code Jan Feb Mar etc.
LO1 2,544 2,554 2,576
CSI 2,346 2,360 2,382
MSC 2,136 2,178 2,206

I can't get a cross-tab to work, but there must be a way to transform
the data structure for a report? I've searched for a solution, but
have had no luck. Is a union query required here? (Yikes! -- am
pretty much a novice on them). Thanks for any help you can provide.

The first step is to use (you guessed it!) a union query to "fold" the
data. At the same time, calculating the monthname from the date:

SELECT Format([Date],"MMM") As Mth, 'LO1' as Function Code,
[LO1] as MonthTotal From queryname
UNION ALL
SELECT Format([Date],"MMM"), 'CSI' ,[CSI] as MonthTotal
From queryname
UNION ALL
etc.

Save that query and use it as the source of a crosstab query



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Susan L]

Karl:
That worked perfectly -- what a difference one word makes. I'll remember
that it's a series of Select statements.

Thank you so much. Thanks to the MVPs, I'll be able to make the deadline for
reporting the data for July.
--
susan

Try this ---
SELECT Format([RPT_DT], "MMM") As Mth, "LO1" as [Function Code], [LO1] as
[Month Total]
FROM qry_EXA_Count2_SumTotals
UNION ALL SELECT Format([RPT_DT], "MMM") As Mth, "CSI" as [Function Code],
[CSI] as [Month Total]
FROM qry_EXA_Count2_SumTotals
UNION ALL SELECT Format([RPT_DT], "MMM") As Mth, "MSC" as [Function Code],
[MSC] as [Month Total]
FROM qry_EXA_Count2_SumTotals;
--
KARL DEWEY
Build a little - Test a little

Hi, Bob.

I've started the union query, but am getting an error about the SELECT
statement either uses a reserved word, an argument name mispelled, or
incorrect punctuation.

Here's what I've done so far:

SELECT Format([RPT_DT], "MMM") As Mth, 'LO1' as Function Code, [LO1] as
Month Total From qry_EXA_Count2_SumTotals
UNION ALL
SELECT Format([RPT_DT], "MMM") As Mth, 'CSI' as Function Code, [CSI] as
Month Total From qry_EXA_Count2_SumTotals
UNION ALL;
--
susan

Susan L wrote:
I have two monthly reports that need be replicated for our customer
in the current (manual) format. For both reports, there is data in
separate columns that needs to be reported in rows, with totals
across the page for each month.

Here's an example of some raw data, after a query that sums numerous
monthly entries.
Date LO1 CSI MSC etc.
1/31/2008 2,544 2,346 2,136
2/28/2008 2,554 2,360 2,178
3/31/2008 2,576 2,382 2,206

Was this a crosstab query? If so, you may be starting at the wrong
point. What does the raw data actually look like?
If not, then you may have a database design issue.


Here's an example of the desired report output.
Function Code Jan Feb Mar etc.
LO1 2,544 2,554 2,576
CSI 2,346 2,360 2,382
MSC 2,136 2,178 2,206

I can't get a cross-tab to work, but there must be a way to transform
the data structure for a report? I've searched for a solution, but
have had no luck. Is a union query required here? (Yikes! -- am
pretty much a novice on them). Thanks for any help you can provide.


The first step is to use (you guessed it!) a union query to "fold" the
data. At the same time, calculating the monthname from the date:

SELECT Format([Date],"MMM") As Mth, 'LO1' as Function Code,
[LO1] as MonthTotal From queryname
UNION ALL
SELECT Format([Date],"MMM"), 'CSI' ,[CSI] as MonthTotal
From queryname
UNION ALL
etc.

Save that query and use it as the source of a crosstab query



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.