We help IT Professionals succeed at work.

searching multiple tables in a single query.

pma111
pma111 asked
on
I have an access DB made up of around 30 tables, all with the same fields, and I need to search a specific field on all tables (ideally in one query) for a certain value. In this case I specify the search term, but it would also be useful to search the field for another tables worth of search terms, so some guidance how to do this for both scenarios would be useful.
Comment
Watch Question

Software & Systems Engineer
Distinguished Expert 2019
Commented:
I would iterate the TableDefs collection in your Application...set each time a Recordset object to the table...iterate the Records of the Recordset and gather the results.
ste5anSenior Developer
Commented:
around 30 tables, all with the same fields
This is in the best case a denormalized data model and in the worst case a wrong data model.

Depending on what it is:

1) Create a UNION query to combine the 30 tables to one view. Run your search on this view.
2) Create 30 search queries and UNION the result.
3) Normalized your data model, so that you only have one table and search this table.

1+2) are equivalent, when you can use UNION ALL for some reasons based on your requirements. The UNION (1) or the search queries (2) can be created manually or automatically querying the tables/query definitions.
I would look into 3) even when this means to adjust ETL processes and some masks. But that's how relational databases works the best and that is what distinguish them from Excel.

Author

Commented:
could you give a pointer on this please:

Create a UNION query to combine the 30 tables to one view.

what would the syntax be? Its basically a data import from a 3rd party source and not a true relational database.
John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:
30 tables UNION... :)
If you want
SELECT field1,...fieldn From Table1
UNION
SELECT field1,...fieldn From Table2
...............
SELECT field1,...fieldn From Table30

Open in new window

I wouldn't go that route...too much room for error.

Author

Commented:
Hmm, the union query works great to get all the data into a single query to then search on but when I want to do the match to another table of 'search terms' - when I try to join it to my table of search terms (the field I want to join on is a number format), and I believe it was a number format in every table in the union join, but when I try a simple:

SELECT UnionedData.*
FROM UnionedData INNER JOIN _Phonedata ON UnionedData.[Number/Description] = [_Phonedata].PhoneNumberNoZeros;

it returns 'type mismatch in expression', any idea why this would be or probable causes?
John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:
it means that you are joing apples to oranges  or
Numbers to Strings...check your fields datatypes...

Author

Commented:
it is a number datatype though in every table I used to create my UnionedData query, and its also a number datatype in the search table (_Phonedata).

Author

Commented:
for whatever reason something was sending it off but changed both to datatype text and it joined fine.
Ryan ChongSoftware Team Lead

Commented:
it is a number datatype though in every table I used to create my UnionedData query, and its also a number datatype in the search table (_Phonedata).

if both fields in these 2 tables are identical with same data type, then you will not get the type mismatch in expression error.

can you make sure these 2 fields are in same data type?

* UnionedData.[Number/Description
* [_Phonedata].PhoneNumberNoZeros
John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:
Probably in the import - linking process Access misinterepreted the datatype of some field...it looks numeric...it feels numeric but is text.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

You didn't happen to use the NZ( ) function on that field in either of your queries, did you?  NZ() will always return a string when used in a query.