The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
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.
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.Clie
Group by A.ClientID
Having Count(A.DocType) > 1)
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.Clie
Group by A.ClientID
Having Count(A.DocType) > 1)
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 trialIt'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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.