Relational Design Question

[Bill]

I have a Supplier table that contains 'supplier name' and 'supplier number'
fields. Both are unique. I have chosen 'supplier number' to be primary.

In a related Contracts table I also have supplier number and relate to that
with cascade update selected.

I would like also to have the 'supplier name' present in the related table
with cascade update selected, but can't see how to do it. I have
experimented with a few things but none work as I would like.

Any suggestions please.

Bill.

 

[Rick Brandt]

I have a Supplier table that contains 'supplier name' and 'supplier
number' fields. Both are unique. I have chosen 'supplier number' to
be primary.
In a related Contracts table I also have supplier number and relate
to that with cascade update selected.

I would like also to have the 'supplier name' present in the related
table with cascade update selected, but can't see how to do it. I have
experimented with a few things but none work as I would like.

Any suggestions please.

My suggestion is don't do it. It would violate proper design rules. You can
display the name anywhere you like by pulling the name from the supplier table
using your stored ID. That is the proper way to do it.

 

[Bill]

Rick, Just let me put that back to you to check I have understood.

Presumably the design violation would be that I have same data, outside of a
related field, in two different tables. Correct way is that whenever I pull
data from the related table but need the supplier name rather than number I
link the query to the Supplier table. Is that right?

Cheers.
Bill.

 

[Rick Brandt]

Rick, Just let me put that back to you to check I have understood.

Presumably the design violation would be that I have same data,
outside of a related field, in two different tables. Correct way is
that whenever I pull data from the related table but need the
supplier name rather than number I link the query to the Supplier
table. Is that right?

Yep.

 

[Jason Lepack]

Rick, Just let me put that back to you to check I have understood.

Presumably the design violation would be that I have same data, outside of a
related field, in two different tables. Correct way is that whenever I pull
data from the related table but need the supplier name rather than number I
link the query to the Supplier table. Is that right?

That's exactly correct.

Cheers,
Jason Lepack

Cheers.
Bill.

My suggestion is don't do it. It would violate proper design rules. You
can display the name anywhere you like by pulling the name from the
supplier table using your stored ID. That is the proper way to do it.

 

[Jeff Boyce]

Bill

So, you are saying that you live/work in a universe in which two separate
suppliers never have the same supplier number. I take it, then, that these
are id numbers that YOU assign to suppliers, rather than numbers the
suppliers provide to you.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Bill]

Both supplier name and supplier number are unique individually and if
concatenated. The situation arose because I inherited the table design from
someone who presumably wanted the supplier name and supplier number in the
related table instead of simply linking when additional data was required.
The design has now been changed as advised in this thread. So my universe is
now in line with yours I hope!!

Regards.
Bill.