Linked Records?

[Daniel]

I have a database which lists different suppliers.

Some suppliers are know as more than one name - "supplier 123" might also be
known as "supplier 213" and "supplier 870" my question is this, when a
supplier with multiple names is edited/updated/deleted or whatever, is it
possible to change the data in all other records relating to that supplier,
just by updating one record, rather than editing each supplier individually.

I have two fields - one with the "suppliers name" and one with the
"suppliers main name". The only thing which changes is the "suppliers name",
so the "suppliers main name" is the same for all records relating to that
one supplier.

So it would look something like this:

SupplierName SupplierMainName
Supplier123 Supplier123
Supplier213 Supplier123
Supplier870 Supplier123

I'm not sure if I can do it any other way, but I still want to be able to
view all of these records if I need too.

Thanks in advance - hope it all makes sense!

 

[tina]

sounds like you have a normalizaton issue. if a single supplier goes by many
names (or perhaps has subsidiaries?), and based only on the information you
posted, i'd recommend a tblSuppliers to hold the "main supplier name" and
all other data that is common to each "supplier name". then a child table,
perhaps tblSupplierDetails (or maybe tblSupplierSubsidiaries), with a record
for each "supplier name" and all other data that is specific to one supplier
name, and with a foreign key field to hold the primary key value of the
related record from tblSuppliers. as follows:

tblSuppliers
SupplierID (primary key)
SupplierMainName (though i'd probably just call it supplier name)
<other fields that describe a supplier>

tblSupplierSubsidiaries
SubsidiaryID (pk)
SupplierID (foreign key from tblSuppliers)
SubsidiaryName
<other fields that describe a specific subsidiary>

relationship is
tblSuppliers.SupplierID 1:n tblSupplierSubsidiaries.SupplierID

hth