UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Ms Access 2016 Record Count With Subquery Count With Percent Result, Access 2016    
 
   
gknoll
post Mar 7 2018, 12:22 PM
Post#1



Posts: 39
Joined: 24-September 05



I have a query that counts the number of records in a table that contains a number of repair tickets, and the result is the number of repair tickets by company that have been completed. Example; Companycode | CompnayName | CountOfTickets

CODE
SELECT
Incident.CompanyCode, Count(Incident.CompanyCode) AS IncCount
FROM
Incident
GROUP BY
Incident.CompanyCode, Incident.IncidentType, Incident.IncidentClosed
HAVING
(((Incident.IncidentType)="On - Site") AND
((Incident.IncidentClosed)=True));


Now, what I am having a hard time getting my head wrapped around is this. Each of the records being counted has a field for marking the ticket as having met an SLA. The field is a text field with either a "Y" or an "N".

What I am trying to do is count the number of records within the query above, that meets the criteria of either a "Y" or an "N", and then divide that result by the count of the main query to get a percentage of repair tickets that match the criteria. So, out of x number of repair tickets for company xyz, x% have met the SLA.

I know this would likely involve a sub-query, but I am not sure how to code it or where to put the sub-query.

I would like to have the result as part of the same result table ie.. Companycode | CompnayName | CountOfTickets | % Met SLA

I don't know if the solution would be a simple one or not, but I have been working on this for sometime now and I just can't see it.

Any help would be highly appreciated.

Thank you for taking the time to review this post and for any help that is given.

Greg
This post has been edited by gknoll: Mar 7 2018, 12:23 PM
Go to the top of the page
 
LPurvis
post Mar 7 2018, 12:52 PM
Post#2


UtterAccess Editor
Posts: 16,271
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

I think we can get you started (and the rest will come) with a suggestion of counting a specific value.

CODE
SELECT
  Incident.CompanyCode,
  Count(Incident.CompanyCode) AS IncCount,
  Count(IIF(SLAFieldName = "Y", 1, Null)) AS YesCount
FROM
  Incident
WHERE
  Incident.IncidentType="On - Site" AND Incident.IncidentClosed=True
GROUP BY
  Incident.CompanyCode, Incident.IncidentType, Incident.IncidentClosed


Once you have the YesCount you can use that expression to operate on the values as required. (Remember to use the full expression though and not the alias YesCount.)


Cheers

--------------------
Go to the top of the page
 
RJD
post Mar 7 2018, 12:57 PM
Post#3


UtterAccess VIP
Posts: 8,352
Joined: 25-October 10
From: Gulf South USA


Hi: Or, instead of Count, you could use as an alternative ...

Sum(IIf([DecisionField]="Y",1,0)) As YCount

..and use the results to calculate the percentage.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
LPurvis
post Mar 7 2018, 01:06 PM
Post#4


UtterAccess Editor
Posts: 16,271
Joined: 27-June 06
From: England (North East / South Yorks)


That is indeed the more usual method.
(Which is exactly why I coerced the Count option ;-)

--------------------
Go to the top of the page
 
RJD
post Mar 7 2018, 04:25 PM
Post#5


UtterAccess VIP
Posts: 8,352
Joined: 25-October 10
From: Gulf South USA


thumbup.gif

...but I like the way you did the Count option, using the Null no-count situation (which may need some explanation for some folks seeing this :-) ).

joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
gknoll
post Mar 8 2018, 09:00 AM
Post#6



Posts: 39
Joined: 24-September 05



Thank you both for your suggestions.

I appreciate the time you took to help me out.

I will give them a go and let you know how it turns out.

Greg
Go to the top of the page
 
gknoll
post Mar 8 2018, 09:34 AM
Post#7



Posts: 39
Joined: 24-September 05



RJD,

Your solution seems to work just the way I need it to.
I knew it had to be something simple and straight forward.
I thank you very much. hat_tip.gif

LPurvis,

I will keep your solution in my tool box in case I need something
that may be more complex or need more control. thumbup.gif

I thank you both.

Cheers!
Go to the top of the page
 
LPurvis
post Mar 8 2018, 10:13 AM
Post#8


UtterAccess Editor
Posts: 16,271
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

They were effectively identical in the result provided. But the Sum is probably more intuitive (but where's the fun in that? :-p)
If nothing else, it's worth taking away how Count works upon Nulls - just for future reference.

Cheers

--------------------
Go to the top of the page
 
RJD
post Mar 8 2018, 10:58 AM
Post#9


UtterAccess VIP
Posts: 8,352
Joined: 25-October 10
From: Gulf South USA


You are very welcome. We are always happy to assist.

And a second on understanding the Nulls use with Count ... a neat approach.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th June 2018 - 03:51 AM