SQL to crosstab lookup

[presuming_ed]

Anyone fancy helping an old Mainframe dinosaur as he tiptoes his way through access?

My problem is this:

I have two tables, the first contains username (field1) and a narrative (field 2). My second table contains username (field 1 which relates to username on table 1) and email address.

I'm trying to find a way to read each record from table one and get the corresponding email address from table 2 using the username. I've tried all sorts of things including using a recordset with a SELECT SQL statement, but can't seem to get the desired results.

I know I'm being very vague, but I'm totally new to Access. I've looked as countless threads on this site and am still none the wiser. Any help whatsoever would really be appreciated.

Cheers.

 

[Mark Wild]

Are you trying to build the query in code?

Have you succeeded in joining the two tables yet?

 

[presuming_ed]

Yes I've joined the two tables, and here's the code I'm using to get the email address:

Private Sub Form_Open(Cancel As Integer)

Dim rst As Recordset
Dim rst2 As Recordset
Dim db As Database
Dim str_first As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("data")
Set rst2 = db.OpenRecordset("lookup")

str_first = ""
With rst
If str_first = "" Then
.MoveFirst
Else
.MoveNext
End If

DoCmd.RunSQL ("SELECT rst2.email_address from rst2 " _
& "WHERE rst2.name = rst.name;")

End With


SendNotesMail rst2.email_address, "Body Message", "Subject"

End Sub

 

[Mark Wild]

As far as I'm aware, you have to specify the join type between the tables.

I would have used only one recordset.

strSQL="SELECT Name, Email_Address " _
& "FROM Table1 INNER JOIN Table2 ON " _
& "Table1.Name= Table2.Name;"

set rst=db.openrecordset(strSQL,dbsnapshot)

I've assumed your two recordsets were tables.

HTH,

M