How to filter by a field that has multiple selections. 1

[Dontremb]

Ok, I just need to set up a criteria that filters my query based on whether or not that field has more than one selection or not.

I.E.: The field is a combo box with, "red, white, blue"

User can select multiple values.

However, when I'm running the calculations, I need to know how many people selected more than one color. I also need to know how many people ONLY selected one color.

Is there any way to do this?

 

[PHV]

SELECT InStr([your field], ',') AS CountOfColor, Count(*) AS CountOfPeople
FROM yourTable
GROUP BY InStr([your field], ',')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWise]

select user ,count(*) as countofcolors from(
select distinct user ,color
from tablename) dt
group by user

 

[PHV]

In fact, I meant this:
SELECT 1+InStr([your field],',') AS CountOfColor, Count(*) AS CountOfPeople
FROM yourTable
GROUP BY InStr([your field], ',')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dontremb]

Ok, thanks, PHV. I tried this:

SELECT 1+InStr([RacialCategory],',') AS CountOfColor, Count(*) AS CountOfPeople
FROM qryRacesBreakdown
GROUP BY InStr([RacialCategory], ',')

That gave me the error:

The multi-valued field '[RacialCategory]' is not a valid expression '1+InStr([RacialCategory],',')'.

I'm probably missing something. Is there any chance you could explain what your SQL statement means? I am probably going to need a query that just lists the selected people first or something.

Thanks again!

 

[PHV]

multi-valued field
Access2007 new type of field to use only for compatibility with SharePoint ?
sorry, I can't help you.

 

[Dontremb]

Oh. That's not good.

Hrm.

 

[Dontremb]

There has to be a way to return only the fields that have multiple selections, right?

If not, what is the point of having fields that allow multiple selections?

If the data is unusable in any way...?

 

[PHV]

Have a look here:

http://allenbrowne.com/Access2007.html#Mixed

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dontremb]

Well crap. Thanks, PHV.

I'm going to do some additional research, but it looks like this is mostly bad news in that I'm not going to be able to do this at all.

On another note, how else do you make a combo box with multiple selections available?

 

[Dontremb]

Well, it looks like I have gotten SOME usable knowledge.

I've been able to display all the multiple selections in different fields. So that's a step in the right direction.

Now the problem is this:

Field1     Field2
John       red
John       white
Roger      blue
Fred       red
Fred       white
Fred       blue
George     red

So, that's what my query looks like. I KNOW there is a way with a query to only display those records that only have one listing. Or those that only display more than one listing.

Note that I don't really want to use 'distinct' because by using that, I'll still be listing everyone, I'll just only see their first color. I actually want to NOT display those who have more than one color.

Thanks again, PHV.

 

[PHV]

SELECT Field1, First(Field2) AS SingleColor
FROM yourQueryAbove
GROUP BY Field1
HAVING Count(*)=1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dontremb]

Awesome, PHV. I'm sure that's simply the SQL for the query that I just figured out how to do. (I'm not at ALL good at SQL yet.)

Anyway, the way I did it was this:

If you use the Design Tab to display totals, you can use that column under [Color] to have it Count.

If you do that, then instead of displaying the actual colors, it will display the number of colors each person has.

Then, I can just set the criteria to 1 or >1 to filter it to exactly what I want.

So, while I didn't try your latest idea, PHV, your last link, (which lead to SEVERAL links, and eventually to a specific part of the Access Help file (which I had to go through SEVERAL pages)) led me to the answer I needed.

So, thank you very much PHV.

 

[pointingbearing]

I do not think small piece of code can achieve your requirements! Maybe I did something wrong, I just can gave it done with simple code! I 'solved' my trouble with SharePoint Cascaded Lookup.
Since my situation is little different so I recommend you just give a shot to it!

 

[pointshing]

I am using SharePoint Cascaded Lookup(

http://www.sharepointboost.com/cascaded-lookup.html)

too, it works fine!
However, I just am just wandering is there any way I can conduct several tiers filter rather just two.
Thanks