Crosstab Query not pulling all the data

[Stoss]

I have a crosstab query and I noticed that 1 "Car" item was not being pulled in. I know that this item does not have any of the required items (i.e. the categories are null) but I still want it in the list. I put a left join in thinking that it would select all Cars from the table. I must be missing something easy here.

TRANSFORM Count([Maint Trouble Calls].ID) AS CountOfID
SELECT [Maint Trouble Calls].Car, Count([Maint Trouble Calls].ID) AS [Total By Car]
FROM ([Maint Trouble Calls] LEFT JOIN CarData ON [Maint Trouble Calls].Car = CarData.Car) INNER JOIN Categories ON [Maint Trouble Calls].Category = Categories.Category
WHERE ((([Maint Trouble Calls].Category) Is Not Null) AND ((CarData.Active)=True) AND (([Maint Trouble Calls].Date) Between Date()-90 And Date()))
GROUP BY [Maint Trouble Calls].Car
ORDER BY [Maint Trouble Calls].Car
PIVOT [Maint Trouble Calls].Category;

Thanks,
Stoss

 

[Stoss]

After looking at the select statement, I can see why I am not getting the correct data. I can see that I am only selecting the "Cars" from within the trouble calls. How do I get all the Cars listed and still maintain this query (query works great besides this detail).

-Stoss

 

[vbaInet]

You have a LEFT JOIN on Trouble Cars. Change it to INNER JOIN or RIGHT JOIN.

TRANSFORM Count([Maint Trouble Calls].ID) AS CountOfID
SELECT [Maint Trouble Calls].Car, Count([Maint Trouble Calls].ID) AS [Total By Car]
FROM ([Maint Trouble Calls] [COLOR=Red][B]LEFT JOIN[/B] [/COLOR]CarData ON [Maint Trouble Calls].Car = CarData.Car) INNER JOIN Categories ON [Maint Trouble Calls].Category = Categories.Category
WHERE ((([Maint Trouble Calls].Category) Is Not Null) AND ((CarData.Active)=True) AND (([Maint Trouble Calls].Date) Between Date()-90 And Date()))
GROUP BY [Maint Trouble Calls].Car
ORDER BY [Maint Trouble Calls].Car
PIVOT [Maint Trouble Calls].Category;

Then you need another criteria of OR [Maint Trouble Calls].ID Is Null, like:

WHERE ((([Maint Trouble Calls].Category Is Not Null) AND (CarData.Active = True) AND ([Maint Trouble Calls].Date Between Date()-90 And Date())) [B][COLOR=Red]OR [Maint Trouble Calls].ID Is Null[/COLOR][/B])

 

[Stoss]

I apologize, I think I may have misled you with the Null thing...forget that part for now and I will try and explain again. I have re-written this 10 times (err, 11 now) because I don't know how to explain....sorry.

Try again,
What I really am trying to do is have the Select statement select all Cars from CarData (as long as they are "Active" -- where statement) and if they meet the criteria, populate the crossfields (cars and category with the ID as my Counting property).

I have no idea if that makes sense. Let me know if it doesn't. I will try again.
-Stoss

 

[vbaInet]

I apologize, I think I may have misled you with the Null thing...

No you didn't.

Is CarData.Car the ID of the Car? Is [Maint Trouble Calls].Car the foreign key field of the Car ID from CarData?

 

[Stoss]

Unfortunately, I inherited this DB so there are no relationships set up in this whole DB...

[Maint Trouble Calls].Car has the same data as CarData.Car so I used an inner join to link those fields in the query.

I think this may clear it up. I want all of the Cars listed down as the rows and all the Categories listed across the top (columns) whether they have data at the intersecting points or not. If they don't, then the id ("Total By Car") would just be 0 for that Car. Right now, all the cars and/or categories that are listed have a least 1 intersecting data (which is why it shows up).

The reason that this is important to me is I am taking this data and putting it into a spreadsheet (automated process) and it needs to have the rows and columns the same every time.

-Stoss

 

