SQL code to do a Union of data in MS-Access and keeping relevant information

ms access union all query
access union query make table
ms access union vs union all
sql merge two tables with different fields
how to combine two select queries in sql with different columns
ms access union into table
sql append tables
sql union merge rows

This is my Access table I need to sort.

and I need it to look like this.

I know I could use this SQL code,

SELECT data1
FROM table
UNION
SELECT data2
FROM table
UNION
SELECT data3
FROM table;

to get it to list all the data columns in one column. What do I need to do so that I can retain the information for each piece of data. I'm really new to SQL and haven't used Access this advanced before so I really don't know what the terminology is to even google what I need. Thanks

You could use this. Try read and understand the query:

SELECT item, type, description, data1 AS data
FROM table_name
WHERE Nz(data1, '') <> ''
UNION
SELECT item, type, description, data2 AS data
FROM table_name
WHERE Nz(data2,'') <> ''
UNION
SELECT item, type, description, data3 AS data
FROM table_name
WHERE Nz(data3,'') <> ''
ORDER BY item, type, description, data;

SQL UNION, This lesson is part of a full-length tutorial in using SQL for Data Analysis. Put differently, UNION allows you to write two separate SELECT statements, and to  Learn SQL by doing 50+ interactive coding exercises with free registration. Start now!

Try below

select item, type, description, data1 from [Table] where data1 Is not null
Union
select item, type, description, data2 from [Table] where data2 Is not null
Union
select item, type, description, data3 from [Table] where data3 Is not null

OR

select * from (

select item, type, description, data1 as data from [Table] 
Union
select item, type, description, data2 as data from [Table] 
Union
select item, type, description, data3 as data from [Table] )

where data is not null

Microsoft Access Union Query: Union vs. Union All Syntax, Union Query SQL Example. Using the same SQL statements and combining them with a UNION command: SELECT City, State, Zip FROM Authors WHERE State  Search For Database Sql Here. Search For Database Sql Now!

Here is the code with very slight changes with your code. Simply union the selection of main columns results.

SELECT item,type,discription 
FROM table
UNION
SELECT data1 as data
FROM table
UNION
SELECT data2
FROM table
UNION
SELECT data3
FROM table;

3. Data, Tables, and Database Design, Such well-designed tables are essential to maintaining data integrity and the long-term This is a sure sign that this information belongs in a separate table (​Figure 3-4). THE FIX: Microsoft tried to make Access user-friendly by allowing you to name Create a blank query and paste this code into SQL View; then run it. Search for Database Sql. The Last Information Now! Results Here. Find Database Sql at Ponduweb.com

How to Apply a UNION in MS Access, You can use Union to add together the records from one table/query to The SQL structure to apply a Union in MS Access between two tables  Find Database Sql. Relevant Results on fastquicksearch. Search for Database Sql Here. Visit our Web Now!

The EXCEPT and INTERSECT Operators in SQL Server, The UNION, EXCEPT and INTERSECT operators of SQL enable you to or query results, to determine which information is the same and which isn't. the UNION or UNION ALL operator to combine the relevant columns of the on either of those operators, you can combine data sets whether they come  Syntax. [TABLE] query1 UNION [ALL] [TABLE] query2 [UNION [ALL] [TABLE] queryn [ …. ]] The UNION operation has these parts: A SELECT statement, the name of a stored query, or the name of a stored table preceded by the TABLE keyword.

10 SQL tricks that you didn't think were possible, This article will bring you 10 SQL tricks that many of you might not have You don't care HOW the database will retrieve this information (e.g. should the You define a common table expression that has exactly two UNION ALL subqueries. logic directly in our SQL statements, just as with Microsoft Excel. Create a new Query: Navigate to CREATE tab > Query Design > SQL View. Type the SQL as below. The syntax of UNION is. SELECT column_name (s) FROM table1 UNION SELECT column_name (s) FROM table2; For my case I want to select all columns, so I use *. Save and run the Query.

Comments
  • Thank you. The top example code worked exactly as I needed. I even understand basically how it works too. I'll just keep learning.
  • Union query must have same number of fields from each table