Please Look at this query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jaybird - 16 Jul 2007 19:52 GMT

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...

Signature

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

Jaybird

Reply to this Message

Allen Browne - 17 Jul 2007 02:07 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Here's my query:
>
[quoted text clipped - 29 lines]
>
> I'd also like to know if this posting makes any sense to you at all...

Reply to this Message

Jaybird - 17 Jul 2007 13:28 GMT

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?

Signature

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

Jaybird

> Jaybird, this table really, really, really needs to be redesigned.
>
[quoted text clipped - 46 lines]
> >
> > I'd also like to know if this posting makes any sense to you at all...

Reply to this Message

Jaybird - 17 Jul 2007 13:50 GMT

Well... Honestly, I posted the wrong version of this query anyway. In place
of LEFT JOIN it should be right outer join.

Signature

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

Jaybird

> Jaybird, this table really, really, really needs to be redesigned.
>
[quoted text clipped - 46 lines]
> >
> > I'd also like to know if this posting makes any sense to you at all...

Reply to this Message

Allen Browne - 17 Jul 2007 14:11 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Well... Honestly, I posted the wrong version of this query anyway. In
> place
[quoted text clipped - 60 lines]
>> >
>> > I'd also like to know if this posting makes any sense to you at all...

Reply to this Message

Jaybird - 17 Jul 2007 15:42 GMT

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.

Signature

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],
[quoted text clipped - 82 lines]
> >> >
> >> > I'd also like to know if this posting makes any sense to you at all...

Reply to this Message

Jaybird - 17 Jul 2007 18:10 GMT

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.

Signature

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

Jaybird

Reply to this Message