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.