DLOOKUP two tables in query

[sockswithsandals]

Have to ask for help, been stuck for too long...



I have two tables tblPersonnel and tblCosts. I'm trying to make a query which would return list of names from tblPersonnel and next to the names would return Cost from tblCosts based on country id. CountryID is pk in tblCosts and fk in tblPersonnel. Cost is numeric value but countryid text string.

This is what I got but it returns an error:

SELECT tblPersonnel.PerID, DLookUp("Cost","tblCosts","CountryID=" & tblPersonnel.CountryID) AS Expr1
FROM tblPersonnel;

Error message:

the expression you entered as a query parameter produced this error: the object doesn't contain the automation object xxxx

How this syntax should go?



EDIT: Yes, got it to work. Correct syntax:

SELECT tblPersonnel.PerID, DLookUp("Cost","tblCosts","[CountryId] ='" & [tblPersonnel].[countryId] & "'") AS Cst
FROM tblCosts INNER JOIN tblPersonnel ON tblCosts.CountryId = tblPersonnel.CountryId;

[jzwp11]

You would not need the DLookup(). You would include both tables in the query with the join for the countryID (I assume that the datatype of the CountryID field in both tables is the same).

SELECT tblPersonnel.PerID,tblCosts.Cost
FROM tblPersonnel INNER JOIN tblCosts ON tblPersonnel.CountryID=tblCosts.CountryID

[sockswithsandals]

Thanks a lot! Don't understand how I missed that one, it's so simple solution but works perfectly. Thanks!

[jzwp11]

You're welcome