So I apologize in advanced if I describing anything wrong. I generally have only lurked to hack together the queries I've needed. I just can't seem to get this one together and I feel like I'm close. I've been at this for days...
I have a list of Items that have an Item number and a manufacturer number. Some manufacturers have the same number for a few different items so I need to combine the manufacturer number with the item number to make a unique manufacturer number for our system to except.
Example of Existing
Part |
Item |
MFRnum |
MFR |
black widget |
widblk |
wid200 |
Acme |
white widget |
widwht |
wid200 |
Acme |
spocket |
spck1 |
spck14 |
Spacely Sprockets |
spocket |
spck2 |
spck15 |
Spacely Sprockets |
Example of what I'm trying to do
Example of Existing
Part |
Item |
MFRnum |
MFR |
black widget |
widblk |
wid200_widblk |
Acme |
white widget |
widwht |
wid200_widwht |
Acme |
spocket |
spck1 |
spck14 |
Spacely Sprockets |
spocket |
spck2 |
spck15 |
Spacely Sprockets |
I have a query that will go through the table and find the parts with duplicate manufacturer numbers but I can't seem to rewrite it any way to update the original table only when finding matching numbers.
Code:
SELECT FILTERED.Item, FILTERED.MFRnum, MFRnum+"_"+Item AS DupMFR
FROM FILTERED
WHERE (((FILTERED.MFRnum) In (SELECT [MFRnum] FROM [FILTERED] As Tmp GROUP BY [MFRnum] HAVING Count(*)>1 )));
The table name is called FILTERED with more fields then in the example tables above.