Field data in crosstab results

[OnLiner]

Hi all

I have a crosstab query with its source from another query.
The fields are..
date | violation | type | description

The crosstab ends up like this...
_______________Column1type1 | column2type2 | column3type3
Row1violation1 | 1
row2violation2 |
row3violation3 |

the crosstab design is to count the description. In other words, it shows how many violations of each type.
Instead of the count, is it possible to have the "date" field show up in the count area?

In other words, if there is a violation3 of type 2, can the date of that violation show there?

 

[hneal98]

What do your two queries look like?

 

[OnLiner]

this is the SQL version of the source version

SELECT tblDiscipline.description, tblDiscipline.violation, tblDiscipline.date, tblDiscipline.type, tblemployees.firstname, tblemployees.lastname, tblemployees.recordid
FROM tblemployees INNER JOIN tblDiscipline ON tblemployees.recordid = tblDiscipline.recordid
WITH OWNERACCESS OPTION;

and this is the sql version of the crosstab

TRANSFORM Count(qryDisTable.description) AS CountOfdescription
SELECT qryDisTable.violation
FROM qryDisTable
GROUP BY qryDisTable.violation
PIVOT qryDisTable.type In ("Counsel","Verbal","Written","Administrative Suspension","Suspension","Termination");

thanks!!

 

[hneal98]

In other words, if there is a violation3 of type 2, can the date of that violation show there?"

To do that, you would have to save your crosstab to a table and then do another query to update it based on your criteria.

 

[dhookom]

You can easily show a date as the "value" in a crosstab query.

TRANSFORM Max(qryDisTable.[Date]) AS TheValue
SELECT qryDisTable.violation
FROM qryDisTable
GROUP BY qryDisTable.violation
PIVOT qryDisTable.type In ("Counsel","Verbal","Written","Administrative Suspension","Suspension","Termination");

I'm not sure what you would expect to see if there is more than one date per Violation and Type.

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]

 

[OnLiner]

wow that did the trick! Thanks tenfold!!!