Avatar of Chuck Lowe
Chuck Lowe
 asked on

Access Query Count multiple types and Update table

I hope the title is correct. I want to identify ClientId that have multiple DocType.

ClientID            DocType
00004            1

00011            1
00011            1
00011            1
00011            2
00011            2

00015            1
00015            3

00020            1
00020            1
00020            1

In the above example ClientID 00011 and 00015 have multiple types. CliendID 00004 and 00020 do not. I want to then Update a field on the table tbl_Clients.MultiType with a "*" for clients that have the multiple types
Microsoft Access

Avatar of undefined
Last Comment
Chuck Lowe

8/22/2022 - Mon
SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rey Obrero (Capricorn1)

try this query


UPDATE tbl_Client
SET tbl_Client.MultiType = "*"
Where Exists
(SELECT C.ClientID, Count(C.DocType) AS CountOfDocType
FROM tbl_Client AS C Where C.ClientID=tbl_Client.ClientID
GROUP BY C.ClientID
Having Count(C.DocType) >1)
Chuck Lowe

ASKER
@Pat
I understand your concern about updating the field. The records are added once every 2 weeks so I will clear the field and then populate it. It looks like the make table query is the way it will work.

@Rey
Rey this updates records that have a count > 1 regardless of the DocType.

Thanks Guys
Chuck Lowe

ASKER
@Pat
They are added in a batch process not from a user or form input.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
PatHartman

Rey wrote the SQL for you (I didn't write it because I didn't want to make it too easy for you to do the wrong thing) but you need to modify it the way I described.  You first need a query that groups by client and doctype.  THEN count THAT query as in Rey's sample.
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Chuck Lowe

ASKER
I gave you both credit. Both work. Thanks Guys.