Please Look at this query

[Jaybird]

Here's my query:

SELECT DISTINCT Tools.[Tool Number] as [Tools/Tool Number], [Order
Entry].[Tool #] as [Order Entry/Tool #], [Order Entry].[Part Number1], [Order
Entry].[Part Number 2], [Order Entry].[Part Number 3], [Order Entry].[Part
Number4]

FROM [Order Entry] INNER JOIN Tools ON ([Order Entry].[Part
Number1]=Tools.[Tool Number]) Or ([Order Entry].[Part Number 2]=Tools.[Tool
Number]) Or ([Order Entry].[Part Number 3]=Tools.[Tool Number]) Or ([Order
Entry].[Part Number4]=Tools.[Tool Number]) Or ([Order Entry].[Tool
#]=Tools.[Tool Number])

WHERE (((Tools.[Tool Number]) Like "*" & [Search Criteria] & "*"))
;

What I think this is doing is looking through the Tools table under Tool
Number, and through the Order Entry table under Tool #, Part Number1, Part
Number 2, Part Number 3, Part Number4 and searching for matches with the
Search Criteria string. It's supposed to search for matches in these fields
of these two tables with the search string that I input. I just wanted to
make sure that this is doing what I think it's doing and that I'm not
overlooking something. Can anybody tell me?

I'm also interested in integrating this search query into a form that will
bring up the matches for verification, and if there are none, provide a way
to add new records to the Tools table. Any idea?

I'd also like to know if this posting makes any sense to you at all...

 

[Allen Browne]

Jaybird, this table really, really, really needs to be redesigned.

Whenever you see repeating fields such as [Part Number 1], [Part Number 2],
.... it always means that you need a related table where you can store many
records instead of having many fields in the one table.

One order can contain many line items. That means you need 2 tables:
- an order header table, with fields such as OrderNumber, OrderDate,
CustomerID.
- an order detail table to hold the line items. Fields such as Quantity,
PartNumber, UnitPrice.

For an example of how to build such a table, open the Northwind sample
database that installs with Access. Open the Relationships window (Tools
menu.) You will see an example of how to set up the Order and OrderDetails
tables.

 

[Jaybird]

You're absolutely right, and I know it. I'm hoping to spend the next month
or so redesigning the table and all its associated queries, forms and
reports. It's a major undertaking. This is simply a workaround until I'm
able to get the thing redesigned. Right now, in order to verify that a tool
is already in our Tool Tracking system, you have to search in the Tool Number
field from the Tools table. If it isn't there, we have to verify that it
exists in some other form in the Order Entry table. It could be under Tool
#, Part Number1, Part Number 2, Part Number 3, or Part Number4. That's up to
six searches for one Tool. We get up to two hundred tools a day. It makes
the person who is tracking these tools crazy. I promised her a quicker way
to search through all these fields. Does this do the trick?

--
Why are you asking me? I dont know what Im doing!

Jaybird

Jaybird, this table really, really, really needs to be redesigned.

Whenever you see repeating fields such as [Part Number 1], [Part Number 2],
.... it always means that you need a related table where you can store many
records instead of having many fields in the one table.

One order can contain many line items. That means you need 2 tables:
- an order header table, with fields such as OrderNumber, OrderDate,
CustomerID.
- an order detail table to hold the line items. Fields such as Quantity,
PartNumber, UnitPrice.

For an example of how to build such a table, open the Northwind sample
database that installs with Access. Open the Relationships window (Tools
menu.) You will see an example of how to set up the Order and OrderDetails
tables.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Here's my query:

SELECT DISTINCT Tools.[Tool Number] as [Tools/Tool Number], [Order
Entry].[Tool #] as [Order Entry/Tool #], [Order Entry].[Part Number1],
[Order
Entry].[Part Number 2], [Order Entry].[Part Number 3], [Order Entry].[Part
Number4]

FROM [Order Entry] INNER JOIN Tools ON ([Order Entry].[Part
Number1]=Tools.[Tool Number]) Or ([Order Entry].[Part Number
2]=Tools.[Tool
Number]) Or ([Order Entry].[Part Number 3]=Tools.[Tool Number]) Or ([Order
Entry].[Part Number4]=Tools.[Tool Number]) Or ([Order Entry].[Tool
#]=Tools.[Tool Number])

WHERE (((Tools.[Tool Number]) Like "*" & [Search Criteria] & "*"))
;

What I think this is doing is looking through the Tools table under Tool
Number, and through the Order Entry table under Tool #, Part Number1, Part
Number 2, Part Number 3, Part Number4 and searching for matches with the
Search Criteria string. It's supposed to search for matches in these
fields
of these two tables with the search string that I input. I just wanted to
make sure that this is doing what I think it's doing and that I'm not
overlooking something. Can anybody tell me?

I'm also interested in integrating this search query into a form that will
bring up the matches for verification, and if there are none, provide a
way
to add new records to the Tools table. Any idea?

I'd also like to know if this posting makes any sense to you at all...

 

[Jaybird]

Well... Honestly, I posted the wrong version of this query anyway. In place
of LEFT JOIN it should be right outer join.
--
Why are you asking me? I dont know what Im doing!

Jaybird

Jaybird, this table really, really, really needs to be redesigned.

Whenever you see repeating fields such as [Part Number 1], [Part Number 2],
.... it always means that you need a related table where you can store many
records instead of having many fields in the one table.

One order can contain many line items. That means you need 2 tables:
- an order header table, with fields such as OrderNumber, OrderDate,
CustomerID.
- an order detail table to hold the line items. Fields such as Quantity,
PartNumber, UnitPrice.

For an example of how to build such a table, open the Northwind sample
database that installs with Access. Open the Relationships window (Tools
menu.) You will see an example of how to set up the Order and OrderDetails
tables.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Here's my query:

SELECT DISTINCT Tools.[Tool Number] as [Tools/Tool Number], [Order
Entry].[Tool #] as [Order Entry/Tool #], [Order Entry].[Part Number1],
[Order
Entry].[Part Number 2], [Order Entry].[Part Number 3], [Order Entry].[Part
Number4]

FROM [Order Entry] INNER JOIN Tools ON ([Order Entry].[Part
Number1]=Tools.[Tool Number]) Or ([Order Entry].[Part Number
2]=Tools.[Tool
Number]) Or ([Order Entry].[Part Number 3]=Tools.[Tool Number]) Or ([Order
Entry].[Part Number4]=Tools.[Tool Number]) Or ([Order Entry].[Tool
#]=Tools.[Tool Number])

WHERE (((Tools.[Tool Number]) Like "*" & [Search Criteria] & "*"))
;

What I think this is doing is looking through the Tools table under Tool
Number, and through the Order Entry table under Tool #, Part Number1, Part
Number 2, Part Number 3, Part Number4 and searching for matches with the
Search Criteria string. It's supposed to search for matches in these
fields
of these two tables with the search string that I input. I just wanted to
make sure that this is doing what I think it's doing and that I'm not
overlooking something. Can anybody tell me?

I'm also interested in integrating this search query into a form that will
bring up the matches for verification, and if there are none, provide a
way
to add new records to the Tools table. Any idea?

I'd also like to know if this posting makes any sense to you at all...

 

[Allen Browne]

Okay, the original query could work with the OR in the FROM clause:
SELECT DISTINCT Tools.[Tool Number] as [Tools/Tool Number],
[Order Entry].[Tool #] as [Order Entry/Tool #],
[Order Entry].[Part Number1],
[Order Entry].[Part Number 2],
[Order Entry].[Part Number 3],
[Order Entry].[Part Number4]
FROM [Order Entry] INNER JOIN Tools
ON (([Order Entry].[Part Number1] = Tools.[Tool Number])
Or ([Order Entry].[Part Number 2] = Tools.[Tool Number])
Or ([Order Entry].[Part Number 3] = Tools.[Tool Number])
Or ([Order Entry].[Part Number4] = Tools.[Tool Number])
Or ([Order Entry].[Tool #] = Tools.[Tool Number]))
WHERE Tools.[Tool Number] Like "*" & [Search Criteria] & "*"

Now you're changing it to an outer join. It might be easier to create the
query based on a single table with the criteria, and then outer join that to
the other table.

It's honestly not worth spending time on though: much better to put the time
into the redesign in my view.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Well... Honestly, I posted the wrong version of this query anyway. In
place
of LEFT JOIN it should be right outer join.
--
Why are you asking me? I dont know what Im doing!

Jaybird

Jaybird, this table really, really, really needs to be redesigned.

Whenever you see repeating fields such as [Part Number 1], [Part Number
2],
.... it always means that you need a related table where you can store
many
records instead of having many fields in the one table.

One order can contain many line items. That means you need 2 tables:
- an order header table, with fields such as OrderNumber, OrderDate,
CustomerID.
- an order detail table to hold the line items. Fields such as Quantity,
PartNumber, UnitPrice.

For an example of how to build such a table, open the Northwind sample
database that installs with Access. Open the Relationships window (Tools
menu.) You will see an example of how to set up the Order and
OrderDetails
tables.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Here's my query:

SELECT DISTINCT Tools.[Tool Number] as [Tools/Tool Number], [Order
Entry].[Tool #] as [Order Entry/Tool #], [Order Entry].[Part Number1],
[Order
Entry].[Part Number 2], [Order Entry].[Part Number 3], [Order
Entry].[Part
Number4]

FROM [Order Entry] INNER JOIN Tools ON ([Order Entry].[Part
Number1]=Tools.[Tool Number]) Or ([Order Entry].[Part Number
2]=Tools.[Tool
Number]) Or ([Order Entry].[Part Number 3]=Tools.[Tool Number]) Or
([Order
Entry].[Part Number4]=Tools.[Tool Number]) Or ([Order Entry].[Tool
#]=Tools.[Tool Number])

WHERE (((Tools.[Tool Number]) Like "*" & [Search Criteria] & "*"))
;

What I think this is doing is looking through the Tools table under
Tool
Number, and through the Order Entry table under Tool #, Part Number1,
Part
Number 2, Part Number 3, Part Number4 and searching for matches with
the
Search Criteria string. It's supposed to search for matches in these
fields
of these two tables with the search string that I input. I just wanted
to
make sure that this is doing what I think it's doing and that I'm not
overlooking something. Can anybody tell me?

I'm also interested in integrating this search query into a form that
will
bring up the matches for verification, and if there are none, provide a
way
to add new records to the Tools table. Any idea?

I'd also like to know if this posting makes any sense to you at all...

 

[Jaybird]

Mr. Browne,

I have enormous respect for you and your opinion. You have consistently
given the clearest, most concise and elegant solutions that I have
encountered on this site. (That is not to say that I don't appreciate others
as well or as much!) I will take your advice and do the best I can.
--
Why are you asking me? I dont know what Im doing!

Jaybird

Okay, the original query could work with the OR in the FROM clause:
SELECT DISTINCT Tools.[Tool Number] as [Tools/Tool Number],
[Order Entry].[Tool #] as [Order Entry/Tool #],
[Order Entry].[Part Number1],
[Order Entry].[Part Number 2],
[Order Entry].[Part Number 3],
[Order Entry].[Part Number4]
FROM [Order Entry] INNER JOIN Tools
ON (([Order Entry].[Part Number1] = Tools.[Tool Number])
Or ([Order Entry].[Part Number 2] = Tools.[Tool Number])
Or ([Order Entry].[Part Number 3] = Tools.[Tool Number])
Or ([Order Entry].[Part Number4] = Tools.[Tool Number])
Or ([Order Entry].[Tool #] = Tools.[Tool Number]))
WHERE Tools.[Tool Number] Like "*" & [Search Criteria] & "*"

Now you're changing it to an outer join. It might be easier to create the
query based on a single table with the criteria, and then outer join that to
the other table.

It's honestly not worth spending time on though: much better to put the time
into the redesign in my view.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Well... Honestly, I posted the wrong version of this query anyway. In
place
of LEFT JOIN it should be right outer join.
--
Why are you asking me? I dont know what Im doing!

Jaybird

Jaybird, this table really, really, really needs to be redesigned.

Whenever you see repeating fields such as [Part Number 1], [Part Number
2],
.... it always means that you need a related table where you can store
many
records instead of having many fields in the one table.

One order can contain many line items. That means you need 2 tables:
- an order header table, with fields such as OrderNumber, OrderDate,
CustomerID.
- an order detail table to hold the line items. Fields such as Quantity,
PartNumber, UnitPrice.

For an example of how to build such a table, open the Northwind sample
database that installs with Access. Open the Relationships window (Tools
menu.) You will see an example of how to set up the Order and
OrderDetails
tables.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Jaybird" <UMUhtq5013> wrote in message
Here's my query:

SELECT DISTINCT Tools.[Tool Number] as [Tools/Tool Number], [Order
Entry].[Tool #] as [Order Entry/Tool #], [Order Entry].[Part Number1],
[Order
Entry].[Part Number 2], [Order Entry].[Part Number 3], [Order
Entry].[Part
Number4]

FROM [Order Entry] INNER JOIN Tools ON ([Order Entry].[Part
Number1]=Tools.[Tool Number]) Or ([Order Entry].[Part Number
2]=Tools.[Tool
Number]) Or ([Order Entry].[Part Number 3]=Tools.[Tool Number]) Or
([Order
Entry].[Part Number4]=Tools.[Tool Number]) Or ([Order Entry].[Tool
#]=Tools.[Tool Number])

WHERE (((Tools.[Tool Number]) Like "*" & [Search Criteria] & "*"))
;

What I think this is doing is looking through the Tools table under
Tool
Number, and through the Order Entry table under Tool #, Part Number1,
Part
Number 2, Part Number 3, Part Number4 and searching for matches with
the
Search Criteria string. It's supposed to search for matches in these
fields
of these two tables with the search string that I input. I just wanted
to
make sure that this is doing what I think it's doing and that I'm not
overlooking something. Can anybody tell me?

I'm also interested in integrating this search query into a form that
will
bring up the matches for verification, and if there are none, provide a
way
to add new records to the Tools table. Any idea?

I'd also like to know if this posting makes any sense to you at all...

 

[Jaybird]

Mr. Browne,

You may be amused (or exasperated) to know that I found the solution I was
looking for...

SELECT temp.[Tool #], temp2.[Tool Number], temp.ContactID, temp2.Owner,
temp.[Part Number1], temp.[Part Number 2], temp.[Part Number 3], temp.[Part
Number4]
FROM temp LEFT JOIN temp2 ON (temp.[Part Number4] = temp2.[Tool Number]) OR
(temp.[Part Number 3] = temp2.[Tool Number]) OR (temp.[Part Number 2] =
temp2.[Tool Number]) OR (temp.[Part Number1] = temp2.[Tool Number]) OR
(temp.[Tool #] = temp2.[Tool Number])

UNION SELECT temp.[Tool #], temp2.[Tool Number], temp.ContactID,
temp2.Owner, temp.[Part Number1], temp.[Part Number 2], temp.[Part Number 3],
temp.[Part Number4]
FROM temp RIGHT JOIN temp2 ON (temp.[Part Number4] = temp2.[Tool Number]) OR
(temp.[Part Number 3] = temp2.[Tool Number]) OR (temp.[Part Number 2] =
temp2.[Tool Number]) OR (temp.[Part Number1] = temp2.[Tool Number]) OR
(temp.[Tool #] = temp2.[Tool Number]);

I combined the results of two single table queries in a UNION statement. It
seems to work well and digs right down into the data without taking a ton of
time. From the results of this query, I can quickly determine where the data
is located and if there are similar entries elsewhere.

This actually helps out quite a bit in the short term, although I agree that
the Order Entry table needs to be normalized.
--
Why are you asking me? I dont know what Im doing!

Jaybird

Okay, the original query could work with the OR in the FROM clause:
SELECT DISTINCT Tools.[Tool Number] as [Tools/Tool Number],
[Order Entry].[Tool #] as [Order Entry/Tool #],
[Order Entry].[Part Number1],
[Order Entry].[Part Number 2],
[Order Entry].[Part Number 3],
[Order Entry].[Part Number4]
FROM [Order Entry] INNER JOIN Tools
ON (([Order Entry].[Part Number1] = Tools.[Tool Number])
Or ([Order Entry].[Part Number 2] = Tools.[Tool Number])
Or ([Order Entry].[Part Number 3] = Tools.[Tool Number])
Or ([Order Entry].[Part Number4] = Tools.[Tool Number])
Or ([Order Entry].[Tool #] = Tools.[Tool Number]))
WHERE Tools.[Tool Number] Like "*" & [Search Criteria] & "*"

Now you're changing it to an outer join. It might be easier to create the
query based on a single table with the criteria, and then outer join that to
the other table.

It's honestly not worth spending time on though: much better to put the time
into the redesign in my view.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Well... Honestly, I posted the wrong version of this query anyway. In
place
of LEFT JOIN it should be right outer join.
--
Why are you asking me? I dont know what Im doing!

Jaybird

Jaybird, this table really, really, really needs to be redesigned.

Whenever you see repeating fields such as [Part Number 1], [Part Number
2],
.... it always means that you need a related table where you can store
many
records instead of having many fields in the one table.

One order can contain many line items. That means you need 2 tables:
- an order header table, with fields such as OrderNumber, OrderDate,
CustomerID.
- an order detail table to hold the line items. Fields such as Quantity,
PartNumber, UnitPrice.

For an example of how to build such a table, open the Northwind sample
database that installs with Access. Open the Relationships window (Tools
menu.) You will see an example of how to set up the Order and
OrderDetails
tables.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Jaybird" <UMUhtq5013> wrote in message
Here's my query:

SELECT DISTINCT Tools.[Tool Number] as [Tools/Tool Number], [Order
Entry].[Tool #] as [Order Entry/Tool #], [Order Entry].[Part Number1],
[Order
Entry].[Part Number 2], [Order Entry].[Part Number 3], [Order
Entry].[Part
Number4]

FROM [Order Entry] INNER JOIN Tools ON ([Order Entry].[Part
Number1]=Tools.[Tool Number]) Or ([Order Entry].[Part Number
2]=Tools.[Tool
Number]) Or ([Order Entry].[Part Number 3]=Tools.[Tool Number]) Or
([Order
Entry].[Part Number4]=Tools.[Tool Number]) Or ([Order Entry].[Tool
#]=Tools.[Tool Number])

WHERE (((Tools.[Tool Number]) Like "*" & [Search Criteria] & "*"))
;

What I think this is doing is looking through the Tools table under
Tool
Number, and through the Order Entry table under Tool #, Part Number1,
Part
Number 2, Part Number 3, Part Number4 and searching for matches with
the
Search Criteria string. It's supposed to search for matches in these
fields
of these two tables with the search string that I input. I just wanted
to
make sure that this is doing what I think it's doing and that I'm not
overlooking something. Can anybody tell me?

I'm also interested in integrating this search query into a form that
will
bring up the matches for verification, and if there are none, provide a
way
to add new records to the Tools table. Any idea?

I'd also like to know if this posting makes any sense to you at all...