Access Query Count multiple types and Update table

Chuck Lowe
Chuck Lowe used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
Commented:
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.
Top Expert 2016

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)

Author

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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
@Pat
They are added in a batch process not from a user or form input.
Distinguished Expert 2017

Commented:
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.
Top Expert 2016
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)

Author

Commented:
I gave you both credit. Both work. Thanks Guys.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial