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!
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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.Clie
GROUP BY C.ClientID
Having Count(C.DocType) >1)
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)
@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
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial