Solved

Determine Ratio in ACCESS query

Posted on 2010-09-02
5
1,266 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This collection of functions covers all the normal rounding methods of just about any numeric value.
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 …
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…

756 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