Determine Ratio in ACCESS query

Ok, this is going to be an obscure solution.  I saw this once done many many years ago and am trying to figure out how to do it in ACCESS 2003 today as I remember.  I have a query that has activities called a "job".  In a "job", there are various numbers of reports.  Now these reports are run each day, but some may fail.  I need to determine the ratio of failures to the total numberof reports in each job.  So, I can have five jobs, each job holds 10 reports.  Say job ONE runs but  2 out of 10 fail, so taht ratio is 2/10.  Fine, but in my queries I have the five jobs and i need to determine the ratio of failures for  each job.  I am trying to avoid doing a lot of summing in queries and the combineing, etc.  It was a neat trick to do in one query and was wondering if anyone had ever seen/solved this same problem.
Sandra SmithRetiredAsked:
Who is Participating?
 
SheilsConnect With a Mentor Commented:
I believe you can acheive this by adding two extra fields in your query. One to count the total number of jobs and one to count the number of failed jobs. Then use the to show your ratio. The sql will be something like

Select COUNT(A.fldResult) AS fldTotal, COUNT(B.fldResult) AS fldFail,fldFail & "/" & fldTotal AS fldRatio  FROM tbl_Jobs As A Left JOIN tblJobs As B ON A.JobID=B.JobID
HAVING B.Result="Fail"
0
 
Sandra SmithRetiredAuthor Commented:
A little confusing, but I think I can see where this is going.  Bascially joining the table back onto itself.  yes, this is an approach.  The one I as thinking actually uses built-in functions based on criteria, but let me try your approach.
0
 
SheilsCommented:
That was just a guide. I can provide a clear answer if you provide the structure of your database. A sample will be ideal but a screen shot of the relationship window showing the relationship between the relevant tables will do fine.

What I am trying to do is query the same table twice but in a different way. So I give it different aliases. Which means that the where condition only applies to the second instance. Two make it simple you can split this into 3 queries.

qryTotal

Select JobID,Count(Result) AS fldTotal from tblJobs

qryFail

Select JobID,Count(Result) AS fldFailed from tblJobs WHERE Result =Failed

qryRatio

Select J.JobID, Nz(fldFailed,0) & "/" & fldTotal Form qryTotal As J
LEFT JOIN qryFail As F
ON J.JobID=F.JobID



As you can see I have added Nz(fldFailed,0). This is so that you get a zero when there is no failed jobs



0
 
Sandra SmithRetiredAuthor Commented:
sb9, am leaving for the day, but will return tomorrow with the details you need.
0
 
Sandra SmithRetiredAuthor Commented:
Thank you, this worked much more elegantly than what I thought I would have to do.
0
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.

All Courses

From novice to tech pro — start learning today.