Lookups...

[SteveF]

...are, I fully accept, the work of the devil. So what do we use instead?

My last database ran into some issues because of my clumsy/inept design and probably the biggest mistake ( among many ) was using lookups to reference one table from another. I want to get this one right from the beginning.

I've had a good search through the forum, really I have, and have found lots of reasons not to use lookups but not too much in regard to what to use instead. Can somebody point me right?

 

[John Big Booty]

Table Level Lookups should be avoided at all cost, but Lookup Tables are SOP. See this sample for an example of how it works and how it can be managed.

 

[SteveF]

Thanks for the sample, I like that and can see how it's working I think. It looks to me like the tables don't reference each other, the form references them both.

What if I'm trying to reference tables directly though? If I have tbl_clients and tbl_sites, where one client may have several sites, I would have used lookups in the sites table to select the appropriate client.

What would be the alternative to lookups there? Or would that be bad design and there is a far better way to do the job?

 

[SteveF]

Resolved I think, forms do the looking up. No need to do it at table level.

Thanks John

 

[John Big Booty]

The table reference each other through the Primary Key (PK) in the sub table which is stored in the main table as a Foreign Key (FK).

In the situation you describe, you would have a further sub table for the different sites, the Schema might look something like;

TBL_Client
ClientID (PK)
ClientName
etc...

TBL_ClientSites
SiteID (PK)
ClientID (FK)
SiteAdd
etc...