[vbaInet]

I'm not confused by what you're trying to do, all I want to know is whether CarData.Car is the ID of the Car or the name of the Car?

 

[Stoss]

Sorry, it is the name of the car

 

[vbaInet]

I see. This will cause problems. Is there no CarID field in the [Maint Trouble Calls] table?

 

[Stoss]

There is a main "ID" (auto generated) field in [Maint Trouble Calls] but [Maint Trouble Calls].Car is a text field. Also, there is no main ID field in CarData and CarData.Car is also a text field. These 2 .Car fields equal each other (text that is).

Thanks,
Stoss

 

[vbaInet]

I'm guessing this is the same issue you have with Category and [Maint Trouble Calls]?

Finally, is the Car ID field in CarData called CarID?

 

[Stoss]

Yes, same issue with Category.

No, just Car

-Stoss

 

[vbaInet]

Have a try:

TRANSFORM Count(CarData.Car) AS CountOfID
SELECT CarData.Car, Count(CarData.Car) AS [Total By Car]
FROM ([Maint Trouble Calls] RIGHT JOIN CarData ON [Maint Trouble Calls].Car = CarData.Car) LEFT JOIN Categories ON [Maint Trouble Calls].Category = Categories.Category
WHERE ((CarData.Active = True) AND (([Maint Trouble Calls].ID Is Null) OR (([Maint Trouble Calls].Category Is Not Null) AND ([Maint Trouble Calls].Date Between Date()-90 And Date()))))
GROUP BY CarData.Car
ORDER BY CarData.Car
PIVOT [Maint Trouble Calls].Category;

 

[Stoss]

That didn't seem to work. I mean, it works but it still does not pull all Cars and all Categories.

On one of the Cars it has a category of <>

I noticed that in the Where statement you have [Maint Trouble Calls].ID is Null. This field is never going to be Null because it is an auto generated ID field.

-Stoss

 

[vbaInet]

That didn't seem to work. I mean, it works but it still does not pull all Cars and all Categories.

It should pull in all Cars but it doesn't make sense for it to pull all Categories. Your criteria is restricting the full records from being shown so maybe you should start off with taken off the Category Is Null and Between criteria.

On one of the Cars it has a category of <>

What do you mean?

I noticed that in the Where statement you have [Maint Trouble Calls].ID is Null. This field is never going to be Null because it is an auto generated ID field.

It will be Null because there's no related data in [Maint Trouble Calls] from CarData. Notice the fields are now being pulled from CarData and not from [Maint Trouble Calls].

 

[Stoss]

In the [Maint Trouble Calls].Category, there are a lot more categories than I want to be viewed. So, I want all CarData.Category to show up and only the [Maint Trouble Calls].Category that equal it.

On one of the cars, it was showing up with <> in the column header (I called it category before on mistake).

I am at a loss, I tried all kinds of different options. No idea what to do.

-Stoss

 

[Stoss]

I did a lot of research looking for solutions to this and it appears that what I am trying to do is not even possible. I have all the categories in place by using the IN clause but for the rows, I think that is am out of luck.

If you have any ideas I would greatly appreciate it but at this point, I just don't think it can be done.

Thanks,
-Stoss

 

[vbaInet]

In the [Maint Trouble Calls].Category, there are a lot more categories than I want to be viewed. So, I want all CarData.Category to show up and only the [Maint Trouble Calls].Category that equal it.

That equal to what precisely? True?

Let me see a stripped down version of your db.

 

[Stoss]

At this point, I am so confused. I really am not sure what is going on anymore. I think I am just in a rut with my thought process. What I want to see happen is more of a problem with how I am pulling the data and what I expect to see, I think.

Thanks for tying to help but I think this is a lost cause for now. I may try and revisit it later.

I can't really give you a stripped down DB right now.

Thanks again for trying.
-Stoss

 

[Stoss]

I was able to use the "IN" statement to get all the columns but.....

Wish there was an IN statement for Rows as well!!!!

-Stoss