Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Determine Ratio in ACCESS query

Posted on 2010-09-02
Medium Priority
1,361 Views
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.
0
Question by:ssmith94015
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 16

Accepted Solution

Sheils earned 2000 total points
ID: 33591375
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

Author Comment

ID: 33591468
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

LVL 16

Expert Comment

ID: 33591628
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

Author Comment

ID: 33592352
sb9, am leaving for the day, but will return tomorrow with the details you need.
0

Author Closing Comment

ID: 33600695
Thank you, this worked much more elegantly than what I thought I would have to do.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
###### Suggested Courses
Course of the Month7 days, 3 hours left to enroll