Query multiple tables (1 Viewer)

[Denise2020]

I have a database with multiple tables I will call ExtAssist, AmData, and Library. The ExtAssist table is used to document "incidents" where agencies call us for help about an object. The AmData table contains the objects list that we have in our file system. The Library table is a database of all media we have in our library (books, cds, etc.).

The tables have no direct relationships to each other. The ExtAssist incidents will refer to an object which may or may not be something in our AmData or Library tables (ie we may have information about it in one of those tables).

I currently have three forms, one for each table, with a string search text field where I can search, for example, "m/25" and any objects with that in the name will come up if it is mentioned in each of the tables. What I would LOVE is a fourth form with a simple text search (browser-like) where users could search "m/25" and would get a list of all the posts listed in EITHER of the three tables that mension that search term. Is this asking too much? I have tried to search how to do this but am afraid I am not even using the correct terminology to try to find an answer.

To clarify, I do not want to join data, I just want a list of results from any and all of the tables where the search term is mentioned, all the books, all the incidents, all the files. Is this possible? Thanks in advance!

 

[plog]

You probably want a UNION:

SQL UNION Operator

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

It's a special type of query that lets you just add the results of queries together. Check out the link and give it a shot.

 

[The_Doc_Man]

Look into UNION queries. Understand that you might have to use your imagination more than a little bit if you want anything other than the name of something to come along in the query. You can search a UNION query easily enough from a single form.

 

[Pat Hartman]

The union query will be comprised of three select queries, one for each table. Select the column you want to search, the ID of the table, and create a "calculated" column so you can identify which table the hit was found in:

Select SomeField as SearchField, ThePKID as PKField, "Table 1" as FoundIn From tbl1
Union Select SomeOtherField, SomeOtherPKID, "Table 2" as FoundIn From tbl2
Union Select SomeThirdField, SomeThirdPKID, "Table 3" as FoundIn From tbl3;

Assigning an Alias in the First select will dictate the column names for the returned set of data.

 

[Denise2020]

Please excuse my ignorance, I am trying to wrap my head around this as someone untrained in VBA. When I tried to create a query with multiple tables before posting, it seemed to "blend" all data together in a horrid Philadelphia Experiment sort of way that actually made a list of results so long it crashed Access and I had to kill it with ctrl+alt+del. That is to say, if one table had 8000 items, another table had 100 and the third had 500, it multiplied them by melding them into each other into an apparently crashable number of results.

I obviously did something wrong because if I search, for example, M/52, I would like to see only the results where m/52 is mentioned in each table, and not to join them together into multiples of each result. That made no sense at all, I am sorry. How about this, if there is 1 mention of m/52 in Table 1, 1 mension in Table 2, and 1 mention in Table 3, I would like to get 3 results back. To complicate matters though, the name m/52 could be mentioned in three separate fields on each Table. So searching 9 fields from 3 tables for that one term would bring me back 3 results. Is a UNION query still what you are both recommending? I am reading about unions now, but definitely need some clarity. Thank you so much!

 

[GaP42]

