Solved

Determine Ratio in ACCESS query

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
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…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now