Please Help on Crosstab Question!

J

JOM

I want to calculate the service level in my report, the information in my
reports comes from a crostab query. The service level is calculated for each
document as follows: ontime/total submitted% but am not sure how to do this
in my report!

e.g.,
W2 1040 1099
OnTime: 35 43 10
OutStd : 22 58 8
Total Produced: 77 101 18 (=OnTime+outstd)
service level: 45.45% 42.57% 55.56% (ontime/total
produced)%




I have the following infor in my crosstab query:
TRANSFORM Count(nz([TaxDocName])) AS TaxDocNames
SELECT
IIf([tblTaxDoc]![TaxDocStatus]="Complete",(IIf((([tblTaxDoc]![TaxDocName]="W2"
Or [tblTaxDoc]![TaxDocName]="1099") And
DateDiff("d",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd)<=10) Or
([tblTaxDoc]![TaxDocName]="1040" And
DateDiff("d",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd)<=2),"OnTime","OutStd"))) AS CompOnTime
FROM tblBorrower INNER JOIN tblTaxDoc ON (tblBorrower.BorID =
tblTaxDoc.BorID) AND (tblBorrower.BorID = tblTaxDoc.BorID)
WHERE (((tblTaxDoc.TaxDOcStatus)="Complete") AND ((tblTaxDoc.TxDocDateRcvd)
Between #12/1/2000# And #12/30/2005#))
GROUP BY
IIf([tblTaxDoc]![TaxDocStatus]="Complete",(IIf((([tblTaxDoc]![TaxDocName]="W2"
Or [tblTaxDoc]![TaxDocName]="1099") And
DateDiff("d",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd)<=10) Or
([tblTaxDoc]![TaxDocName]="1040" And
DateDiff("d",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd])<=2),"OnTime","OutStd"))), tblTaxDoc.TaxDOcStatus
PIVOT tblTaxDoc.TaxDocName In (W2, 1040, 1099);
 

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

Top