Multitable search - How To Create Advanced Search Forms, Filters, Parameters, and Dynamic Queries in Microsoft Access


	Back to Search Seminar Lessons Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Multitable search

Gary James

3 years ago

Rich what I'm looking for is information on how to perform searches that include date from multiple similar tables where the data is too large to fit in one table.

I'm building a database that could have upwards of 8 - 10 million entries of people. I currently have about 5 million entries, and I'm approaching the 2GB size limit for my accdb file.

If I split up the data into multiple smaller tables, how would I perform a search over those multiple tables in a single pass - for example look for everyone with the last name "Jones". To speed things up I've indexed several fields that are likely to be important in different searches.

Does this seminar cover searches across multiple tables in a single pass?

Attached is what my Table looks like.

	Gary James OP @Reply 3 years ago

	Adam Schwanz @Reply 3 years ago
It sounds like you need to look into SQL server :P. I'm not sure how efficient it would be to union query multiple tables together and then use the normal search on that.

Scott Axton @Reply

3 years ago

To answer your question - Yes the seminar does cover that. You would use a Union Query. Then if you are just looking at the data no problems. However there are issues, which Richard covers, if you have to be able to edit them. Union Queries are not update-able. Fields must match exact with UNION and * all fields

Union Queries:
Access Expert 16
Access Expert 17
Access Search Seminar
Lesson 23. Multi Table Search Form 1
Union Query (TechHelp)

Just to name a few.

	Gary James OP @Reply 3 years ago
Adam & Scott, thanks for the feedback. I'm beginning to think that maybe I should look into moving the backend into SQL Server. Microsoft has a free version of SQL Server called SQL Server Express that I can play around with. I know that Rich covered moving Access tables to SQL Server, but off hand I don't remember where I saw it. That's another nut to crack.

	Kevin Yip @Reply 3 years ago
SQL Server Express has a limit too: 10 GB max database size, which should be enough for your immediate future, but a limit nonetheless that you should keep in mind. When your Access database is close to the 2GB limit, it's time to migrate to SQL Server instead of splitting up the tables the way you describe, which would cause all kinds of issues.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Search Seminar Lessons.

Next Unseen

New Feature: Comment Live View