Simple query but I am drawing a blank today.

M

mack5511

Guppy
Hello,

I am sure this is a fairly simple query but I am drawing a blank today. I have a single table of 4500 rows that contains the following fields.

Table Name = RK_BOM

Model_Number
Model_Description
Item_Number
Option_Class
Country

Background on table data: The table contains a list of models from different countries. The models are composed of one or more items to form a kit. The idea is to compare all the items and see how many of the kits contain similar items.

From the info above I am trying to write a query to look at all the Item_Numbers and match them to all the applicable Model_Number(Kits).

Any help would be appreciated.

Thanks,

Mack
 
I may be missing something here but is it not just a

select Item_Number, Model_Number from RK_BOM GROUP BY Model_Number

?
 
Penhall,

It may be my attempt at explaining is off. That query errors out as Item_Number is not part of the group by clause. I will try to give a better explanation.

The Item_number field contains the global items list for all countries models of kits. The person who is requesting this wants to see how many kits throughout the world contain similar items. If 2 kits contain the exact matches she wants to know. But also if those same 2 kits have another kit that has 3 of the 4 items for example she wants to see that too. I am thinking I need to count the times a certain Item number shows up in the list and determine the matching off that number. If an item_number exists only once it is not in any other kits. I am trying to work through the logic as we speak. I don't think it is as simple as a group by query. I want to thank you for answering. I do appreciate you help.


Thanks,

Mack
 
This is a bit confusing to grasp your table data (it looks like it a flat table rather than a relational database)

Is
a) Model_Number is a unique kit number
and
b) Item_Number is actually a LIST of parts

EG:
Model1, (part 1, part 2, part3 etc)

or does each Item_Number exist in a seperate record.

Model1,part1
Model1,part2
 
Penhall,

The data structure is like the second example you have. So my data looks like this.

Model_1, Model Description_1, Item_Number_1, Country_1
Model_1, Model Description_1, Item_Number_2, Country_1
Model_1, Model Description_1, Item_Number_3, Country_1
Model_1, Model Description_1, Item_Number_4, Country_1
Model_1, Model Description_1, Item_Number_5, Country_1
Model_1, Model Description_1, Item_Number_6, Country_1
Model_1, Model Description_1, Item_Number_7, Country_1


Model_2, Model Description_2, Item_Number_1, Country_2
Model_2, Model Description_2, Item_Number_2, Country_2
Model_2, Model Description_2, Item_Number_3, Country_2
Model_2, Model Description_2, Item_Number_4, Country_2
Model_2, Model Description_2, Item_Number_5, Country_2
Model_2, Model Description_2, Item_Number_6, Country_2

etc


Models can have as little as a single part or many. The Item_numbers are unique to the item but they can make up any kit worldwide. We have 14 countries. This is an attempt to simplify the Bill of Materials across the company. So all the models with a similar parts list can be identified.

I am sorry if this seems obtuse I have been called upon to try and help out someone else and am still working through everything also.


Thanks,

Mack
 
select Item_Number, Model_Number from RK_BOM GROUP BY Item_Number

Would return something ike

Item_Number_1, Model_Number1
Item_Number_1, Model_Number2
Item_Number_1, Model_Number3
Item_Number_2, Model_Number1
Item_Number_3, Model_Number1
Item_Number_3, Model_Number2
Item_Number_3, Model_Number3

or simply

select Item_Number,Model_Number from RK_BOM order by Item_Number (asc or desc)

to get a list of Item_Numbers with corresponding model_numbers

BUT

Do you just want Item_Numbers that appear multiple times.

- again, just trying to think this through with you.
 
Penhall,

From my thinking the only item numbers that would appear more than once would mean they are part of multiple models.So if that was the case I could eliminate the single items from the list and that would leave only a subset of the models that have items in common. Then a group by might work better ultimately I will be exporting the results to an Excel sheet so I can do some sorting and grouping on that end if I have the relevant data. I can put all the unmatched items on one worksheet and the matched on a separate worksheet. I am not sure if this helps or not.


Thanks,

Jim

P.S. I will sleep on it and attack it fresh in the morning. This was dumped in my lap with a hurry hurry hurry can you help me. Needless to say it was not part of my work before today.
 
select Item_Number, Model_Number
from RK_BOM
GROUP BY Item_Number
Having Count(Item_Number)>1

and

select Item_Number, Model_Number
from RK_BOM
GROUP BY Item_Number
Having Count(Item_Number)=1
 
Penhall,

After some sleep and a discussion with some others it was not as simple as I had thought. I needed to compare every kits contents against every other kit in the list and then cross reference the parts to give a display of one kits contents and the list of other kits in common. Lucky for me we have a guy that knew exactly what was needed and he took over. I just wanted to thank you again for your help.



Thanks,

Jim
 
You must log in or register to reply here.
Back
Top