Crosstab subcolumn query/report heading pblm

J

Jen

I used the suggestion from the following google groups:
"multi value crosstab cartesian group:*access* author:hookom"
which used the following code in the Northwinds db to produce month/data
columns:

TRANSFORM Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]!UnitPrice)) AS DaVal
SELECT Products.ProductName
FROM tblXtabColumns, Orders INNER JOIN (Products INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Products.ProductName
PIVOT [FldName] & Month([OrderDate]);

On my data, I used the following code:

TRANSFORM (Nz(Sum(tblClaims_Final.Count),0)) AS DaVal
SELECT tblClaims_Final.LOC, tblClaims_Final.RvwRsn
FROM tblClaims_Final, tblReports
GROUP BY tblClaims_Final.LOC, tblClaims_Final.RvwRsn
PIVOT [ReportType] & Month([Date]);

Which is producing the 2 report (C170, RC85) breakouts with subsequent month
#'s, but it is putting the same data under each report. So the data is the
same under each report:
C1701 same as RC851, C1702 same as RC852 and so on when the totals should be
different.

Any suggestions?

Thanks.
Jen
 
G

Gary Walter

"Jen"wrote:
I used the suggestion from the following google groups:
"multi value crosstab cartesian group:*access* author:hookom"
which used the following code in the Northwinds db to produce month/data
columns:

TRANSFORM Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]!UnitPrice)) AS DaVal
SELECT Products.ProductName
FROM tblXtabColumns, Orders INNER JOIN (Products INNER JOIN [Order
Details]
ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID =
[Order
Details].OrderID
GROUP BY Products.ProductName
PIVOT [FldName] & Month([OrderDate]);

On my data, I used the following code:

TRANSFORM (Nz(Sum(tblClaims_Final.Count),0)) AS DaVal
SELECT tblClaims_Final.LOC, tblClaims_Final.RvwRsn
FROM tblClaims_Final, tblReports
GROUP BY tblClaims_Final.LOC, tblClaims_Final.RvwRsn
PIVOT [ReportType] & Month([Date]);

Which is producing the 2 report (C170, RC85) breakouts with subsequent
month
#'s, but it is putting the same data under each report. So the data is
the
same under each report:
C1701 same as RC851, C1702 same as RC852 and so on when the totals should
be
different.
Click to expand...
Hi Jen,

What field in tblClaims_Final determines whether you
have a "C170" or "RC85" count?

Suggest you provide example data from tblClaims_Final
(also showing this mystery field) and what you expect as
a result.

Your TRANSFORM will have to "jog" back and forth
depending on tblReports.ReportType and this mystery
field....

ignoring NZ for now, it might be something like

TRANSFORM
SUM(IIF(tblReports.ReportType="C170" AND
[mysteryfield]="mysteryvalueforC170",
tblClaims_Final.Count,
IIF(tblReports.ReportType="RC85" AND
[mysteryfield]="mysteryvalueforRC85",
tblClaims_Final.Count,
0))
)

above assumes that tblReports.ReportType has 2 values
"C170"
"RC85"

Do you see how this works?

You started with a group by query

SELECT tblClaims_Final.LOC, tblClaims_Final.RvwRsn
FROM tblClaims_Final
GROUP BY tblClaims_Final.LOC, tblClaims_Final.RvwRsn

look at the results...

You "Cartesian Joined" tblReports so each record above will
occur "internally" twice, once with with a "C170" and once w/ "RC85"

to see this "internal" grouping...

SELECT
tblReports.ReportType,
tblClaims_Final.LOC,
tblClaims_Final.RvwRsn
FROM tblClaims_Final, tblReports
GROUP BY
tblReports.ReportType,
tblClaims_Final.LOC,
tblClaims_Final.RvwRsn

For every group in this "internal grouping",
you want to add additional columns using
a crosstab.

Your TRANSFORM wants to sum the count for
"C170" records within each "internal group"
and sum the count for "RC85" records within
each "internal group."

Whether you are interested in count sum for
"C170" or "RC85" is determined by tblReports.ReportType.
Along with the month of the date, this is determined
by the PIVOT clause.

Whether a record within the "internal group" provides
a count to sum for "C170" or RC85" must be determined
by some "mystery field."

Does that help?

good luck,

gary
 
J

Jen

THANKS GARY!!!!
I used the following code you provided with my "mystery field":

TRANSFORM
SUM(IIF(tblReports.ReportType="C170" AND
tblClaims_Final.Report="C170",tblClaims_Final.Count,
IIF(tblReports.ReportType="RC85" AND
tblClaims_Final.Report="RC85",
tblClaims_Final.Count, 0)))

The data seems to be right now, but I need to do some verification.

Appreciate the help! I stared at this too long........

Jen


Gary Walter said:
"Jen"wrote:
I used the suggestion from the following google groups:
"multi value crosstab cartesian group:*access* author:hookom"
which used the following code in the Northwinds db to produce month/data
columns:

TRANSFORM Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]!UnitPrice)) AS DaVal
SELECT Products.ProductName
FROM tblXtabColumns, Orders INNER JOIN (Products INNER JOIN [Order
Details]
ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID =
[Order
Details].OrderID
GROUP BY Products.ProductName
PIVOT [FldName] & Month([OrderDate]);

On my data, I used the following code:

TRANSFORM (Nz(Sum(tblClaims_Final.Count),0)) AS DaVal
SELECT tblClaims_Final.LOC, tblClaims_Final.RvwRsn
FROM tblClaims_Final, tblReports
GROUP BY tblClaims_Final.LOC, tblClaims_Final.RvwRsn
PIVOT [ReportType] & Month([Date]);

Which is producing the 2 report (C170, RC85) breakouts with subsequent
month
#'s, but it is putting the same data under each report. So the data is
the
same under each report:
C1701 same as RC851, C1702 same as RC852 and so on when the totals should
be
different.
Click to expand...
Hi Jen,

What field in tblClaims_Final determines whether you
have a "C170" or "RC85" count?

Suggest you provide example data from tblClaims_Final
(also showing this mystery field) and what you expect as
a result.

Your TRANSFORM will have to "jog" back and forth
depending on tblReports.ReportType and this mystery
field....

ignoring NZ for now, it might be something like

TRANSFORM
SUM(IIF(tblReports.ReportType="C170" AND
[mysteryfield]="mysteryvalueforC170",
tblClaims_Final.Count,
IIF(tblReports.ReportType="RC85" AND
[mysteryfield]="mysteryvalueforRC85",
tblClaims_Final.Count,
0))
)

above assumes that tblReports.ReportType has 2 values
"C170"
"RC85"

Do you see how this works?

You started with a group by query

SELECT tblClaims_Final.LOC, tblClaims_Final.RvwRsn
FROM tblClaims_Final
GROUP BY tblClaims_Final.LOC, tblClaims_Final.RvwRsn

look at the results...

You "Cartesian Joined" tblReports so each record above will
occur "internally" twice, once with with a "C170" and once w/ "RC85"

to see this "internal" grouping...

SELECT
tblReports.ReportType,
tblClaims_Final.LOC,
tblClaims_Final.RvwRsn
FROM tblClaims_Final, tblReports
GROUP BY
tblReports.ReportType,
tblClaims_Final.LOC,
tblClaims_Final.RvwRsn

For every group in this "internal grouping",
you want to add additional columns using
a crosstab.

Your TRANSFORM wants to sum the count for
"C170" records within each "internal group"
and sum the count for "RC85" records within
each "internal group."

Whether you are interested in count sum for
"C170" or "RC85" is determined by tblReports.ReportType.
Along with the month of the date, this is determined
by the PIVOT clause.

Whether a record within the "internal group" provides
a count to sum for "C170" or RC85" must be determined
by some "mystery field."

Does that help?

good luck,

gary
Click to expand...
 
G

Gary Walter

Hi Jen,

I wonder if you really needed tblReports after all?

Maybe I am missing something, but how are
your results different from results of following query?

TRANSFORM (Nz(Sum(T.Count),0))
SELECT
T.LOC,
T.RvwRsn
FROM tblClaims_Final AS T
WHERE
T.[Report] IN ('C170', 'RC85')
GROUP BY
T.LOC,
T.RvwRsn
PIVOT
T.[Report] & Month(T.[Date]);

Perhaps you get further "extended groups"
in your query that had no C170/RC85
(which would be lost by above WHERE)?

good luck,

gary

Jen said:
I used the following code you provided with my "mystery field":

TRANSFORM
SUM(IIF(tblReports.ReportType="C170" AND
tblClaims_Final.Report="C170",tblClaims_Final.Count,
IIF(tblReports.ReportType="RC85" AND
tblClaims_Final.Report="RC85",
tblClaims_Final.Count, 0)))

The data seems to be right now, but I need to do some verification.

Appreciate the help! I stared at this too long........

Jen


Gary Walter said:
"Jen"wrote:
I used the suggestion from the following google groups:
"multi value crosstab cartesian group:*access* author:hookom"
which used the following code in the Northwinds db to produce
month/data
columns:

TRANSFORM Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]!UnitPrice)) AS DaVal
SELECT Products.ProductName
FROM tblXtabColumns, Orders INNER JOIN (Products INNER JOIN [Order
Details]
ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID =
[Order
Details].OrderID
GROUP BY Products.ProductName
PIVOT [FldName] & Month([OrderDate]);

On my data, I used the following code:

TRANSFORM (Nz(Sum(tblClaims_Final.Count),0)) AS DaVal
SELECT tblClaims_Final.LOC, tblClaims_Final.RvwRsn
FROM tblClaims_Final, tblReports
GROUP BY tblClaims_Final.LOC, tblClaims_Final.RvwRsn
PIVOT [ReportType] & Month([Date]);

Which is producing the 2 report (C170, RC85) breakouts with subsequent
month
#'s, but it is putting the same data under each report. So the data is
the
same under each report:
C1701 same as RC851, C1702 same as RC852 and so on when the totals
should
be
different.
Click to expand...
Hi Jen,

What field in tblClaims_Final determines whether you
have a "C170" or "RC85" count?

Suggest you provide example data from tblClaims_Final
(also showing this mystery field) and what you expect as
a result.

Your TRANSFORM will have to "jog" back and forth
depending on tblReports.ReportType and this mystery
field....

ignoring NZ for now, it might be something like

TRANSFORM
SUM(IIF(tblReports.ReportType="C170" AND
[mysteryfield]="mysteryvalueforC170",
tblClaims_Final.Count,
IIF(tblReports.ReportType="RC85" AND
[mysteryfield]="mysteryvalueforRC85",
tblClaims_Final.Count,
0))
)

above assumes that tblReports.ReportType has 2 values
"C170"
"RC85"

Do you see how this works?

You started with a group by query

SELECT tblClaims_Final.LOC, tblClaims_Final.RvwRsn
FROM tblClaims_Final
GROUP BY tblClaims_Final.LOC, tblClaims_Final.RvwRsn

look at the results...

You "Cartesian Joined" tblReports so each record above will
occur "internally" twice, once with with a "C170" and once w/ "RC85"

to see this "internal" grouping...

SELECT
tblReports.ReportType,
tblClaims_Final.LOC,
tblClaims_Final.RvwRsn
FROM tblClaims_Final, tblReports
GROUP BY
tblReports.ReportType,
tblClaims_Final.LOC,
tblClaims_Final.RvwRsn

For every group in this "internal grouping",
you want to add additional columns using
a crosstab.

Your TRANSFORM wants to sum the count for
"C170" records within each "internal group"
and sum the count for "RC85" records within
each "internal group."

Whether you are interested in count sum for
"C170" or "RC85" is determined by tblReports.ReportType.
Along with the month of the date, this is determined
by the PIVOT clause.

Whether a record within the "internal group" provides
a count to sum for "C170" or RC85" must be determined
by some "mystery field."

Does that help?

good luck,

gary
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

2 crosstabs 1
Strange Behaviour in Crosstab 4
Report based on two similar queries 1
Crosstab query showing months 2
Sort months by calendar order from a crosstab query 5
List Box to create a Report based on crosstab query 6
Need to create a report using a crosstab query 2
Crosstab query and report 14

Top