Re: Lookup Tables and Field Validation Rule Properties

Rick Brandt wrote:
"samah" <samah@xxxxxxxxxx> wrote in message news:O6Sz9sWDHHA.3228@xxxxxxxxxxxxxxxxxxxxxxx
Access Novice - WinXp/Access 2003

I am in the process of designing my first database and plan to use this
mainly to teach myself Access. The purpose of this database is to store
employees data and track down employees personal info, employment
history, salary history, leave records, Visa/Residence Status (75% of
the employees of the company are expats of different nationalities) etc.
etc..

The tables in my original design contained many lookup fields but after
hanging over in this forum for quite some time now and understanding the
perils of lookup fields, I re-designed the database by removing the
lookup fields and putting them in many small lookup tables.

Now I have ended up with a single subject(employees) database with
numerous small tables, most of them directly linked to the main
employees table through Employees ID.

I feel the need to reduce the number of tables in the database since I
plan to add new features like sales order processing, invoicing etc at a
later stage. So I seek your valuable advice.

When a field value is drawn from a limited list of possible values, what
is the most efficient way of getting that value into the field? through a lookup table or directly into the main table with field validation rule property set to the list of all possible values? Instead of applying the field validation rule property at table level, can it independently be applied at form level? If yes, how?

Thank you for your time in advance.

I have seriously taken your advices given in this forum and really
started with a paper, pencil and a good eraser. So I hope you won't have
any hesitation in helping me! <grin>.

Don't worry about table propagation until you get to a VERY large number. To answer your question you should use both a lookup table to drive a ComboBox on a form for making entries and also use referential integrity between the data table and lookup table to make it impossible to enter a value that is not found in the lookup table.


Thank you, Rick. May be I didn't make myself very clear.

tblEmployees
-----------
empID - PK
empFName
empLName
deptID - FK
<other fields>


tblDepartment
-------------
deptID - PK
deptName

I have only 3 records in the departments tables. Isn't it efficient to remove the department table, replace the deptID(FK) field in the Employees table with a new field named DeptName and setting its field validation rule to (In "DeptName1","DeptName2","DeptName3") ?

The other question I asked was, instead of setting the above validation in the table level, can't I implement it in the Form Level. May be in the form that I am going to use for the data entry of Employees table, I should be able to add a combo box which could display a list of the above values to choose from?

Hope I made myself clear this time.

thanks.

samah.
.

Relevant Pages

  • Re: Lookup Tables and Field Validation Rule Properties
    ... I am in the process of designing my first database and plan to use this ... employees data and track down employees personal info, ... The tables in my original design contained many lookup fields but after ...
    (microsoft.public.access.gettingstarted)
  • Re: OT: SQL
    ... query processing. ... FROM Employees e, Employees m, Management mgt ... Manager and Employee Salaries. ... The scheme used does not model database files in general, ...
    (sci.logic)
  • And yet another one from the mind of Lohkee!
    ... Internet Content Blocking Software ... Some organizations that allow employees to surf the net have ... a database of websites that have been categorized by type of the content ... existence of a web site before you can categorize it. ...
    (comp.security.misc)
  • And yet another one from the mind of Lohkee!
    ... Internet Content Blocking Software ... Some organizations that allow employees to surf the net have ... a database of websites that have been categorized by type of the content ... existence of a web site before you can categorize it. ...
    (comp.os.ms-windows.nt.admin.security)
  • And yet another one from the mind of Lohkee!
    ... Internet Content Blocking Software ... Some organizations that allow employees to surf the net have ... a database of websites that have been categorized by type of the content ... existence of a web site before you can categorize it. ...
    (microsoft.public.win2000.security)
Quantcast