Total ,Fail , Pass Queries

  • Thread starter Thread starter anil
  • Start date Start date
A

anil

Hi all
I am using Four queries to get final percentage.

In first query I get all results from tables in particular date period.
In second query I get [Total] of result/Sample with respect to on
Field.
In third query I get the [Fail] of results where condition = [ ].
Then in Fourth query I get pass by [Total]-[Fail] and then the
percentage.

I have lot of these queries depending on location ,Parameters,Months
and so on.

Is there any way that I can get rid of these queries in VBA or atleast
decrease the number from 4 to 3 or 2.

Thanks
anil
 
You have not given us the SQL of your queries, so this is a guess.

It will probably be possible to select all of the data you want with one
query. It will probably be possible to count and sum the data you want in
that query. You will probably have to input those results into another query
to calculate the percentages you desire. That would make it a two query job.

It will probably be possible to clump together all of that SQL into "one"
query that gives you the whole answer, but this may be virtually unreadable
and a maintenance nightmare.

You can get the various counts and sums that you need by using IIF
functions. e.g.

SUM (IIF(ISNULL([myfield]),1,0)
SUM(IIF([X]="something",1,0)

HTH Happy travelling.
 
Dear david
My queries look like (although not syntaxically correct, just an idea)

Query 1: - SELECT tblLocation.LocationName, tblSample.SampleTakenDate,
tblParameter.ParameterName, tblResults.ResultValue,
tblResults.ResultIsPass
FROM (tblParameter INNER JOIN (tblSite INNER JOIN (tblSample INNER JOIN
tblResults ON tblSample.SampleID = tblResults.SampleID) ON
tblSite.SiteID = tblSample.SiteID) ON tblParameter.ParameterID =
tblResults.ParameterID) INNER JOIN tblLocation ON tblSite.LocationID =
tblLocation.LocationID;

Query 2(Q2) : - Select locationName,parameterName,Count(resultValue) as
Total,SampleTakenDate from query1;

Query 3(Q3): - Select locationName,parameterName,Count(resultValue) as
failed,SampletakenDate from query1 where query1.resultispass = "0";

Query 4: - Select Q2.LocationName,Q2.ParameterName,Q2.Total,Q3.Failed
,[Total]-[Fail] as [pass],[Pass]/[Total] as [Percent] from q2,q3 joined
by locationName and ParameterName

So if you help me now as in your last mail I could not judge properly
what to do .
thanks
anil
 
This is all "Air code" and might be horribly wrong. Unmatched brackets are
my "thing" so I prefer to paste tested code.

from query 4, working back, we want as a result:

SELECT tblLocation.LocationName,tblParameter.ParameterName, _

So we want:-

GROUP BY tblLocation.LocationName,tblParameter.ParameterName

at the end.
---------------
and complete our desired results in SELECT :-

COUNT(tblResults.ResultValue) AS Total, _
SUM(IIF (tblResults.ResultIsPass = "0",1,0)) AS Failed, _
SUM(IIF(tblResults.ResultIsPass = "0",0, 1)) AS Passed, _
1 + avg(tblResults.ResultIsPass = "0") AS Percent
--------------
Putting it all together:-

SELECT tblLocation.LocationName,tblParameter.ParameterName,
COUNT(tblResults.ResultValue) AS Total,
SUM(IIF(tblResults.ResultIsPass = "0",1,0)) AS Failed,
SUM(IIF(tblResults.ResultIsPass = "0",0, 1)) AS Passed,
1 + avg(tblResults.ResultIsPass = "0") AS Percent
FROM (tblParameter INNER JOIN (tblSite INNER JOIN (tblSample INNER JOIN
tblResults ON tblSample.SampleID = tblResults.SampleID) ON
tblSite.SiteID = tblSample.SiteID) ON tblParameter.ParameterID =
tblResults.ParameterID)
INNER JOIN tblLocation ON tblSite.LocationID = tblLocation.LocationID
GROUP BY tblLocation.LocationName,tblParameter.ParameterName ;

It will be a minor miracle if I have got that syntax right, and if I have
not it will demonstrate my point about maintenance of complex SQL.
 
Thanks david
That worked like wheel and it solved lot of my queries problem or say
no of query problems.
Actually I did not write full query as I there were many factors to be
considered while writting full query and it becomes very bulky and
cumbersome.
So due to shortage of space also,I just provide the small idea.
That is why It look like "air code"although it worked fine in all
queries and give desired results,My only purpose was to reduce number
of queries by writting some better rational approachable query.
Thanks for ur help
anil
This is all "Air code" and might be horribly wrong. Unmatched brackets are
my "thing" so I prefer to paste tested code.

from query 4, working back, we want as a result:

SELECT tblLocation.LocationName,tblParameter.ParameterName, _

So we want:-

GROUP BY tblLocation.LocationName,tblParameter.ParameterName

at the end.
---------------
and complete our desired results in SELECT :-

COUNT(tblResults.ResultValue) AS Total, _
SUM(IIF (tblResults.ResultIsPass = "0",1,0)) AS Failed, _
SUM(IIF(tblResults.ResultIsPass = "0",0, 1)) AS Passed, _
1 + avg(tblResults.ResultIsPass = "0") AS Percent
--------------
Putting it all together:-

SELECT tblLocation.LocationName,tblParameter.ParameterName,
COUNT(tblResults.ResultValue) AS Total,
SUM(IIF(tblResults.ResultIsPass = "0",1,0)) AS Failed,
SUM(IIF(tblResults.ResultIsPass = "0",0, 1)) AS Passed,
1 + avg(tblResults.ResultIsPass = "0") AS Percent
FROM (tblParameter INNER JOIN (tblSite INNER JOIN (tblSample INNER JOIN
tblResults ON tblSample.SampleID = tblResults.SampleID) ON
tblSite.SiteID = tblSample.SiteID) ON tblParameter.ParameterID =
tblResults.ParameterID)
INNER JOIN tblLocation ON tblSite.LocationID = tblLocation.LocationID
GROUP BY tblLocation.LocationName,tblParameter.ParameterName ;

It will be a minor miracle if I have got that syntax right, and if I have
not it will demonstrate my point about maintenance of complex SQL.


anil said:
Dear david
My queries look like (although not syntaxically correct, just an idea)

Query 1: - SELECT tblLocation.LocationName, tblSample.SampleTakenDate,
tblParameter.ParameterName, tblResults.ResultValue,
tblResults.ResultIsPass
FROM (tblParameter INNER JOIN (tblSite INNER JOIN (tblSample INNER JOIN
tblResults ON tblSample.SampleID = tblResults.SampleID) ON
tblSite.SiteID = tblSample.SiteID) ON tblParameter.ParameterID =
tblResults.ParameterID) INNER JOIN tblLocation ON tblSite.LocationID =
tblLocation.LocationID;

Query 2(Q2) : - Select locationName,parameterName,Count(resultValue) as
Total,SampleTakenDate from query1;

Query 3(Q3): - Select locationName,parameterName,Count(resultValue) as
failed,SampletakenDate from query1 where query1.resultispass = "0";

Query 4: - Select Q2.LocationName,Q2.ParameterName,Q2.Total,Q3.Failed
,[Total]-[Fail] as [pass],[Pass]/[Total] as [Percent] from q2,q3 joined
by locationName and ParameterName

So if you help me now as in your last mail I could not judge properly
what to do .
thanks
anil
Click to expand...
Click to expand...
 
Back
Top