Multi-Valued Field Filtering Issue...

[Access '07 Noob]

I've created a multi-value field in Access '07 with a value list I created
myself. Essentially, I'm databasing all of my songs (I'm an aspiring DJ.) I
have several values each song can contain, made via a multivalue data column,
such as: disco, pop, ender, electro, epic, peak) -- etc. So some songs have
multiple values (like peak, disco, and pop). So I have all of those values
checked in my column correctly for the track -- yet when I choose to filter
them and I filter by "peak, disco and pop" only -- it shows any songs which
have ANY one of those three values -- not ALL 3 like I'm searching for. Does
that make sense? So essentially I'm looking for a way to filter with an
"AND" operator and not an "OR" like it's behaving now.

So I'm trying to search for songs which have ALL of those values, which I've
entered via a multi-value list, which I specify -- not ANY. How can I do
this?

I've only worked with Access for about 5 minutes so I'm pretty clueless
about how it works. I don't even know what a query is so be gentle with your
answer. Also, I'd appreciate as much detail as possible about any answers
since I have only about 10 minutes of experience with Access '07!

If anyone thinks this would be easier for me to do in Excel, let me know
that also. I started my track database with Excel then moved it to Access.

Thanks!

 

[Arvin Meyer [MVP]]

Even though Access 2007 has the ability to use multi-valued fields, it is
poor database design to use them. I suggest that you create a second table
which has the SongID and the SongTypeID as an intersection table between the
Song table and the SongType table and have multiple rows of data instead of
multiple values in a single field. You will then find that search becomes
very easy, and you will be able to migrate your data to other systems which
are compliant with good database design.

Multi-valued field were included in Access 2007 to allow it to work with
Sharepoint server which is incapable of relational design.