query only one list element in multiple lookup field (with multiselect listbox) (1 Viewer)

tucoxn · Dec 8, 2017

I have a database with two related tables that I'm working to query. The tables are "Alumni" and "SeminarStaffers".

The "Alumni" table has a multiselect listbox that allows input of multiple staffers from the "SeminarStaffers" table.

The "SeminarStaffers" table looks like this:

Code:

ID   StafferName
1    Carl
2    Natalia
3    Daniel
4    Bob
5    Richard

The "Alumni" table looks a little like this:

Code:

ID   Last         SeminarName    Staffers2
1    Johnson      Teachers1      Carl, Natalia
2    Paulson      Teachers1      Carl, Natalia
3    Robinson     Teachers1      Carl, Natalia
4    Wolf         Media1         Daniel, Natalia
5    Scott        Media1         Natalia, Daniel
6    Roberts      Teachers2      Natalia
7    Street       Teachers2      Natalia
8    Duzan        Sports1        Bob
9    Williams     Sports1        Bob
10   Lord         Media2         Richard, Carl
11   Elder        Media2         Richard, Carl

I would like to write a query that only results in the following:

Code:

Alumni.Last        Alumni.SeminarName   Alumni.Staffers2
Roberts            Teachers2            Natalia
Street             Teachers2            Natalia

This SQL code that I tried is not working. I think it's because "Staffers2" is a multiple lookup field (with multiselect listbox). Here's the code I tried:

Code:

SELECT Alumni.Last, Alumni.SeminarName, Alumni.Staffers2
FROM SeminarStaffers INNER JOIN Alumni ON SeminarStaffers.ID = Alumni.Staffers2.Value
WHERE (((SeminarStaffers.StafferName)="Natalia"));

Instead of what I want, the above SQL results in:

Code:

Alumni.Last        Alumni.SeminarName   Alumni.Staffers2
Johnson            Teachers1            Carl, Natalia
Paulson            Teachers1            Carl, Natalia
Robinson           Teachers1            Carl, Natalia
Wolf               Media1               Daniel, Natalia
Scott              Media1               Natalia, Daniel
Roberts            Teachers2            Natalia
Street             Teachers2            Natalia

I appreciate any help you can provide.

Ranman256 · Dec 8, 2017

your table is not designed correctly. Its not normalized.
Alumni.Staffers2 field should only contain 1 name in the field
if you want multiple names for this 1 alumnus, then Staffers2 field should be in a separate child table.

tAlumniStaffers table:
AlumniID, Staffer
123, Carl
123, Natalia

now you can query any individual staffer. Its an easy fix with a make table query.

tucoxn · Dec 8, 2017

Hi Ranman256,

So it's not possible to do this using the current database structure?

I was thinking it might be possible to use a query that includes counting the length of the lists in Alumni.Staffers2 and only include lists of length 1 in the query result.

Thanks!

Mark_ · Dec 8, 2017

I'd also look at your naming conventions.

If you have a field called "Staffers2", one would expect there to be a "Staffers1". This would not be normalized data.

Much as Ranman posted, you'd have a child table to link Alumni to Staff.

Personally I'd stay away from multiselects. They cause many issues and are not supported in most SQL back ends. You've encountered ONE of their downsides.

tucoxn · Dec 8, 2017

I solved this with adding the "Count" function into the query:

Code:

SELECT Alumni.Last, Alumni.SeminarName, Alumni.Staffers2
FROM SeminarStaffers INNER JOIN Alumni ON SeminarStaffers.ID = Alumni.Staffers2.Value
GROUP BY Alumni.Last, Alumni.SeminarName, SeminarStaffers.StafferName
HAVING (((SeminarStaffers.StafferName)="Natalia") AND ((Count(Alumni.Staffers2))=1));

This results in exactly what I want:

Code:

Alumni.Last        Alumni.SeminarName   Alumni.Staffers2
Roberts            Teachers2            Natalia
Street             Teachers2            Natalia

Mark_, I agree with you about the naming convention. I should change "Staffers2" to something else. I called it that because I had to delete my first two tries at making that table, which were called "Staffers" and "Staffers1".

Thanks again for allowing me into your community.

query only one list element in multiple lookup field (with multiselect listbox) (1 Viewer)

tucoxn

New member

Ranman256

Well-known member

tucoxn

New member

Mark_

Longboard on the internet

tucoxn

New member

Users who are viewing this thread