SQL problem- can it be done??

[sparkash]

Is it possible to Merge the 2 queries below in to one querie.


strSQL = "Select * From CALIBRECORDS where InstrumenNumber = " & "'" & Form1.Text2.Text & "'"




strSQLq = "Select Instrumentdescription From Guages where InstrumenNumber = " & "'" & Form3.Text49.Text & "'"

i have 2 fields in 2 diferent tables that i would like to have on one form.

Any help would be appreciated

[brucevde]

Sure, try something like this.

strSQL = "Select C.*, G.Instrumentdescription 
From CALIBRECORDS C Join Guages G On C.InstrumenNumber  = G.InstrumenNumber 
Where C.InstrumenNumber = '" & Form3.Text49.Text & "'"

[maxl]

Is there a link between the 2 tables ?

[sparkash]

Thanks brucevde for your suggestion, However, i cant get your code to work, i get a syntax error.

Also, there is no mention of form3.text2.text in your code.

I am a newbie to databases, so can you please keep any replies simple.

Thanks mate.


maxl,

The link between the 2 tables is the instrument number.

[jxiong]

SELECT
A.column1, A.column2,
B.Instrumentdescription
FROM InstrumenNumber as A,
Guages as B
WHERE
A.InstrumenNumber = B.InstrumenNumber

Replace column1 and column2 with your desired columns from
table InstrumenNumber!

[sparkash]

Thanks for reply, but what do you mean by column1 and column 2?

Am i missing something basic?

Please enlighten me.

Thanks

[maxl]

Originally posted by sparkash
Thanks brucevde for your suggestion, However, i cant get your code to work, i get a syntax error.

Also, there is no mention of form3.text2.text in your code.

I am a newbie to databases, so can you please keep any replies simple.

Thanks mate.


maxl,

The link between the 2 tables is the instrument number.

strSQL = "SELECT C.*,  G.InstrumentDescription FROM CaliberRecords C INNER JOIN Guages G ON C.InstrumenNumber=G.InstrumenNumber WHERE C.Id_InstrumenNumber=" & txtInstrumenNumber.Text

[jxiong]

what I mean is the actual column name from table A. If you
want all of them, just use * and it should be fine.

SELECT
a.*
B.Instrumentdescription
FROM InstrumenNumber as A,
Guages as B
WHERE
A.InstrumenNumber = B.InstrumenNumber

P.S. I am leaving for home but if you can't get it just
let me know by Monday.