Member Avatar for spowel4

I've got two ms access tables within one .mdb file, the tables are mrproduction & fglotnum. From mrproduction I need records from fields mrStkNum, mrLotNum, and mrQty. From fglotnum I need records from field mrCost. The common field between the two tables is lotNum (i.e. mrproduction.mrLotNum & fglotnum.mrLotNum).

I guess an inner join is the way to retrieve what I need from the two tables, unless someone has another way to do this that they could tell me. I've been trying to do an inner join but I get error messages no matter what I try, basically saying no matches found.
The relevant code section:

strSql = "SELECT mrproduction.mrStkNum, mrproduction.mrLotNum, mrproduction.mrQty, fglotnum.mrCost FROM mrproduction, fglotnum"
 strSql = strSql & " WHERE mrproduction.mrLotNum = fglotnum.mrLotNum"
 rs.Open strSql, cn, adOpenStatic, adLockOptimistic, adCmdText
 rs.MoveFirst

This is the first time I've tried doing an inner join and I've not had any luck so far in figuring out where I've gone wrong.

  • 2 Contributors
  • 1 Reply
  • 166 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by aktharshaik
Member Avatar for aktharshaik

Try this one.

strSql = "SELECT mrproduction.mrStkNum, mrproduction.mrLotNum, mrproduction.mrQty, fglotnum.mrCost FROM mrproduction INNER JOIN fglotnum ON mrproduction.mrLotNum=fglotnum.mrLotNum"
      rs.Open strSql, cn, adOpenStatic, adLockOptimistic, adCmdText
      If rs.BOF = False Then
           rs.MoveFirst
      End If

also can add sorting order clause

strSql = "SELECT mrproduction.mrStkNum, mrproduction.mrLotNum, mrproduction.mrQty, fglotnum.mrCost FROM mrproduction INNER JOIN fglotnum ON mrproduction.mrLotNum=fglotnum.mrLotNum ORDER BY mrproduction.mrStkNum, mrproduction.mrLotNum"
      rs.Open strSql, cn, adOpenStatic, adLockOptimistic, adCmdText
      If rs.BOF = False Then
           rs.MoveFirst
      End If
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.