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
Chuck LoweAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Why do you want to store this indicator?  As soon as a new record for the client is added or deleted, the indicator could be incorrect.  It would be better to count as needed.  Regardless, this count requires a two step process since Access SQL does not support the count distinct predicate.  I do this by nesting queries.
qry1
Select ClientID, DocType
From YourTable
Group By ClientID, DocType

qry2
Select ClientID
From qry1
Group By ClientID
Having Count(*) > 1;

It is qry2 that would return the clients you want to flag but you can't use qry2 directly to update the table since queries that aggregate data are not updateable.  You might be able to get this to work by making qry2 a subquery or you might make qry2 a make table query and then use the temp table to do the update.  

BUT ---------  since this update violates normal forms, do not do it unless you are absolutely certain this is necessary and you understand the ramifications of doing it.
0
Rey Obrero (Capricorn1)Commented:
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)
0
Chuck LoweAuthor Commented:
@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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Chuck LoweAuthor Commented:
@Pat
They are added in a batch process not from a user or form input.
0
PatHartmanCommented:
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.
0
Rey Obrero (Capricorn1)Commented:
ok, try this

UPDATE tbl_Client
 SET tbl_Client.MultiType = "*"
 Where Exists
(
      Select A.ClientID, Count(A.DocType)
       From
(
            SELECT C.ClientID, C.DocType
             FROM tbl_Client AS C
              GROUP BY C.ClientID, C.DocType
) As A
Where A.ClientID=tbl_Client.ClientID
Group by A.ClientID
Having Count(A.DocType) > 1)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chuck LoweAuthor Commented:
I gave you both credit. Both work. Thanks Guys.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.