DLookUp in query headache! multiple duplicate records returned

[colin-whitehead]

I have 2 tables
tblReports primary key UPN, plus numeric fields Effort, Attain, etc

tblComments numeric primary key ID & textfield Text

In the Query I select each record from tblReports using UPN and for
each of the numeric fields use DLookup to look up the Text in
tblComments.

the expression DLookUp("Text","tblComments","ID=" & tblReports!Effort)
retrives the correct text but when I use the query it produces
multiple duplicate records - (the number of duplicate records equal
the number of records in the tblComments table)

I cannot see how this is happening. Can anyone help!

[Scott]

Can't you use a join query and avoid the dlookup?

<co*************@supanet.comwrote in message
news:8d**********************************@e10g2000 prf.googlegroups.com...

>I have 2 tables
tblReports primary key UPN, plus numeric fields Effort, Attain, etc

tblComments numeric primary key ID & textfield Text

In the Query I select each record from tblReports using UPN and for
each of the numeric fields use DLookup to look up the Text in
tblComments.

the expression DLookUp("Text","tblComments","ID=" & tblReports!Effort)
retrives the correct text but when I use the query it produces
multiple duplicate records - (the number of duplicate records equal
the number of records in the tblComments table)

I cannot see how this is happening. Can anyone help!