Retrive data from two table

[chrislx]

Hi,

I want to retrieve data from two tables, tbl-1 and tbl-2. I wonder how to make it work using recordset. Should I have 2 recordsets opened? How to set the sqlCriteria?

Here is the sql selection:

SELECT tbl-1.field-1
FROM tbl-1
WHERE tbl-1.field-1 Not In (Select tbl-2.field-1 from tbl-2) And (tbl-1.field-1)<>(value read from form)
ORDER BY tbl-1.field-1

Thanks for helping!
chrislx

 

[randy700]

How about something like this....

Dim db as Database
Dim rs as Recordset
Dim strSQL as String
strSQL = "SELECT tbl-1.field-1 FROM tbl-1 WHERE tbl1.field-1 Not In (Select tbl-2.field-1 FROM tbl-2) And (tbl-1.field-1) <> " & forms!formName!controlName & " ORDER BY tbl-1.field-1"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)


Randy

 

[SiJP]

Do the two tables contain the same data, or are they based on a primary/forign relationship?

e.g.

1) Do both tables contain one field called Name, just with different data?

If so, use a UNION SELECT query:

SELECT *
FROM tbl1

UNION SELECT *
FROM tbl2

2) If your tables are set up so tbl1 contains header details (e.g. one record per person) and a second table for their drink preferences (e.g. stella, merlot, vodka etc)

then you will need to create a select query kind of like...

SELECT *
FROM tbl1 INNERJOIN myID ON tbl1.myID = tbl2.myID


HTH's

 

[SiJP]

Looks like I misread the question.. scratch the above

 

[chrislx]

Thank you for your replys.

The tbl-2 is the subset of tbl-1 and both including field-1 which is the primary key of tbl-1.
I want to select all of the records which is in tbl-1 and not in tbl-2 and insert them to tbl-3.

I have trouble to read the data in recordset by using Do Until loop. for example,

Dim db as Database
Dim rs as Recordset
Dim strSQL as String
strSQL = "SELECT tbl-1.field-1 FROM tbl-1 WHERE tbl1.field-1 Not In (Select tbl-2.field-1 FROM tbl-2) And (tbl-1.field-1) <> " & forms!formName!controlName & " ORDER BY tbl-1.field-1"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

Do Until rs.EOF
MsgBox rs.field-1
(do sql insert into tbl-3 after I got a record from recordset)
Loop

the rs.field-1 value I got is the data from tbl-2. I want to the data in tbl-1 and not in tbl-2

what is wrong?

Thanks again
chrislx

 

[jrbarnett]

chrislx

With one table storing a subset of the data of another table this is not considered good practise, it runs against the database design principles known as Normalisation.

If you only want to use specific records from one table, then only select those in queries etc.
If you need more information on good database design techniques and normalisation, can I draw your attention to the Fundamentals of DAtabase Design document in the programmers section of the site

http://www.abcdataworks.com/

which explains it in quite easy terms. This site itself is run by Tek-Tips member JeremyNYC.

John

 

[PHV]

And what about this ?
"SELECT tbl-1.field-1 FROM tbl-1 LEFT JOIN tbl-2 ON tbl-1.field-1=tbl-2.field-1" & _
" WHERE tbl-2.field-1 Is Null And tbl-1.field-1<>" & forms!formName!controlName & _
" ORDER BY tbl-1.field-1"

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884