SQL Query Problem

[CodeConfused]

Hi:

I have a joined query from two tables. I want ro preserve ALL the data from the RIGHT table while summing the data from a field in the LEFT table having a specific criteria from a third field. Here's the code:

SELECT ActionType.[Action Type], Sum([6 Export OwnOcc Loan Activity].[Action Type]) AS [SumOfAction Type]
FROM [6 Export OwnOcc Loan Activity] 
RIGHT JOIN ActionType 
ON [6 Export OwnOcc Loan Activity].[Action Type] = ActionType.[Action Type]
GROUP BY ActionType.[Action Type], [6 Export OwnOcc Loan Activity].Occupancy
HAVING ((([6 Export OwnOcc Loan Activity].Occupancy)="1"));

The query runs fine (showing all data from the "ActionType " table) when I remove the "HAVING ((([6 Export OwnOcc Loan Activity].Occupancy)="1" ))" criteia. It does not show all the data from the "ActionType " table with that criteria in place.

Can someone tell me how to fix it to show ALL data from the "ActionType " table?

[Rabbit]

Please use code tags when posting code.

That's because there's no record in the left table. So the occupancy field isn't going to have any data. So when you say it has to have "1" in there, it's not going to return since it's not "1". You'll want to return the "1"s or where it's null.

[CodeConfused]

Rabbit:

Thanks. I think that makes sense. The key issue here is that we need the occupancy to only be the "1" values. I was thinking about that after I posted and thought it might work to do a pre-query filtering out the "1s" then do the joined query without the criteria.

I bet that would work...

[Rabbit]

Returning ones and nulls is no different than what you're trying to accomplish with a second query.