Help with SQL query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, I need your help again.

I have a query with records containing all customer orders. I need to run a
query that takes the items on the current order and lists the details for
every other order that included those same items.

Field Names:
Customer_Name, Item_Num, Mos, Order_Num

The same Item_Num could be found on several Order_Num records. Is there a
quick way to look at the current Order_Num and list every other Order_Num
including other fields where the Item_Num is the same?

I've tried a few things, not getting anywhere.

Thanks!
Mary
 
Dear Mary:

I believe you just need to join the table on itself using the
Item_Num, at least for an initial approximation:

SELECT *
FROM YourTable T
INNER JOIN YourTable T1 ON T1.Item_Num = T.Item_Num
ORDER BY T.Order_Num, T.Item_Num, T1.Order_Num, T.Order_Num

I have used the name YourTable where you should substitute the actual
name of your table. Also, I have applied some sorting to give this
some structure for viewing the results. You may wish to change this
sorting according to what helps you analyze what was produced.

The first thing you may notice is that every Order/Item has as one of
its related rows that same Order/Item. This is probably not useful
information. It could be eliminated as follows:

SELECT *
FROM YourTable T
INNER JOIN YourTable T1 ON T1.Item_Num = T.Item_Num
WHERE T.Order_Num <> T1.Order_Num
ORDER BY T.Order_Num, T.Item_Num, T1.Order_Num, T.Order_Num

I'm not sure what other changes might be useful. Perhaps you do not
need all the columns, and would like them relabelled, something like
this:

SELECT T.Order_Num, T.Item_Num, T.Customer_Name, T.Mos,
T1.OrderNum AS AnotherOrder, T1.Customer_Name As OtherCustomer
FROM YourTable T
INNER JOIN YourTable T1 ON T1.Item_Num = T.Item_Num
ORDER BY T.Order_Num, T.Item_Num, T1.Order_Num, T.Order_Num

Perhaps this is a good starting point and you can tell me what else
might be useful.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top