Lookup "many records" error

[BenFranske]

I have a lookup field on a subform but when I try to use it to make a selection instead of the code displaying I get a "many records" message. I believe this is because the form to lookup includes a JOIN which could result in multiple records for the same ID. However, to prevent just this problem I have limited the number of records on the form to lookup by using a GROUP BY statement in my SELECT on the form to lookup. If I manually edit the table and add the records the Lookup code field in the subform actually shows the correct value, I am just unable to set or update it through the form UI.

The form to lookup is using this SQL:
SELECT peopleID, fulltext_name FROM (
SELECT peopleID, people_name.fulltext_name FROM people
JOIN people_name on people_name.person_id = people.peopleID
ORDER BY people_name.date_effective DESC, people_name.peopleNameID DESC) AS a
GROUP BY peopleID

The Lookup ID field is peopleID and code field is fulltext_name. Given the "GROUP BY people id" statement there should never be multiple records for the same peopleID.

Any ideas on how to fix this?

[admin]

Ben,

Create a View that does the grouping and use that.

Steven

[BenFranske]

Interesting thought, I'll give that a try and see how it goes. Thanks!

[admin]

.