Link to home
Start Free TrialLog in
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
SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)
Avatar of Chuck Lowe
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
@Pat
They are added in a batch process not from a user or form input.
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I gave you both credit. Both work. Thanks Guys.