Query Help - if join critera is not met try another

[millhouse123]

I have a very simple query that joins two table and returns all records that are NULL in table 2

SELECT Automated.Company, Automated.Account, Automated.ProfitCenter, Automated.Amount, [TrialBalance].Amount, UserName
FROM (Automated 
LEFT JOIN Conversion ON (Automated.Account=Conversion.[Mil Account]) AND (Automated.Company=Conversion.[Mil Company]) AND (Automated.ProfitCenter=Conversion.[MIL Profit Center])) 
WHERE Conversion.[SAP Account] IS Null AND Conversion.[SAP Profit Center] is NULL AND Conversion.[SAP Company] is NULL;

Table 1 = Automated
Table 2 = Conversion

The conversion is joined on Company, Account and Profit Center

What I would like to do is take the records that the query above returns and replace automated.profitcenter with 000000. So I would like the query then to return not only those that now join but those that still do not join. See example below

 

[Micron]

What I would like to do is take the records that the query above returns and replace automated.profitcenter with 000000

Easiest way you colud accomplish this would be to create an update query and use the first query as the source. You will have to format the table field to hold all those zero placeholders.

So I would like the query then to return not only those that now join but those that still do not join.

This I don't understand. Joins do influence what records will be returned when dealing with more than one table, but you can return records for fields that are not joined as well. It is the criteria on the query fields that controls this.

You probably noticed that your example was not included in your post.