Crosstab Query not pulling all the data

[Stoss]

As I was sitting here thinking about this issue. It seems to me what I really need is a union query. In other words, pull all the Categories from CarData.Category no matter what, and then pull the Categories from [Maint Trouble Calls]. Just not sure if this is even possible with a crosstab query.

Any advice on this idea?
-Stoss

 

[vbaInet]

We will keep going round in circles re this matter. Without seeing your db there isn't much more help I can offer.

Maybe the way forward would be to present your thought process on a spreadsheet, so show a few records from the current query and another set of records with your desired outcome. Put them in separate sections on the sheet with the right field names. Also indicate which records in the Categories table you would like to be included in the query.

 

[Stoss]

This is a sample spreadsheet of what I would like. I am hoping the visual representation will help. I have the Categories setup just fine but the rows ("Cars") are not. I would like (as seen below) for all the cars to show up all of the time. In this example, notice the last row where "Ford" does not have anything associated with it as far as category. However, under my current crosstab and using this example, "Ford" would not be listed at all.

"Car"	     Engine	Transmission    Body    Tires	  Electrical
Honda        4             0	        0	 0	      0
Toyota       0             4	        0	 0	      0
Chevy        0             0	        0	 3	      0
Ford         0             0 	        0	 0	      0

I have tried all of the code on the previous posts and nothing has worked.

Thanks,
Stoss

 

[Stoss]

I did it!!!!

Here was the trick to the whole thing. My problems had to do with how (and when) the where clause was executing.

I put the where clause directly into the join statement and it works (as far as I can tell).

Here is the new crosstab code (for those interested).

TRANSFORM CLng(Nz(Count(CarData.Car),0)) AS CountofID
SELECT CarData.Car, Count([Maint Trouble Calls].Category) AS CountOfCategory
FROM [Maint Trouble Calls] RIGHT JOIN CarData ON ([Maint Trouble Calls].Car = CarData.Car and (([Maint Trouble Calls].Date) Between Date()-30 And Date() and CarData.Active = True))
GROUP BY CarData.Car
PIVOT [Maint Trouble Calls].Category In ("Motor","Transmission","Body","Tires","Electrical");

-Stoss

 

[vbaInet]

Happy to hear!

 

[Stoss]

Me too!

Thanks for all your time and effort!!! I really appreciate it!

-Stoss

 

[vbaInet]

You're welcome!