There must be a better way to validate data entry (1 Viewer)

I am auditing someone else Database. It consists of 4 entities and some associative tables. A MAJOR part of the tables (15 tables) are nothing but lookup tables to insure valid data entry. I call them reference tables. They are mostly STATIC.( City.... ).

Whenever you design a form for data entry, you use Combo Boxes to relate to these tables to ensure valid input (their main purpose in life!!!).

Is there a way to reduce ALL these ref. tables. I know from your wonderful forum that you forbid lookup data type. And I concur...

Help please

I know from your wonderful forum that you forbid lookup data type

Click to expand...

No, it is Lookups on TABLES that are a problem. Combos on forms are GOOD. Lookups on tables are BAD.

I have a mini-app that I add to each new application I create. This mini-app has existed in one form or another since around 1980. It is now in Access. It includes two tables. tblCodeTables and tblCodes. A form and a subform and two reports. One long, one condensed. In most applications I create queries for each "table" and use those as the RowSources for combos.

This method works well for all simple lookup tables.

Here's a copy. There's a number of code modules that have nothing to do with the app but you might find them useful.

Thank you Pat for your support.
Yes, that what I wanted to say...Lookup in Tables where I put a list of text fields INSIDE the table design.
I will look at your work and I might come back with questions. Thanks again.

Pat, It is obviously a well thought approach and I will spend some time studying it , then adapt it for my needs.

For a quick fix, let me indulge you a bit in the following idea.
If I create a tblReferance, each column represent it's own data validation list. ( This table will replace the 15 tables above)

Then when it comes to binding the data Entry combo box in the form I use a query to select THAT column only. The box will be populated with the content of that column only.......

Your response will be greatly appreciated

I would NOT recommend that. From how I understand your post, you would have 15 fields in your table, and for each combo box, you would populate it with a query which is pulling back one of those fields?

Not good.

For a start, what if one lookup has 100 values and one only has 10? You would then have a table where for one field, you will have 90 null values. Pointless.

Secondly, if you are making a selection from a combo box and storing that selection in a record you are creating, you don't want to store the value, but usually the ID. For example, you would have a tbl_CityLookup which would have the fields CityID and CityName. CityID would be a Primary Key, numerical field. When you are creating a new record and select a city, you wouldn't store then City name in your new record, but the CityID.

Thirdly, what if you need to apply different functionality moving forward for some of your lookup data? For example, I do a lot of database work on Document Management systems. We have something like a Document Status Code, which is a lookup value. But the Status may also drive functionality in the Document Management System, so my lookup table doesn't just have StatusID, StatusDescription - it also has other fields such as a Yes/No field for ShowInResults (for example).

I believe you should always keep separate tables for separate lookup data sets.

I used to use a table specifically for lookup data which looked something like.

tblLookup
ID (auto)number Long PK (I always had several instance so I generated my own Long PKs)
parentId number - In case I want to do a recursion
luType Text (or number) - type of data
luText Text - Text value (if needed)
luText2 Text - 2nd Text value (if needed)
luNumber Long - Numeric value - (if needed)
luNumber2 Long - 2nd numeric value (if needed)
deleted Yes/No - Has the value been marked deleted (I didn't physically delete records)
dateStamp Date/Time - Date & Time the record was altered

I then created a set of lookup queries to translate chunks of data in the table into meaningful sets of data for use with combo boxes.

Example:


If I had a record with the luText "<All records>" and luType "All records" I could change the code slightly to give me an all records entry for reporting purposes.


A useful side effect was that I could use one, or two, generic maintenance screens and just change a few things like labels, title and the default value of the field luType depending what data I was working with.

I agree , my idea is very crude, but may be if I use VBA I can clean it up, after all , what I need is to create the elements of an array to feed the combo box.
As for the One Table solution given above, I made a first attempt and it worked. Thanks.
Using KISS!!, I created the tblLookup with 3 fields , applied the query and I got my virtual table to use with combo box.
My questions are:
You provided "optional" numeric pairs in case my data is numeric ???
The "AllTypes" is to create a report from all records ?? Why?
If I just omit the where I will get all the records that I can use to create a report.
Lastly, the maintenance part ...I really need you help in putting me on the right track to finalize this clean approach of yours.

I include all the fields in the table but only populate the ones I require for the data type.

I use the "All Records" ("<All values>") field when I'm creating Where Clauses on the fly using VBA, for instance.


The attached database is only rough but it gives you an idea of the sort of thing I would try and build.

Form frmMaintenance presents a list of data types
Form popMaintenance does the actual maintenance

With people like you, I wake up looking forward for another beautiful day.....