Searching two tables that may contain the requested "Order Number"

[Richie Piper]

I Have two table that store despatch information - MainTable and Suv_Ivs (the
company I work for has a policy of linking all orders for one customer being
despatched in one pallet/box to a 'Master' Order Number for trackablility).
I have't tried writing an acces query yet, only using the wizards as i used
to writing 32bit applications around a dtabase file).
The Wizard had#s generated the following SQL statement:

SELECT MainTable.IV_Index, MainTable.IV_Number, Sub_Ivs.Sub_IVNo,
Freight.MainIndex, Freight.Package_ID
FROM (MainTable INNER JOIN Freight ON MainTable.IV_Index =
Freight.MainIndex) INNER JOIN Sub_Ivs ON MainTable.IV_Index =
Sub_Ivs.MasterIV_Index
WHERE (((MainTable.IV_Number)=[IV Number])) OR (((Sub_Ivs.Sub_IVNo)=[IV
Number]));

This query rarely returns information that i am looking for - or it will
return the information in the sub table and it related link in the MainTable,
but will not return a dataset when the only matching record is in main table
alone.

I'm a ralative novice with this syle of development, is there any way in
which i can retrive the querie results even if there isn't a related link in
the Sub_IVs table

 

[Allen Browne]

Double-click the line joining one table to the other in the upper pane of
query design view.

Access gives you a dialog with 3 options. Choose the one that gives you:
All records from MainTable, and any matches from ...

You will also need to modify the criteria under your sub table to:
Is Null Or =[IV Number]

This is known as an outer join. More info:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html