DLookUp in query headache! multiple duplicate records returned

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!

Re: DLookUp in query headache! multiple duplicate records returned

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

wrote in message
news:8d5c7356-bcfb-4f95-ac1b-2132cf3e757b@e10g2000prf.google groups.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!

Re: DLookUp in query headache! multiple duplicate records returned

colin-whitehead@supanet.com wrote:
> 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!

It sounds like a mess. Even the description of your problem.

If might understand your problem correctly and then again not. If I do,
I'd open up the query builder and add tblReports and then add 5
tblComments...you'll end up with tblComments, tblComments_1 ...
tblComments_4. Now draw you link lines for the ID to tblComments and
link lines for each numeric field to the associated tblComments_?
tabble. Now dbl-click on each link line and set to "All Recs in
TblReports and matching in tblComments". That way if some recs don't
have a number you don't exclude the record. Now drag the comments field
from each tblComments table.

To make it easier to know which comments field is what, you can make the
column heading make some sense
UPNComments : Comments (for the UPN-Comments link)
EffortComments: Comments (for the Effort-Comments_1 link)
IOW, give your heading name, then a colon :, then the field name in the
field name row of the query builder.

Constant Sorrow
http://www.youtube.com/watch?v=bANZUyVf0nM