non equivalent joins

[Jaybird]

I had never heard of these until recenly. Even now I can't find any
discussions about them. (I'm probably looking for the wrong topic...)
Anyhow, I'm looking for a way to quickly search through two separate tables
for matches or partial matches of a Tool Number. In other words, for each
text string I input, I want all of the matching records in either table.
That way, I can quickly see if a similar Tool Number has been inputted (sp?)
into either table. This way, I can avoid "double entries" with tool numbers
that are similar, but not the same. The problem has been that unless the
records ARE EXACTLY THE SAME, no matches will be found in my query. I can't
remember who posted, or exactly what the name is, but a "non-equivalent join"
seems to be what I'm looking for. Here's my version of the SQL which seems
to be getting me where I want to go...

SELECT DISTINCTROW[Order Entry].[Order Number], [Order Entry].[Part Number1]
as [Tool Number], [Contacts].CompanyName as [Owner], Tools.[Part Number],
Tools.Location, Tools.[In], Tools.Out, Tools.Notes
FROM (Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID) INNER JOIN Tools ON Left([Order Entry].[Tool #],10) Like
"*" & Tools.[Tool Number] & "*"
WHERE (([Order Entry].Shipped) is null) and (([Order Entry].[Tool #]) is not
null) and (([Order Entry].[Part Number1]) is not null)
UNION

SELECT DISTINCTROW[Order Entry].[Order Number], [Order Entry].[Part Number
2] as [Tool Number], [Contacts].CompanyName as [Owner], Tools.[Part Number],
Tools.Location, Tools.[In], Tools.Out, Tools.Notes
FROM (Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID) INNER JOIN Tools ON Left([Order Entry].[Tool #],10) Like
"*" & Tools.[Tool Number] & "*"
WHERE (([Order Entry].Shipped) is null) and (([Order Entry].[Tool #]) is not
null) and (([Order Entry].[Part Number 2]) is not null)
UNION

SELECT DISTINCTROW[Order Entry].[Order Number], [Order Entry].[Part Number
3] as [Tool Number], [Contacts].CompanyName as [Owner], Tools.[Part Number],
Tools.Location, Tools.[In], Tools.Out, Tools.Notes
FROM (Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID) INNER JOIN Tools ON Left([Order Entry].[Tool #],10) Like
"*" & Tools.[Tool Number] & "*"
WHERE (([Order Entry].Shipped) is null) and (([Order Entry].[Tool #]) is not
null) and (([Order Entry].[Part Number 3]) is not null)
UNION

SELECT DISTINCTROW[Order Entry].[Order Number], [Order Entry].[Part Number4]
as [Tool Number], [Contacts].CompanyName as [Owner], Tools.[Part Number],
Tools.Location, Tools.[In], Tools.Out, Tools.Notes
FROM (Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID) INNER JOIN Tools ON Left([Order Entry].[Tool #],10) Like
"*" & Tools.[Tool Number] & "*"
WHERE (([Order Entry].Shipped) is null) and (([Order Entry].[Tool #]) is not
null) and (([Order Entry].[Part Number4]) is not null)
UNION

SELECT DISTINCTROW[Order Entry].[Order Number], [Order Entry].[Tool #] as
[Tool Number], [Contacts].CompanyName as [Owner], Tools.[Part Number],
Tools.Location, Tools.[In], Tools.Out, Tools.Notes
FROM (Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID) INNER JOIN Tools ON Left([Order Entry].[Tool #],10) Like
"*" & Tools.[Tool Number] & "*"
WHERE [Order Entry].Shipped is null
UNION SELECT DISTINCTROW "No Order Number" as [Order Number], Tools.[Tool
Number] as [Tool Number], Contacts.CompanyName as [Owner], Tools.[Part
Number], Tools.Location, Tools.[In], Tools.Out, Tools.Notes
FROM (Contacts INNER JOIN Tools ON Contacts.ContactID = Tools.Owner)
ORDER BY [Tool Number];

This is a UNION query because the Oder Entry table is not normalized. I
need to be able to view all matches from the [Part Number1], [Part Number 2],
[Part Number 3], [Part Number4], and [Tool#] fields from the Order Entry
table, and the [Tool Number] field from the Tools table and compare them to a
search string that I input.

Can anybody tell me how I could incorporate this query into a form so that,
once I input the search string, I get all of the matching records and in what
table they are located? This is to avoid double entries in the Tools table
for tools we may have already inputted with a slightly different spelling,
and to double check that such a tool (or the part number it is designed to be
used with) does indeed exist in the Order Entry table.

I hope no one will find it necessary to ask me WHY the Order Entry table is
designed the way it is... (it wasn't me!)