CODEWITHSUNDEEP
sqlms-access
a404error
a404error

Reputation: 11

Access 2010 Create column containing a list of fields from multiple columns in other tables

After too much time trying to Google this and returning results to concat or sum the columns, I am going to ask it here. I am hoping that this will be a no-brainer kind of question, and I merely failed to properly use the correct keywords when conducting a Google search.

I have two tables (Motors and Couplings) that contain information about, well motors and couplings. Here is the basic structure of the tables:

Table Name: Motors Columns: Process, Motor Name

Table Name: Couplings Columns: Process, Coupling Name

There are numerous other columns but none of them would be of any relevance as to creating the query. All I want to do is create a query that can retieve all Motor Names and all Coupling Names that are part of a specific Process, obviosuly with a WHERE statement, and put them into a column (most likely a temporary column) called Component. Unfortunately Google tells me to concatenate or sum up the results... which is not what I want to do. Just a listing or a summary. I do apologize, as I know this is an SQL 101 question. Any help is appreciated though.

Upvotes: 1

Views: 1293

Answers (1)

SeanC
SeanC

Reputation: 15923

A union select will get the data from both tables.

SELECT * from Motors Where process="Text"
Union
Select * from Couplings Where process="Text"

while I have used * to get the data in the example, you will have to return the same number of columns in each select, else Access can return some strange results, or just fail.

I suggest you make 2 queries, then copy the SQL from each into another empty query. Replace the ; at the end of the first query with the word UNION. this should create the query you need

Upvotes: 1

Related Questions