Cross Tab Query - Brain Cramp 1

[mkallover]

OK, I'm having a total brain cramp trying to create a crosstab query. It's been a while since I've done this.

I have a query (qryCust) that pulls the following fields from a large DB:

CUST(this is customer number and all are the same "0333"), [ERR 1], [ERR 2], [ERR 3] and then several fields with different costs in them.

ERR 1, ERR 2, ERR 3 will contain either an error code or will be null.

I'm trying to come up with a query that will look like:

     Count of Err(If not null) | SumCost1 | SumCost2 | SumCost3
ERR 1
ERR 2
ERR 3

I'm completely stuck. I know there should be a way to get this done but it's been so long I just can't get going. Any help would be greatly appreciated.

 

[Remou]

You will need first to create a Union Query, if I uderstand your table set up correctly. For example:

[tt]SELECT Err1 As ErrNo, Cost1, ..., Costn
UNION ALL
SELECT Err2 As ErrNo, Cost1, ..., Costn[/tt]

 

[mkallover]

There is only one table with the fields that I named.

 

[Remou]

There is only one table with the fields that I named.

Hence my suggestion of a Union query, otherwise, how would you show Err1 to 3 in one column? If you need to know which field contained the error number, you can say:

[tt]SELECT Err1 As ErrNo, "Err1" As ErrCount, Cost1, ..., Costn FROM tblT
UNION ALL
SELECT Err2 As ErrNo, "Err2" As ErrCount, Cost1, ..., Costn FROM tblT
UNION ALL
SELECT Err3 As ErrNo, "Err3" As ErrCount, Cost1, ..., Costn FROM tblT[/tt]

 

[mkallover]

Thanks man, I didn't quite grasp what you were saying at first but that is what I needed.