Populating Tables with a Query 1

[TCARPENTER]

Can't seem to find what I'm looking for - I could easily have missed it or just plain not searched for the right thing - it's been a while since I used Access...

I think I'm close, but I can't quite seem to get there - but I have two tables, one is basically a lookup table (Table1), the other is the table I'm trying to flesh out (Table2). I thought either a union query or a left/outer join would populate the missing fields but it's obviously not working.

[u][b]Table1:[/b][/u]

[b]Dia	C	H	A	D	F	T	J[/b]	
#0-80	0.004	0.06	0.096	0.06	0.074	0.025	050	
#1-64	0.005	0.073	0.118	0.073	0.087	0.031	0.0625	
#2-56	0.008	0.086	0.14	0.086	0.102	0.038	0.078125	
#3-48	0.008	0.099	0.161	0.099	0.115	0.044	0.078125	
#4-40	0.009	0.112	0.183	0.112	0.13	0.051	0.09375	
#5-40	0.012	0.125	0.205	0.125	0.145	0.057	0.09375	
#6-32	0.013	0.138	0.226	0.138	0.158	0.064	0.109375	
#8-32	0.014	0.164	0.27	0.164	0.188	0.077	0.140625

[u][b]Table2:[/b][/u]

[b]  Descr	       Length	Dia[/b]
#0-80x1	        1	#0-80
#0-80x1 .25	1.25	#0-80
#1-64x1	        1	#1-64
#1-64x1.25	1.25	#1-64
#1-64x1.5	1.5	#1-64
#2-56x1	        1	#2-56
#2-56x1.25	1.25	#2-56
#2-56x1.5	1.5	#2-56
#2-56x1.75	1.75	#2-56
#3-48x1	        1	#3-48
#3-48x1.25	1.25	#3-48
#3-48x1.5	1.5	#3-48
#3-48x1.75	1.75	#3-48
#3-48x2	        2	#3-48
#4-40x1	        1	#4-40
#4-40x1.25	1.25	#4-40
#4-40x1.5	1.5	#4-40
#4-40x1.75	1.75	#4-40
#4-40x2	        2	#4-40
#4-40x2.25	2.25	#4-40
#5-40x1	        1	#5-40
#5-40x1.25	1.25	#5-40
#5-40x1.5	1.5	#5-40
#5-40x1.75	1.75	#5-40
#5-40x2	        2	#5-40
#5-40x2.25	2.25	#5-40
#5-40x2.5	2.5	#5-40
#6-32x1.25	1.25	#6-32
#6-32x1.5	1.5	#6-32
#6-32x1.75	1.75	#6-32

So based on Dia, Table2 (or maybe even a third table) would contain all the information from Table1.

Thanks for any help
Todd

 

[dhookom]

I don't see any information from table1 in table2 other than the Dia field. Where and why do you get values for Descr and Length?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Something like this ?
SELECT * FROM Table2 LEFT JOIN Table1 ON Table2.Dia = Table1.Dia

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TCARPENTER]

Duane,

Right - the Dia field is the common denominator, Table2 is actually much longer than Table1 because of the Length field. The Descr field is just a combination of Dia and Length. The Length field is hard coded of the screw length offerings. The Why is a bit more difficult - have to build this out this way for a SolidWorks design table in Excel.

 

[TCARPENTER]

PHV,

I did that, and I wound up with blanks, I as hoping to populate the blank fields:

Sheet2.Dia	C	H	L	A	D	F	T	J	Descr	Length	Sheet6.Dia
#10-24	0.018	0.19		0.312	0.19	0.218	0.09	0.15625	#10-24x2.5	2.5	#10-24
#10-24	0.018	0.19		0.312	0.19	0.218	0.09	0.15625	#10-24x2.75	2.75	#10-24
#10-24	0.018	0.19		0.312	0.19	0.218	0.09	0.15625	#10-24x3	3	#10-24
#10-24	0.018	0.19		0.312	0.19	0.218	0.09	0.15625	#10-24x3.5	3.5	#10-24
									1/4-20x1.5	1.5	43834
									1/4-20x1.75	1.75	43834
									1/4-20x2	2	43834
									1/4-20x2.25	2.25	43834
									1/4-20x2.5	2.5	43834
									1/4-20x2.75	2.75	43834
									1/4-20x3	3	43834
									1/4-20x3.25	3.25	43834
									1/4-20x3.5	3.5	43834
									1/4-20x4	4	43834
									1/4-20x4.5	4.5	43834
									1/4-20x5	5	43834
									5/16-18x1.75	1.75	43236
									5/16-18x2	2	43236
									5/16-18x2.25	2.25	43236

 

[PHV]

Seems like there is no Sheet2.Dia with 43834 value...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TCARPENTER]

PHV,

They are there - I just shortened the tables - I didn't think anybody wanted to see the whole lot...

 

[PHV]

If all Dia codes are in Table1 then use this:
SELECT * FROM Table2 INNER JOIN Table1 ON Table2.Dia = Table1.Dia

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TCARPENTER]

Sorry - I'm not explaining this well - for arguments sake, I need a 3rd table built containing all the records from Table2 combined with Table1 based on Dia.

Hopefully I'm making sense...

 

[PHV]

Like the result of my last SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TCARPENTER]

No - notice the Length field? For each Length field, Table3 needs to have the information from Table1, and Table2. The trick is Table2 contains only some of the information from Table1 - it needs the rest. In short Table1 contains screw head parameters, and Table2 contains the lengths.

 

[TCARPENTER]

Sorry guys - I'm an idiot. PHV caught the error in his first post - it just made a whooshing sound as it went over my head...

So sorry and thanks for hanging in there with me...

Todd