Create new fields from duplicate records

[Guest]

Hi! I have a table with duplicate records like this:

item_number, vendor_number, vendor_name

There are 2 vendor number/names for some, but not all, of the item numbers,
creating duplicate records.

I want to eliminate the duplicates by creating two new fields,
vendor_number_2 and vendor_name_2.

Possible? THANKS!

 

[tina]

as you have it now, the table is not normalized. doing what you ask will
make it *more* non-normalized. you should have a table of vendors to hold
the VendorNumber, which i assume is unique to each vendor, and VendorName,
as

tblVendors
VendorNumber (primary key)
VendorName
<any other fields that describe a vendor.>

i assume you also have a table of items, and that each ItemNumber is unique
to an item, as

tblItems
ItemNumber (primary key)
ItemName
<other fields that describe an item.>

i'll further assume that not only may one item have multiple vendors, but
also that one vendor may be associated with multiple items. that's a
many-to-many relationship, that you model with a "join" or "linking" table
between tblVendors and tblItems, as

tblItemVendors
ItemID (foreign key from tblItems)
VendorID (foreign key from tblVendors)
you can use the two fields as a combination primary key for this table,
which will ensure that each *combination* of item and vendor is listed only
once, but each item may be listed multiple times, and each vendor may be
listed multiple times.

hth