Merge two tables columns

[brandon20]

Hi,

I am trying to merge the data from two columns together in access.

I have one table with oil wells and their well tops.
I have a bunch of oil wells, each of these oil wells has layers and the depths associated with them.

I have a second table with the oil wells and their final depths.


I need to combine the oil well final depth in the same column as the well layers.

How would I do this?
With a union query?

Thanks for any help

Example tables

Table1=WellLayerTable

Well	Layer	Depth
Well_1	A	100
Well_1	B	200
Well_1	C	300
Well_1	D	400
Well_1	E	500
Well_1	F	600
Well_1	G	700
Well_1	H	800
Well_1	I	900
Well_1	J	1000
Well_1	K	1100
Well_2	A	500
Well_2	B	600
Well_2	C	700
Well_2	D	800

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

Table2=WellTDTable

Well Layer Depth
Well_1 FinalDepth 1500
Well_2 FinalDepth 2000

 

[Micron]

by combine, you could mean concatenate or you could mean sum. I can't make sense out of your examples either. Assuming you mean sum, I get 2600 for well 2 and I don't get 1500 for 1 either, so I probably can't help much beyond saying you might need an aggregate query. To create that, you 'turn on' the Totals option on the Ribbon in query design.

 

[PichaiTC]

Create a query with the below statement

(
SELECT Well, Layer, Depth
FROM WellLayerTable
)
Union All
(
SELECT Well, Layer, Depth
FROM WellTDTable
)

 

[brandon20]

I should have included a "what I want" table at the bottom to better describe what I was looking for.

PichaiTC Thanks! this was exactly what I was looking for. I knew it was a union query I was looking for, just wasnt sure how to write the sql.

Thanks again!!