Developer Forum

Help- I'm inexperienced how to write SQL. I have the following query that
links multiple tables and outputs data:

SELECT Locs.Vendor, Categories.Category, [Week 1].Loc, [Week 1].Dept, [Week
1].Class, [Week 1].Item, [Week 1].Description, [Week 1].[Old Price], [Week
1].Units, [Week 1].Net, Rates.[Hudson's], [Week 1]!Net*Rates![Hudson's] AS
[Billing Amount], [Week 1].[Price Change Type]
FROM (Categories INNER JOIN Rates ON Categories.Category = Rates.[Category
Name]) INNER JOIN (Locs INNER JOIN [Week 1] ON Locs.Loc = [Week 1].Loc) ON
Categories.Dept = [Week 1].Dept
WHERE (((Locs.Vendor)="Hudson's") AND (([Week 1].[Price Change
Type])="Manual" Or ([Week 1].[Price Change Type])="DC Smarts"))
ORDER BY [Week 1].Loc;

From the output from this query, I want to perform a left outer join to
match records on another table (if they exist), yet keep the records from the
original output if there is no match found.

So the output from my original query should equal the number of records
after checking it against my last table. How do I modify the above formula
to take the final output then apply the additional table? Or is there a more
efficient recommendation to perform such a task?

Thank you!!!

Save the first query (name it qryOne, for example). Create a new query (name
it qryTwo, for example) that uses the Nz function to substitute the value
from qryOne when no value is found in the NewTable:

SELECT Nz(NewTable.FieldName1, qryOne.FieldName1) AS FieldName1,
Nz(NewTable.FieldName2, qryOne.FieldName2) AS FieldName2
FROM qryOne LEFT JOIN NewTable
ON qryOne.FirstLinkingField = NewTable.FirstLinkingField AND
qryOne.SecondLinkingField = NewTable.SecondLinkingField;

--

Ken Snell
<MS ACCESS MVP>
http://www.yqcomputer.com/