MS Access '07: Multi-Valued Column Question....

by SoulLifter Jan 30, 2008 4:15PM PST

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?

Essentially I'm just rt-clicking the top of my multi-value column and selecting the values I want to show up in list form. It's definitely treating it as an OR operation and not an AND operation.

I'm trying to search for songs which have ALL of those values which I specifify -- not ANY. How can I do this? I'm just trying to filter the column now but I may need to do something else.

I've only worked with Access for about 5 minutes so I'm pretty clueless about how it works. I don't even know how to query so be kind.

Thanks for any help. Also, if anyone can reccomend a more simple database program for me to keep track of my tracks -- I'm open.

Discussion is locked

- Collapse -

bump...

by SoulLifter Jan 31, 2008 12:47AM PST

...anyone?

- Collapse -

Re: multi-valued column question.

by Kees Bakker Jan 31, 2008 3:15AM PST

If it doesn't work with multi-valued fields, switch to single valued fields in a separate (linked) table. Then it's just a join between different views on the join between these two tables. That's rather elementary use of a relational database. It allows for an unlimited number of values. Making it work with a friendly userinterface and a different number of views is another story.

With a limited number of values (say, up to 20) better make 20 yes/no fields in a record. This is somewhat easier to implement, I think.

In the case of limited number of values, you could also use Excel and rely on autofilter to select the songs for a search combination. That's easier.

Kees

- Collapse -

Addition.

by Kees Bakker Feb 2, 2008 7:06PM PST

I've thought about it a little bit, and this is what I would do in Access.

1. Keep using the multivalued field.
2. Make a basic query for 1 search value, with that being a parameter.
3. Same for 2 search values (that's a join between 2 basic queries, in essence) with 2 parameters.
4. And so on for 3, 4, 5, ...
5. Than make a search screen where I could enter the search criteria. For example via 5 drop-down-boxes.
6. On that screen, depending in the number of criteria entered, call the right query and provide the right parameters via VBA. Or, even easier, have the queries reference the fields on the form (=forms!selectionscreen!field1, and so on).

That's about it. Of course, just running the query and entering the parameters in the dialog windows is possible also, but that's rather prone to typo's. In the screen you can validate them.

Kees

- Collapse -

Thank you

by SoulLifter Feb 4, 2008 6:21AM PST

thanks for the thoughtful replies Kees. Not sure what I'm going to do. Found some DJ cataloging software based on MS Access "DigiJ." Looks promising so I may check that out first.

- Collapse -

Nothing wrong with a well-written application ...

by Kees Bakker Feb 4, 2008 6:40AM PST

if it suits you, especially if you're not a programmer yourself. Programmers might think otherwise ...

Kees

MS Access '07: Multi-Valued Column Question....

CNET Forums

Other Forums

Forum Info