Finding Matches in 6 columns...

[donbettis]

Here is what I need to do…

I have a table with 8 columns…

Date | Order Number | Item 1 | Item 2 | Item 3 | Item 4 | Item 5 | Item 6 |

How can I look at the Item 1 thru Item 6 columns and see how many times Part Number 45666 and 54667 were purchased together?

The Part Numbers may be in any of the 6 columns…And I really have no idea what part numbers will match up… We have over 90 different part numbers…

Basically my boss wants me to run a report that will show him what parts are being sold together… Kind of like people that buy this part also buy this part at the same time…

Thank in Advance

 

[Jack Cowley]

Your table is not normalized. Once you normalize your table then you will easily be able to get the information your boss wants.

tblOrders
OrdersID (PK)
OrderNumber (FK)
OrderDate
Item

hth,
Jack

 

[dcx693]

Jack is totally right that you should normalize the tables. You will be far better off for it in the long and short term.

However, if you must find the solution and are unable or unwilling to change the structure of your database, you can create a query that calls a custom function to find occurrences of those part numbers within a particular order. Not pretty, but it can be done.

 

[donbettis]

Thanks for both replies...

Unfortunately I did not design the database...It was here before me...I spoke to the powers at be about rewriting the database...They said whatever I decided would be fine with them...I wonder how long that will last

Therefore I have already rewritten most of the database...I have normalized the tables or at least I think I have

My Table structure is as follows:

Order Detail ID
Order ID
Product ID
Quantity
Unit Price
Discount

So how can I get this report they want?

Thx

 

[Jack Cowley]

Use a Totals query and you can filter the records on Order Number or whatever you like. Then you can get a total for the various part numbers that you want. You will probably have to do a bit of fiddling but you will be able to sort it out...

hth,
Jack

 

[donbettis]

thx...I'll give it a try