Totally Stuck 1

[Hawkide]

I am trying to create a query and I’m about to pull out my hair. Please help…

I have a table with test info. One of the fields in the table contains a true/false value indicating if the test was INVALID (true = Invalid, false = valid)

I want to create a query that returns what percentage (or fraction) of the tests were valid (i.e. NOT INVALID)

For Example: if Test_1 occurred 100 times, and 7 of those had INVALID marked as true, the calculated result should be 93%

The query should give results grouped by test

Test_1 93%
Test_2 85%
Test_3 100%

The query is going to be used for a bar chart on a graph that displays % Valid vs Test. TIA...

 

[PHV]

A starting point:
SELECT TestField, (Count(*)+Sum(InvalidField))/Count(*) As [% Valid]
FROM yourTable
GROUP BY TestField

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Hawkide]

I have spent hours on this, and you just solved it for me in minutes. I think I should send you a fruit cake or something. Thank you soooo much...

 

[Hawkide]

Thanks again for the previous help. I was able to use your suggestion in a couple queries.

I have another tricky one that I have spent hours on. Somewhat similar to before in what I am looking for.

I have the same table with test info. It contains the following fields:

StartDate
EndDate
TimeAllowed (TimeAllowed is an integer value in Days)

I want to create a query that returns what percentage (or fraction) of the tests were within the TimeAllowed


For example…Lets say the TimeAllowed = 5 days
If EndDate - StartDate = 4 days then our result is within[/Color Green]
If EndDate - StartDate = 5 days then our result is within[/Color Green]
If EndDate - StartDate = 6 days then our result is NOT within[/Color Red]

In other words:
DateDiff("d",[StartDate],[EndDate]) <= TimeAllowed , then our result is within
DateDiff("d",[StartDate],[EndDate]) > TimeAllowed , then our result is NOTwithin

Once again the query should give the results grouped by Test.

This is the last of 12 queries I need to create and I am going nutz…