Limit Listbox Selection

[MPEcho]

Greetings all,

I have tables project and people

PROJECT has fields |projID|Detail1|Detail2| . . . | ManagerID |
PEOPLE has |PersID|ProjID|Name| . . .

Not great design, having to hard code people into each project. But it's what I have. (Fortunately not many overlaps)

On the Form-Project, where the project details live, there is a listbox to select Manager, entering ManagerID from the PersID of people.

Problem is that the form is showing the entire list of people, not just those associated with the project?

[Villeroy]

What you describe is a many-to-many relation where peoples belong to projects and vice versa.
viewtopic.php?f=100&t=40444

[MPEcho]

What you describe is a many-to-many relation where peoples belong to projects and vice versa.
viewtopic.php?f=100&t=40444

Yes, I agree it should be. But I rushed this database into production knowing even less than I do now. So Project to People is one to many, with project ID becoming a part of the person record. I want to use a listbox to fill the Manager ID, but limit the possible selection to those persons with the project ID of the currently selected project record.

So far, I have this in the form:

Code: Select all

SELECT "PEOPLE"."Last" || ', ' || "First" || COALESCE ( ( ' ' || "Middle" ), '' ) AS "Name", "PEOPLE"."pers-id" FROM "PEOPLE", "Base-info" WHERE "PEOPLE"."PROJ-id" = "Base-info"."PROJ_id"

But that does not limit the selections.

Thanks for taking a look.

[Villeroy]

PEOPLE should store unique persons with unique First-Last-Middle names, otherwise the list box is useless if you can not distinguish each individual.
You can enforce uniqueness with an additional unique index on the 3 name fields.
Why do you include the Base-info table? This makes no sense and I think it is the reason why you have the duplicates.

This is how an ordinary list box looks like:

Code: Select all

SELECT "PEOPLE"."Last" || ', ' || "First" || COALESCE ( ( ' ' || "Middle" ), '' ) AS "Name", "PEOPLE"."pers-id" 
FROM "PEOPLE"
ORDER BY "Name"

It selects 2 fields from another table. Each row represents one distinct item (person in this case). The second field is the primary key which is written into a foreign key field of the form's record set, the first field should be a most descriptive and unique text. The second field makes the selected item distinguishable for the database engine, the first field makes it distinguishable for humans.

[UnklDonald418]

a listbox to select Manager, entering ManagerID from the PersID of people.
Problem is that the form is showing the entire list of people, not just those associated with the project?

In order to limit the list box selection list to only those associated with the project you could use a filter table to store the selected "PROJ-id".
Then your query should work if you change all the references to "Base-info" to “FilterTable”.