Continuing our series of Tips – Tricks and Guidelines with Microsoft Access shares some additional items for this month. The ongoing articles will continue to focus on the tables within an Access database.
Using tables for drop down lists?
The tables within an Access database are used to store all the database information. This is the fundamental purpose for all the data within the database. But what other reasons would there be to store other components of the database?
Throughout most databases, there are choices for drop down lists in forms. For example, a customer table will store a field such as a state within the United States. Another example would be a specific product that a company is selling or manufacturing or another example would be a table to store employees, etc.. There is an option in a form called a value list, but to better manage the choices, a separate table should be used to store the specific items that are contained in the list.
To ensure that each record has the proper value, the table of options should be the primary source of the value. You would not want the end user to “make up” values on the fly as this could lead to entering inconsistent values for whatever the reason.
The employee table as noted below may have the following design.
As noted, it makes sense to set the status of each employee to “Active” as these are the employees that will be included in the drop-down list. By not deleting them, then they are still available for future use.
Tip – Naming the actual dropdown list table a proper way can be helpful. For example, naming all tables for drop down lists starting with “TblDrp……” will store them all together in the database window as noted in the below figure:
Validation of data when entering data into the database at the table level
Along with drop down tables forcing accurate data into tables, another method within the table itself is setting a validation rule. This is done in the design of the table inside the field properties itself.
For example, to make sure an accurate shipping date in the future is entered, you can use a validation rule to control this by doing the following.
Validation Rule:[ShippedDate] >=Date()
Validation Text: The shipping date must be the current date or a date in the future.
See the figure below
When the validation rule is “broken”, then the following message appears. See below figure.
These are just a few time saving tips when using Access tables. If you are having trouble knowing how to get started with Microsoft Access, reach out to Arkware today for any database needs.