Lookups at table level

[Dairy Farmer]

Found this interesting and would like some info.

You don't by chance have lookups defined in the field at table level do you? If so, you shouldn't.

Is this bad practice?
How would this affect my DB?
Any useful comments please.

I have a table that has lookups.

T_Animal
IDAnimal
AnimalNumber (Indexed No Dup's)
AnimalGroup (Lookup)
AnimalSex (Lookup)
AnimalBreed (Lookup)
AnimalOrigin (Lookup)
AnimalCullReason (Lookup) .....

T_AnimalGroup
IDGroup
GroupNumber
GroupName

AnimalGroup = IDGroup

Ans so on.

 

[pbaldy]

Lookup tables are fine; most of us recommend against lookup fields:

http://www.mvps.org/access/lookupfields.htm

 

[Dairy Farmer]

Tested removing the lookups and it works fine.

What about when I need to use Allow Multible Values(Access 2K7)?

Example:

T_MedicalProblem
IDProblem
MedicalProblem (no dup's)
RecommendedTreatments (Multible Values, from ProductName)

T_Treatment
IDTreatment
ProductName (no dup's)
PrescribedDosage
PrescribedRoute (also Multible Values from T_Routes, i.e. IM, IV, Oral...)

 

[vbaInet]

Multivalued fields are much harder to manipulate and in my opinion not true one-to-many relationships. They are much harder to manipulate because in most cases you would need DAO for this.

Lastly, they aren't supported in any other known database like SQL Server or Oracle.

It has its pros though

 

[Dairy Farmer]

The DB will only be used as ACCDE with Access Runtimes (if use does not have Access 2K7+). Dont think I will go the SQL Express route. Too much of an install.

It is mainly for assigning mulit recomended treatments to a medical problem. When recording the actual treatment they are not used because some factors change. i.e. dosage and route. For the actual treatments I have another table that is indexed so that the same treatment is not repeated for the same animal on the same day. Some treatments can be repeated, but then I just change the dosage accordingly.

T_TreatmentGiven
IDTreatmentGiven
DateGiven
AnimalGiven (from T_Animal) - allow dup's if not dup of Date and Problem and Treatment
ProblemGiven (from T_Problem)
TreatmentGiven (from T_Products)
DosageGiven (no links)
RouteGiven (single value from T_Routes)

 

[boblarson]

Tested removing the lookups and it works fine.

What about when I need to use Allow Multible Values(Access 2K7)?

My suggestion - do it using accepted methods (using a Junction table) which stores the record ID and the ID of the Item in another table and then you use a subform to add multiples. Multi-valued fields actually do the same but the table is hidden to you and is much more painful to get the data out when you go for reporting.

 

[gemma-the-husky]

I think the problem is that MS are adding some "non-relational" functionality, which may seem appealing to less-advanced developers

all of this functionality can be added easily in a relational manner without needing the MS usage. One reason for not using these MS add-ons is that they will be access-specific, and probably wont be available if you upsize ot a different back-end. (eg SQL). Personally I would avoid stuff like this.

eg, I hardly ever try to build constraints into access tables. I prefer to validate everytihng on input forms.

 

[Dairy Farmer]

I prefer to validate everytihng on input forms.

Having problems with that. Had a look at some of the posts re form validation. Will post a thread in Forms.

 

[pbaldy]

Here are the basics of validating on a form:

http://www.baldyweb.com/BeforeUpdate.htm

 

[Dairy Farmer]

Thanks. All works now.