Solved

Determine Ratio in ACCESS query

Posted on 2010-09-02
5
1,286 Views
Last Modified: 2012-05-10
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
Comment
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
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
Sheils earned 500 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

by:ssmith94015
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

by:Sheils
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

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

Author Closing Comment

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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This collection of functions covers all the normal rounding methods of just about any numeric value.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Viewers will learn the different options available in the Backstage view in Excel 2013.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question