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
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.
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.
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.Clie
GROUP BY C.ClientID
Having Count(C.DocType) >1)