First thing I would do is to council you to reconsider your table structure.  See the Too Many Tables video for more info.

The other thing you could do, as you mentioned, is use a Union Query and build your search on that query.
Be aware that a Union query is not editable and requires that you have the same fields in each table to work.  The draw back is definitely speed in that case.

Additionally the judicious use of Indexing can greatly speed up your searches.
To many indexes can actually slow your data base down however, so use them sparingly.

You can setup your forms to default to filter.

If they come from the main menu to be searching by a type you could then default the new functionality to use those values.

Thanks for the notes!

First off, I want to apologize if my posts are never clear. I tend to ramble and talk in circles, so sometimes my questions aren't clear.

@Scott - My question was rooted in the fact that I was aware I had too many tables and I wanted to consolidate things. Plus the fact that I am using a union query for two particular searches, and it takes 30 seconds to load. Which is why I want to consolidate the queries.

@Scott Re:Indexing - The auto number is really the only field I can index (without duplicates) As I've had employees get hired by those companies and become technicians; I've had previous casino employees get appointed (by the tribe) to the board of directors, or get hired by Tribal Regulatory, or become a Gaming Commissioner. Due to this, I can't even use SSN as a (no duplicate) indexed field.

@Alex - yeah I'm aware I could filter the forms, I figured I could keep the "persons type" separate by adding a WHERE statement to the SQL/VBA that the search function for those tables/forms are based on.

With that said, the one thing I am not sure how to do is, in those particular forms, how to make sure the "persons type" is associated correctly, if I consolidate these tables into one table, but still use filtered searchs to keep each type separate. As said, if I use the casino employee search form to add an individual that will be associated as a casino employee, and add a vendor entity/company in the company search screen? If it was the same table, I would use a default value when adding a new record, but since it would be a default value based on the form that the record is being added from, and not the table, that's the part I'm not sure how to accomplish (without adding a dropdown, requiring the end user to specify the "person" type).

Hopefully that clears up what I'm trying to accomplish?

Hi Sean,
I mainly agree with Scott on the too many tables, but I also believe the issue relates more to focusing on specific topics for each table. I see many repeated entities in the tables. I would urge you to pare the tables down to specific topics. If you begin to see item repeated then examine the need for another table. Reexamine your table structure and relationships to each.
HTH
John

Hi Sean,
I mainly agree with Scott on the too many tables, but I also believe the issue relates more to focusing on specific topics for each table. I see many repeated entities in the tables. I would urge you to pare the tables down to specific topics. If you begin to see item repeated then examine the need for another table. Reexamine your table structure and relationships to each.
HTH
John

ps: You want to consider Many to Many relationships, for I think you have a few. That may help with the issues you described in the last paragraph.

@John

Thanks for the notes John. The repeated items are actually purposeful, unfortunately. Each entry has a unique Certification Number. Also our State Gaming Agency requires companies be licensed separately from the TLS (Tribal Lottery System) Manufacturer/Distributors and Sports Wagering Vendors. The Tribal-State Compact Appendix S requires they be licensed separately. Since these entries all generate certificates, clear letters and have unique Certificate Numbers, License Dates and Expiration Dates, there is no easy way of having that specific information - not without relegating the certifications to a sub-table, which would make the functionality of my database (generating certificates, clear letters, checking certification status on the State Website, submitting State Gaming Agency Personnel Termination Notifications, generating reports) not work - at least not work with my current level and understanding of access (which is admittedly limited).

If you search "IGT" on our States Database - https://fortress.wa.gov/wsgc/etransfer/OnlineServices/search/license-search.cshtml?employerno=IGT&what2do=getemployer&getrecord=Get+Record

you'll see 6 different entries. My database has to mirror this.

While the note about repeat entries is a valid note and tells of poor database design, on my part, hopefully, this shows I can't get around this particular issue.

Here are screenshots of the forms and reports from my front end. I have some other items in the back-end, as well as user login, and add user screen - but I won't include those because I feel like those elements are aspects of the database I can't really take credit for since I either didn't make the code from the ground up and/or don't have a line by line understanding of how those functions work. I blanked out sensitive information and identifying factors, even though some of the certification information is actually public record.

I just wanted to show that this database I created was more than just those search forms. Also wanted to show there are a number of features in the database that are specific to the license and/or person type.

Honestly, I am feeling a little defeated in this thread, as I knew from the start of this thread I wanted to take four of those tables and merge them into a single table. However the one thing I was trying to figure out is how to maintain those individual search forms so my end users can add companies and person types without having to specify what type they are when being added to those specific forms. That was the main factor I have not been able to figure out. If I can figure that out, I can manage to filter those continuous search forms accordingly and get everything else reconfigured accordingly.