Query that returns values from a list

[mbamber]

Hi all,

I have two tables, TBL_Students and TBL_Email. Each of these tables have a field called Category, which allows multiple values from a list.

Is there a way to return records from TBL_Students where at least one value in TBL_Students!Category = at least one value in TBL_Email!Category.

E.g. If Student A has categories Maths, Physics and Computing;Student B has categories Maths, English and History;Student C has categories Physics and Geography

I would expect:
when TBL_Email!Category = Maths,Physics : Students A,B,C to be returned
when TBL_Email!Category = Maths : Students A,B to be returned
when TBL_Email!Category = Physics,Geography : Students A,C to be returned

I hope this sort of makes sense!

TIA

 

[CJ_London]

is your category field actually stored as a multi select list or a single field with 'Maths,Physics' in it?

I don't use multi select fields myself, so not sure if this will work but I would expect it to be something like

SELECT DISTINCT Tbl_Students.* 
FROM Tbl_Students INNER JOIN Tbl_Email ON Tbl_Students.Category=tbl_Email.Category

But not sure if you can join on multi select fields in which case you might try

SELECT DISTINCT Tbl_Students.* 
FROM Tbl_Students, Tbl_Email 
WHERE Tbl_Students.Category=tbl_Email.Category

 

[mbamber]

Ok thanks.

In the end I remade my data structures and is seems to work better.

Thanks for the reply anyway!