Crosstab Question 1

[krets]

I have a crosstab query that is set up to display the counts of errors made by each technician in the database.

Row header: Tech initials
Column header: Error code

There are 30 possible error codes that are stored in a table called "tblAIT" (we refer to errors as AITs).

Right now my query is set up as:

TRANSFORM Count(qryMgrAIT.Key) AS CountOfKey
SELECT qryMgrAIT.tblAITInfo.TechRxEInit, qryMgrAIT.CellNum, qryMgrAIT.Location, Count(qryMgrAIT.Key) AS [Total Of Key]
FROM qryMgrAIT
GROUP BY qryMgrAIT.tblAITInfo.TechRxEInit, qryMgrAIT.CellNum, qryMgrAIT.Location
PIVOT qryMgrAIT.ErrorCode;

This crosstab query is based on another query:

SELECT tblAITInfo.*, tblTechs.*
FROM tblAITInfo INNER JOIN tblTechs ON tblAITInfo.TechRxEInit=tblTechs.TechRxEInit;

The query displays information for any errors that the techs have already made and counts them. However, I would like to be able to see every error code in the query with a zero if the tech has never made that error.

Is there a way for me to do that?

 

[dhookom]

You need to modify your crosstab in two places. Change your Value and add all possible column headings into the Column Headings property:

TRANSFORM Val(Nz(Count(qryMgrAIT.Key),0)) AS CountOfKey
SELECT qryMgrAIT.tblAITInfo.TechRxEInit, qryMgrAIT.CellNum, qryMgrAIT.Location, Count(qryMgrAIT.Key) AS [Total Of Key]
FROM qryMgrAIT
GROUP BY qryMgrAIT.tblAITInfo.TechRxEInit, qryMgrAIT.CellNum, qryMgrAIT.Location
PIVOT qryMgrAIT.ErrorCode IN ("code1","Code2","Code3",..."Code30";

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[krets]

Awesome. That worked great.

So there's no way to dynamically get the list of error codes? I only ask that because in the future they might want to add new error codes and I was hoping to allow them to do most of the maintenance of the DB on their own through forms.

 

[PHV]

Perhaps this ?
TRANSFORM Count(qryMgrAIT.Key) AS CountOfKey
SELECT qryMgrAIT.tblAITInfo.TechRxEInit, qryMgrAIT.CellNum, qryMgrAIT.Location, Count(qryMgrAIT.Key) AS [Total Of Key]
FROM tblAIT LEFT JOIN qryMgrAIT ON tblAIT.ErrorCode = qryMgrAIT.ErrorCode
GROUP BY qryMgrAIT.tblAITInfo.TechRxEInit, qryMgrAIT.CellNum, qryMgrAIT.Location
PIVOT tblAIT.ErrorCode

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[krets]

Interesting.

That returns all the error codes as column headings like I want. However, it also adds a blank row to the top of the results with zeroes for every error code. All the other records have blanks when there is no error code instead of zeroes like your first suggestion had.

 

[PHV]

The blank row may be filtered in your form/report.
for the blanks instead of zeroes, replace this:
TRANSFORM Count(qryMgrAIT.Key) AS CountOfKey
with this:
TRANSFORM Val(Nz(Count(qryMgrAIT.Key),0)) AS CountOfKey

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886