Merging Columns in a Query

[bwlytkr]

I have multiple tables that have columns with the same header and data type that I would like to merge into one column when I build and run my query. What is the best way to do that. I know very little about writing code so, if there is a way to do this, please send me an example of what I would put in SQL and where in the SQL I would put it. Below is an example of the columns I would like to merge. There 5 tables with this info. Thanks in advance for your help with this. John

SourceOne Deadline Date NTG Days Aged Bucket, Days Aged

 

[Joe4]

Can you actually show us some sample data and expected output?
Just seeing column headings of different data types is not that helpful.

 

[welshgasman]

Google UNION query

 

[bwlytkr]

These same columns are on 5 different excel tables. I want to combine the data for each of these columns into one column in my access query. Another words, I only want 1 SourceOne Deadline Date Column, 1 DaysAged column and 1 NTG Days Aged Bucket Column with all the data from each of respective table columns in them. Hope this clarifies it.

SOURCEONE DEADLINE DATE	DaysAged	NTG DAYS Age Bucket
06/11/23	24	G- < 30 DAYS OLD
06/11/23	24	G- < 30 DAYS OLD
06/11/23	24	G- < 30 DAYS OLD
07/02/23	5	G- < 30 DAYS OLD
07/02/23	5	G- < 30 DAYS OLD

 

[Joe4]

OK, so it sounds like you are saying that you have 5 tables, all with the same fields, and you want to combine it into one query. Is that correct?
If so, then a UNION query is the way to go, as Welshgasman mentioned.

Just note that while you can query and report off on Union queries, the data in Union queries is NOT editable.

 

[bwlytkr]

This is a copy of the union clause I tried but I keep getting an incomplete Query Clause error. I tried deleteing the : at the end of the statement too and the same error comes up.

SELECT [SO AL Survey Master].[SOURCEONE DEADLINE DATE], [SO FL Survey Master].[SOURCEONE DEADLINE DATE], [SO GA Survey Master].[SourceOne Deadline Date]
FROM [SO AL Survey Master], [SO FL Survey Master], [SO GA Survey Master];
UNION

 

[Joe4]

You basically have complete query statements combined with the word UNION, i.e.

SELECT Field1, Field2, Field3, Field4, Field5
FROM Table1
UNION
SELECT Field1, Field2, Field3, Field4, Field5
FROM Table2
UNION
SELECT Field1, Field2, Field3, Field4, Field5
FROM Table3
UNION
SELECT Field1, Field2, Field3, Field4, Field5
FROM Table4
UNION
SELECT Field1, Field2, Field3, Field4, Field5
FROM Table5

 

[bwlytkr]

I figured out the issue and got it working. However, it is not going to work for what I need. Good to know though. Thanks for the help. I have another question. Is there a query formula to populate a cell in a column if not blank. For ex. I have 3 columns named DaysAged from 3 different tables and I need it to populate the cell that isn't blank.

Field	DaysAged	DaysAged	DaysAged	Days Aged Formula
Table	SO FL Survey Master	SO AL Survey Master	SO GA Survey Master
	5​
		4​
			3​

 

[Joe4]

Do a Google Search on "Update Queries in Microsoft Access".

Note that if your query contains multiple tables, it must be an editable query in order to make updates.
Here is a write-up on what kinds of queries are updateable and which are not.

This Recordset Is Not Updateable. Why?

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com