Denise2020 - A union query will support what you are after. It is written in SQL using the Query editor - the special "Union" query type.
Pat Hartman has given you an outline of the union query (#4) structure.
To start you could prepare a set of select queries (using the query Design panel) - for each table and field you wish to interrogate. You mention 9 fields across the 3 tables need to be checked. Each of these 9 queries must have the same layout with consistent data types. Get each working separately to show a list of rows containing the data you need, each with the same layout.
Then write the union SQL that combines each result. There is no join made between the queries.
Make sure you include in your select query a column which says where the data came from - the source tablename and the source fieldname - so any search result you get can be traced to the source - to allow you to confirm it is working - you can hide this later

 

[Galaxiom]

Assigning an Alias in the First select will dictate the column names for the returned set of data.

Hence no point putting aliases on the fields in the subsequent subqueries.

Also note that the Union keyword will automatically apply a Distinct on the records so there are no repeats. In this situation there clearly can be no duplicates so UNION ALL should be used to avoid the waste of time checking for duplicates.

 

[The_Doc_Man]

Please excuse my ignorance, I am trying to wrap my head around this as someone untrained in VBA. When I tried to create a query with multiple tables before posting, it seemed to "blend" all data together in a horrid Philadelphia Experiment sort of way that actually made a list of results so long it crashed Access and I had to kill it with ctrl+alt+del. That is to say, if one table had 8000 items, another table had 100 and the third had 500, it multiplied them by melding them into each other into an apparently crashable number of results.

I obviously did something wrong because if I search, for example, M/52, I would like to see only the results where m/52 is mentioned in each table, and not to join them together into multiples of each result. That made no sense at all, I am sorry. How about this, if there is 1 mention of m/52 in Table 1, 1 mension in Table 2, and 1 mention in Table 3, I would like to get 3 results back. To complicate matters though, the name m/52 could be mentioned in three separate fields on each Table. So searching 9 fields from 3 tables for that one term would bring me back 3 results. Is a UNION query still what you are both recommending? I am reading about unions now, but definitely need some clarity. Thank you so much!

From your description, you may well have done something called a "Cartesian JOIN" - which was the wrong approach anyway, as you don't want a JOIN to put the tables together. A JOIN through three tables, if done without appropriate syntax, essentially does a combination / permutation operation where it takes each record from each table and combines those records with each OTHER record in the other tables. Basically that has the result of MULTIPLYING the tables to give you COMBINATIONS of records. From your numbers, you would get 8000 x 500 x 100 records, or 400,000,000 records. I admit I'm guessing, but you MIGHT have tried something like

SELECT T1.FLD1, T1.FLD2, T1.FLD3, T2.FLD4, T3.FLD5 FROM TABLE1 T1, TABLE2 T2, TABLE3.T3 ;

The above (with three tables in the FROM clause and no explicit JOIN syntax to constrain them) is an example of a Cartesian JOIN - which would do exactly the type of system overload that you described earlier. Since, as you say, the tables don't have a relationship to each other, you cannot JOIN them this way. You'll give Access a hernia if you try. Even TWO tables would have the multiplication effect.

The UNION syntax also puts tables together, but not side-by-side. Rather it does it end-to-end, ONE TABLE AT A TIME! No combinations involved. That would give you 8600 records to search. THAT should be fairly fast. You exercise it by using a second query that instead of looking at the tables, looks at the UNION query as its data source. A query of a query is PERFECTLY legal and in fact quite common.

Here is the only trick: You say you have one table with 3 fields to be searched. Unless the other two tables ALSO have three fields to be searched, your UNION query becomes unbalanced. You might need more than three UNIONs.

Your multi-field unbalanced UNION just might look like this:

SELECT "TBL1" As TName, "FLD1" As FName, FLD1 As FValue FROM TBL1 UNION ALL
SELECT "TBL1", "FLD2", FLD2 FROM TBL1 UNION ALL
SELECT "TBL1", "FLD3", FLD3 FROM TBL1 UNION ALL
SELECT "TBL2", "FLD4", FLD4 FROM TBL2 UNION ALL
SELECT "TBL3", "FLD5", FLD5 FROM TBL3 ;

Store that query with a name. For this discussion, I'll use MyUnionQuery. You only need the "AS" clauses in the first SELECT because the other UNIONed clauses will use the names provided for the first such clause. You can then search for your search string.

SELECT Tname, FName, FValue FROM MyUnionQuery WHERE FValue = your search string ;

How you convey the search string to the second query is up to you, but that can be the subject of further discussion later. Far more important is to read up on what a UNION query actually does. It probably wouldn't hurt to look up "Cartesian JOIN" as well just so you'll know why you don't want to do that again.

 

[mike60smart]

Please excuse my ignorance, I am trying to wrap my head around this as someone untrained in VBA. When I tried to create a query with multiple tables before posting, it seemed to "blend" all data together in a horrid Philadelphia Experiment sort of way that actually made a list of results so long it crashed Access and I had to kill it with ctrl+alt+del. That is to say, if one table had 8000 items, another table had 100 and the third had 500, it multiplied them by melding them into each other into an apparently crashable number of results.

I obviously did something wrong because if I search, for example, M/52, I would like to see only the results where m/52 is mentioned in each table, and not to join them together into multiples of each result. That made no sense at all, I am sorry. How about this, if there is 1 mention of m/52 in Table 1, 1 mension in Table 2, and 1 mention in Table 3, I would like to get 3 results back. To complicate matters though, the name m/52 could be mentioned in three separate fields on each Table. So searching 9 fields from 3 tables for that one term would bring me back 3 results. Is a UNION query still what you are both recommending? I am reading about unions now, but definitely need some clarity. Thank you so much!

Are you able to upload a zipped copy of the database with some sample data?