The Database 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.( Male/Female.... ).
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 that you forbid Table lookup inside table design. And I concur...
Your reference tables are actually a very good way to validate data entry where you want to accept only a limited set of values. It's easy to tell what the expected values are because they're right there. But there is a downside to having these tables in the same database as your other data: someone might, accidentally or intentionally, change the validation values. E.g. you probably don't want to have more sexes available than just male and female (although some people do).
One way to solve this is to put all your reference tables away in a separate Access database file and link to the tables you need from your main database file. Then, put restrictive security permissions on the reference table DB so that most people can't edit it. That will keep your reference tables pristine.