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