Two tables to result in single column with field Name identifier for each record

[Rx_]

Trying to wrap my head around this one to display in a single column.
I know how to do this wiht VBA. But, this output will need to reside on a SQL Server View. So I need a SQL language solution. If it can work in MS Access Query, it won't be too difficult to test then translate to SQL Server.
Pleae see the Attached graphic. - all non essential fields are blocked out.

Customer Table with PK Customer_ID.
There are two tables with FK Customer_ID.
1. Table Lease1 - Has 3 Fields - the form code enforces No Fields -or All Fields. The red * indicate a Required field - These 3 are entered together.
2. Table Lease2 - Has 1 field with 0 to Many records.

Goal:
The Type shows up in a single column.
Each Type shows where the data comes from (Lease Type, Surface Owner, Mineral Owner, or Hz Lease Type)

Challange:
Lease1 table has 3 fields that need to be transformed into a single column.
Lease2 table has 1 field to be appended to the single table.

Then, there is the column that identifies where the data came from based on the column name.

 

[Rx_]

For the table with 3 fields, this is the Union Query.
Basically, create a 3 single query designs. Make the out put field names the same. Then create a new query in SQL view - paste each of them in with the Union statement (remember to remove the " ; " )
This puts all three in a nice column with the source identifier.

Now, How will I join the zero to many view to this? Will a view with the same union work?

SELECT Wells.ID_Wells, "Lease Type" AS [Lease Type Name], Wells_Lease_Type.Lease_Type AS Type
FROM (Wells INNER JOIN Wells_Lease ON Wells.ID_Wells = Wells_Lease.ID_Wells) INNER JOIN Wells_Lease_Type ON Wells_Lease.ID_Lease_Type = Wells_Lease_Type.ID_Lease_Type
Union
SELECT Wells.ID_Wells, "Surface Owner" AS [Lease Type Name], SurfaceOwner.SurfaceOwner AS Type
FROM (Wells INNER JOIN Wells_Lease ON Wells.ID_Wells = Wells_Lease.ID_Wells) INNER JOIN SurfaceOwner ON Wells_Lease.SurfaceOwnerID = SurfaceOwner.SurfaceOwnerID
Union
SELECT Wells.ID_Wells, "Mineral Owner" AS [Lease Type Name], MineralOwner.MineralOwner AS Type
FROM (Wells INNER JOIN Wells_Lease ON Wells.ID_Wells = Wells_Lease.ID_Wells) INNER JOIN MineralOwner ON Wells_Lease.MineralOwnerID = MineralOwner.MineralOwnerID

Union Query Output:
ID_Wells Lease Type NameType
12 Lease Type Federal
12 Mineral Owner Federal
12 Surface Owner Federal
13 Lease Type State
13 Mineral Owner State
13 Surface Owner State
14 Lease Type State
14 Mineral Owner State
14 Surface Owner State
15L ease Type Federal
15 Mineral Owner Federal
15 Surface Owner Federal

 

[plog]

Can you post starting sample data from your tables and then expected results based on that sample data?

 

[Rx_]

Will try to get that after afternoon meeting.
For now, I got the intended results.
The union of the zero to many fit right in once the derived field names matched.
The last query in the Union is the many to one.

Usually, I pull a Union Query off of one table. However, it will still work no matter the relationship or source so long as each output has the same field names.

SELECT Wells.ID_Wells, "Lease Type" AS [Lease Type Name], Wells_Lease_Type.Lease_Type AS Type
FROM (Wells INNER JOIN Wells_Lease ON Wells.ID_Wells = Wells_Lease.ID_Wells) INNER JOIN Wells_Lease_Type ON Wells_Lease.ID_Lease_Type = Wells_Lease_Type.ID_Lease_Type
Union
SELECT Wells.ID_Wells, "Surface Owner" AS [Lease Type Name], SurfaceOwner.SurfaceOwner AS Type
FROM (Wells INNER JOIN Wells_Lease ON Wells.ID_Wells = Wells_Lease.ID_Wells) INNER JOIN SurfaceOwner ON Wells_Lease.SurfaceOwnerID = SurfaceOwner.SurfaceOwnerID
Union
SELECT Wells.ID_Wells, "Mineral Owner" AS [Lease Type Name], MineralOwner.MineralOwner AS Type
FROM (Wells INNER JOIN Wells_Lease ON Wells.ID_Wells = Wells_Lease.ID_Wells) INNER JOIN MineralOwner ON Wells_Lease.MineralOwnerID = MineralOwner.MineralOwnerID
Union
SELECT Wells_Lease_DirHz.ID_Wells, "Dir Hz Lease" AS [Lease Type Name], Wells_Lease_DirHz.HZ_Type AS Type
FROM Wells_Lease_DirHz

I have only a few minutes to between an unscheduled meeting.
The Union Query graphic is uploaded now.
Query 1, 2, 3 are on the same Wells_Lease table - each field had a different lookup table to join to.
Query 4 is the Union with Query 1,2,3 that tested to be accurate.
Then Query 5 is the single table with the zero to many.
It was added to the Query 4 at the bottom.
The key to the Union is to make sure the field names are the same.
Also note that on each of the Lease Type Name field - The source is hard coded.

 

[Galaxiom]

The key to the Union is to make sure the field names are the same.

No. The first section of the query determines the names of the fields. The fields in subsequent sections are added in order regardless the names. They don't even need any names.

BTW Type is a reserved word.

 

[Rx_]

DOH!! Old habits from the VMS OS Query days are hard to break!

And, I am just the type to do that and spend an hour wondering why.
Thanks for pointing that out. I really do know better. But, I forgot... again.

 

[Galaxiom]

Also note that UNION compares all the results from the different sections and removes duplicates.

If that process is not required, use UNION ALL. It can save a lot of time.