Report based on Crosstab with not all 'values' included 1

[geaker]

I have a cross tab query to change the class to 1, 2, 3, 4, or 5 based on the age of the invoice. It is an aging report if you know what I mean.

SQL for Crosstab:
TRANSFORM Sum([InvoiceAmount]-[SumOfInvoiceAmount]) AS ClassInvoiceA
SELECT AllExceptPD.ReportDate, AllExceptPD.ServiceCenter, AllExceptPD.BillingDate, AllExceptPD.InvoiceNumber, AllExceptPD.InvoiceAmount
FROM AllExceptPD
GROUP BY AllExceptPD.ReportDate, AllExceptPD.ServiceCenter, AllExceptPD.BillingDate, AllExceptPD.InvoiceNumber, AllExceptPD.InvoiceAmount
ORDER BY AllExceptPD.ServiceCenter, AllExceptPD.BillingDate
PIVOT AllExceptPD.Class;

I have several queries that change the field 'Class' to 1,2,3,4, or 5. 1 being less than 30 days old, 2 being 30-60 days old, 3 being 60-90 days old and so on. The report is based on the crosstab query with class being the 'value'.

The problem I am having is that I sum the amounts for the invoices to show the total for each value of 'Class' however there may or may not be all 5 values for 'Class'. The sum statement in the report is '=Sum([1])' or 2 or 3... which are the values in the 'Class' field. The report is giving me an error if there is not a valus of 'Class' for 1,2,3,4 or 5. The error I am getting is 'The MS Jet Engine does not recognize "[1]' as a valid field name or expression". Is there a way around this so the report will still print if 'Class' on contains records for say 1,2, and 3 instead of records for all possibilities of 'Class'? I hope I am making sense. The report is in landscape and has fields for all possible vlaues of 'Class' which are 1, 2,3, 4, or 5.

Thanks,
Gerald

 

[CosmoKramer]

Gerald,

Give this a shot and let me know if it works:

TRANSFORM Sum([InvoiceAmount]-[SumOfInvoiceAmount]) AS ClassInvoiceA
SELECT AllExceptPD.ReportDate, AllExceptPD.ServiceCenter, AllExceptPD.BillingDate, AllExceptPD.InvoiceNumber, AllExceptPD.InvoiceAmount
FROM AllExceptPD
GROUP BY AllExceptPD.ReportDate, AllExceptPD.ServiceCenter, AllExceptPD.BillingDate, AllExceptPD.InvoiceNumber, AllExceptPD.InvoiceAmount
ORDER BY AllExceptPD.ServiceCenter, AllExceptPD.BillingDate
PIVOT AllExceptPD.Class

In (1,2,3,4,5);

 

[geaker]

Worked like a champ.

Thanks

 

[geaker]

Cosmo,
As I said that worked great for the sums. Now the percent calculation don't work. Is it because there isn't a value in the 1, 2, 3, 4, or 5? This is the caculation I use:
=Sum[1]/(Sum[1] + Sum[2] + Sum[3] + Sum[4] + Sum[5])

Thanks

 

[CosmoKramer]

Geaker,

Check out Access help for the Nz function. Something like this:

=(Nz(Sum[1],0))/(Nz(Sum[1],0) + Nz(Sum[2],0) + Nz(Sum[3],0) + Nz(Sum[4],0) + Nz(Sum[5],0))

 

[geaker]

Cosmo,
Thanks once again. It worked fine.

Gerald