Help to group by categories (1 Viewer)

linus

New member
Local time
Today, 03:18
Joined
Feb 13, 2020
Messages
7
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:

1582120194102.png

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

1582121183310.png

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

arnelgp

error reading drive A:
Local time
Today, 15:18
Joined
May 7, 2009
Messages
9,280
you need to Create a Total query from the Union query you already have.
 

namliam

The Mailman - AWF VIP
Local time
Today, 09:18
Joined
Aug 11, 2003
Messages
11,541
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
Code:
Select ID, SEGMENT, COUNT(*) as AantalKeer
From   qryUnion
group by ID, SEGMENT
Store this as qryTellerMax
Code:
Select ID, max(AantalKeer) myMax from qryTeller group by ID
This should give your end result.
Code:
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.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom