CODEWITHSUNDEEP
sqlms-accessms-access-2010ms-access-2007
Kendal
Kendal

Reputation: 161

MS Access: query only one criteria in multiple criteria lookup field

I have a MS Access database that has a multiple criteria lookup field with entries like:

ID  Names
1   Carl, Daniel
2   Natalia
3   Carl, Natalia
4   Natalia, Carl
5   Carl
6   Natalia
7   Bob
8   Bob
9   Bob, Natalia

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

2   Natalia
6   Natalia

I tried this code and it isn't working:

WHERE (((Table.Name)="Natalia"));

This provides all the instances that include Natalia:

2   Natalia
3   Carl, Natalia
4   Natalia, Carl
6   Natalia
9   Bob, Natalia

Thank you very much for your help.

Upvotes: 0

Views: 686

Answers (2)

Kendal
Kendal

Reputation: 161

I solved the problem with the following line of code:

    HAVING (((Names)="Natalia") AND ((Count(Staffers))=1));

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

First, you should not be storing lists a delimnited strings. The right way is a junction table, with one row per name.

Second, your WHERE clause (with the column name fixed) should do what you want:

WHERE Names = "Natalia"

If you wanted all those other rows, you would use LIKE:

WHERE Names LIKE "*Natalia*"

Upvotes: 0

Related Questions