This is a crosstab query in Access, hopefully this is the right section of the forums to place this....

I am getting the right results as far as what the boss wants to see, but
he just asked to see all the Equipment Names for the row headers, not just the ones that have results for the time period which is what I have now.

That way he can see which equipment isnt having any operations, as well as the equipment that is.
Any thoughts how to accomplish this?


PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Count([Operation Entry].[Operation Status ID No]) AS [CountOfOperation Status ID No1]
SELECT [Equipment Type].[Equipment Name]
FROM ([Operation Entry] INNER JOIN [Operation/Failure] ON [Operation Entry].[Operation Status ID No] = [Operation/Failure].[Operation Status ID No]) INNER JOIN [Equipment Type] ON [Operation Entry].[Equipment ID No] = [Equipment Type].[Equipment ID No]
WHERE ((([Operation Entry].[Operation Status ID No])=1 Or ([Operation Entry].[Operation Status ID No])=2) AND (([Operation Entry].[Class ID No])="4") AND (([Operation Entry].RecDate) Between [Start Date] And [End Date]))
GROUP BY [Equipment Type].[Equipment Name]
ORDER BY "# " & [Operation Status Name] DESC
PIVOT "# " & [Operation Status Name];

I ended up making a query of the crosstab query and included the equipment table. Right join on the equiptable from the crosstab query.
It gave me two rows of equipment, but one of them was correct because it showed all equipment values.
So when I made the report I just didnt include the crosstab field from the original query.
It's a hack way of doing it but it works ...

Isn't the universe actually a collection of hacks, bound together with kludges?

-PatP