Help to group by categories (1 Viewer)

[linus]

Hello everyone, im relatively new in access and i need some help:

I have this query that select the ID and NAME of a client, and a possible segment to which it belongs as well...
to do so i make several queries and join them by using UNION ALL, like this:

I need to count the number of segments per customer and leave the one with the highest number of repetitions... but i dont know how to use a count or group by functions separating the different segments... like... if "TECNOLOGICO" appears 7 times and the rest only 4 for a single CUSTOMER, leave only 1 row with "TECNOLOGICO" as VALUE of SEGMENT

I will greatly appreciate your help
ALSO, excuse me for my bad english

 

[arnelgp]

you need to Create a Total query from the Union query you already have.

 

[namliam]

having umpteen tables to store this data is a sure sign of a faulty design, suggest you fix your design.

Going with this.
Store your union query as qryUnion
Select
union all
select etc.

Store this as qryTeller

Select ID, SEGMENT, COUNT(*) as AantalKeer
From   qryUnion
group by ID, SEGMENT

Store this as qryTellerMax

Select ID, max(AantalKeer) myMax from qryTeller group by ID

This should give your end result.

Select qryTeller.*
from   qryTEller
join   qryTellerMax on qryTEller.ID = qryTellerMax.ID and qryTeller.AantalKeer = qryTEllerMax.myMax

Now all you need to do is figure out how to work with two segments that both have 10 lines.