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
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
Last Comment
Chuck Lowe
SOLUTION
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.
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
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
ASKER
@Pat
They are added in a batch process not from a user or form input.
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
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.
ASKER
I gave you both credit. Both work. Thanks Guys.
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)