Am I modelling this incorrectly?

  • Thread starter Thread starter GHS
  • Start date Start date
G

GHS

I have a table that lists valid commodity types
tTypes
Name field

I have a table that lists valid manufacturers
tMfr
Type field (lookup from tTypes)
Name field

I want... a parts table that has:
tParts
ID
Type field (lookup from tTypes)
Mfr field (***lookup from tMfr but based on this current row's Type)
Name field

How can I, or should I, have the tParts.Mfr be "select distinct name from
tMfr where tMfr.Type = **THIS ROWS'S Type**"?

I'm trying to use this on a form, so maybe this is really a forms question
but it feels like I modeled the data wrong.

Thanks for your help...

--GHS
 
What I notice is that you've called several fields "Name". This is a
reserved word in Access. Using that will confuse both you and Access.

Consider changing the names of these fields to something more meaningful,
such as:
TypeName and MfrName

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Just some small changes to your tables:

tbl_Types
TypeName (PK)

tbl_Mfr
MfrName (PK)
TypeName (FK)

tbl_Parts
PartID (PK)
MfrName (FK) [An MfrName will be associated with a TypeName in tbl_Mfr, so
you should not use TypeName here]
PartName

A table is not an appropriate place for doing lookups. Your tables contain
data, which a query will then bring together.
 
Back
Top