Hi Theresa,
It sounds like you've fallen into one of Microsoft's carefully laid traps:
Lookup Fields in your tables. I recommend that you get rid of them and
never use them again. They will always come back to bite you!
Lookup Fields surfaced several versions of Access ago. It appears that
someone at MS had an epiphany and their developers have been forced to leave
the "feature" in the product. I don't know if it occurs to anyone but it's
an egregious violation of the rules for relational databases formulated by
E. F. Codd in the 1970s. I guess they figure that new users won't be aware
or that they probably weren't going to put a decent user interface on their
application??
The content of a field must be "atomic". Actually, the representation of
Codd's rules that I've seen were presented just before launching into
normalization.of data in tables but it should also apply to data within the
database, not just within the record, unless there are compelling reasons
for breaking the rules; there sometimes are.
So.... Go back and redo the table designs such that you have just one
"atomic" element in each field. That means that you'll also have to redo
parts of any Forms that had the Lookup Fields placed on them, etc. Then,
when the query in your Report retrieves the data it will be what you want.
Alternatively, you could try writing a query which duplicates the (hidden)
query in the Lookup Field and rewrite the query on which the form is based
to leave out the Lookup Filed and nest the new query to get the data you
want.
Removing the Lookup Fields is the better way to go.
HTH
