One Query tapping multiple tables

[yukon]

Hello:

I have 15 tables that have similar but unrelated data. There are technically no relationships I can form between these tables. Each table does have a STATUS field which can have a value of either PRELIMINARY or RELEASED.

I need a single query that can list records from all tables that have STATUS=PRELIMINARY. In other words, one result window with all the preliminary entries in the database regardless of table.

This sounds like it should be simple, but I'm stuck. Thanks for any suggestions.

 

[scriverb]

This sounds like a UNION query. A UNION query allows you to build a new recordset from multiple locations where there is no clear releationship between the tables but there is a need to select records from all the tables and display them as if coming from one. The main requirement is that each SELECT statement for each table must have the same number of columns and they must be named the same.

Follow these instructions exactly because building a UNION query is a little different.

I find the easiest way for beginners is to make a simple query from one of the tables and then use that SQL code to paste into a UNION query to start the process.

Build a query in design mode of any one of your tables. This would include the data you want in your columns as well as the selection criteria:
Select Field1, Field2, Field3, Status From tblYourTable1 WHERE tblYourTable1.STATUS = "PRELIMINARY"

Now copy your SQL from the SQL window and then delete all of the SQL from the window. This will start you off fresh in this query. Go back to the Design Window. From the Query menu select SQL Specific and then select UNION. This will set this query up as a UNION QUERY. The SQL window will be blank. Paste into it the SQL that you copied previously. Place your cursor at the end and press Enter/Return. Type in UNION and a space and another ENTER/RETURN. Now paste in the SQL again. This code you will have to modify to reflect the second table.
EXAMPLE::
Select Field1, Field2, Field3, Status From tblYourTable1 WHERE tblYourTable1.STATUS = "PRELIMINARY"
UNION
Select Field1, Field2, Field3, Status From tblYourTable2 WHERE tblYourTable1.STATUS = "PRELIMINARY"
UNION
Select Field1, Field2, Field3, Status From tblYourTable3 WHERE tblYourTable1.STATUS = "PRELIMINARY"
UNION . . .
do this through all 12 tables. Save the query. This query will reformat your tables data into one table with data mixed. You can have one of the fields be the name of the table where the data is from. Just name the field Table: "tblYourTable1" Each SELECT would have to have a column like this with their specific table identified.

Get back with me if you have some questions. Union queries are a little confusing but they are really powerful in just these situations. Bob Scriver

 

[JeremyNYC]

I only glanced at the previous reply, but it seems like a good way of going about making a union query. I have a couple of concerns, though.

One is that, if the data are all unrelated, the fields in the tables will not be the same, and in a Union query you have to have the same number of fields coming from each table. Even if you have the same number of fields from each table, if they hold different types of data, your query is going to be very hard to understand.

The other is that, if each of the tables actually has the same fields, then you would be much better off combining all of the tables into one table, adding a single field to indicate what type of data they are. One of the reasons you'll be much better off is that then you won't have to deal with union queries, which are, as the other poster noted, a little cumbersome.

Hope this helps.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing and building Access databases since 1995.

 

[scriverb]

JennyNYC: Hi, you are correct in your assessment of the situation with his 15 tables. Those were some of my concerns but I was hoping/assuming that even though they were not releated in anyway as "YUKON" stated, they were in fact similar. My assumption was that because they had a common field with a common value for selection that they were probably 15 tables from 15 different institutions such as prisons or something like that where the data had no releationship to each other but the data that he wanted to display was in fact the same for the columns.

If they are not he can still do the same with the UNION query by creating a column for each type of data to be displayed. If a particular table doesn't use that column just state it in the Select and put a Null or empty string value it it. This would be similar to your suggestions with the common table but it would be dynamic and he wouldn't have to restructure his existing database. What I mean is fields 1-5 would be for tables 1,3,5,8, and 15 and fields 6-12 would be for the other tables. It would all come out in one recordset with some data fields empty and some field where they apply to that particular table.

Come back with more ideas please. Always looking for new ways to solve the problem. Bob Scriver

 

[yukon]

Thank you, Bob and Jeremy, for your posts.

Bob, your example worked like a charm - I have exactly what I want. The only small change was that you did not increment "WHERE tblYourTable1.STATUS" in your example above, but I saw it ahead of time.

FYI - the data in these tables represent different classes of electronic components. An external application ties to these tables and displays data in different categories according to their tables.

Thanks again,
Yukon

 

[scriverb]

Yukon, I am glad that I could help. Sorry about the cut and paste error. Great that you spotted it. As for Jenny/Jeremy(NYC): Sorry about the name oversite. Maybe it was just wishful thinking or late night blurries.

See you in the next question. Bob Scriver

 

[JeremyNYC]

LOL! Hadn't noticed when I read it. =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing and building Access databases since 1995.