Help with SQL Query - Conditional SHOW

J

Jen

Hi,
I have a complex SQL Query that I am having some trouble with. I have
2 tables TestTable and Actual Table. I need to manipulate data from
each table for 2 different time periods. Using aliases for the tables,
I am able to get the correct values I need for the two time periods.

However, I don't want to show all the results. The average values are
typically between -50 and 50. I want only those whose absolute value
is greater than 5 to be displayed. Is there a way to hide the rest?
Also, is there a way to sort them by the absolute value?

SELECT Avg(T1.Param1-E1.Param1)-Avg(T2.Param1-E2.Param1) AS DiffP1,
Avg(T1.Param2-E1.Param2)-Avg(T2.Param2-E2.Param2) AS
DiffP2,
Avg(T1.Param3-E1.Param3)-Avg(T2.Param3-E2.Param3) AS
DiffP3
Avg(T1.Param4-E1.Param4)-Avg(T2.Param4-E2.Param4) AS
DiffP4
Avg(T1.Param5-E1.Param5)-Avg(T2.Param5-E2.Param5) AS
DiffP5
Avg(T1.Param6-E1.Param6)-Avg(T2.Param6-E2.Param6) AS
DiffP6
Avg(T1.Param7-E1.Param7)-Avg(T2.Param7-E2.Param7) AS
DiffP7
Avg(T1.Param8-E1.Param8)-Avg(T2.Param8-E2.Param8) AS
DiffP8
Avg(T1.Param9-E1.Param9)-Avg(T2.Param9-E2.Param9) AS
DiffP9
Avg(T1.Param10-E1.Param10)-Avg(T2.Param10-E2.Param10) AS
DiffP10
FROM TestTable AS T1 INNER JOIN ActualTable AS E1 ON
T1.DataTime=E1.DataTime, TestTable AS T2 INNER JOIN ActualTable AS E2
ON T2.DataTime=E2.DataTime
WHERE (((T1.DataTime) Between [Start Date] And [End Date]) AND
((T2.DataCaptureTime) Between [Start Date 2] And [End Date 2]));

Right now my results appear as:
DiffP1 DiffP2 DiffP3 DiffP4 DiffP5 DiffP6 DiffP7 DiffP8
DiffP9 DiffP10
-10 12 3 -6 -7 27 -14
-4 -8 9

I would like the following:
DiffP6 DiffP7 DiffP2 DiffP1 DiffP10 DiffP9
DiffP5 DiffP4
27 -14 12 -10 9 -8
-7 -6

And columns with DiffP3, DiffP8 are eliminated.

Any help would be greatly appreciated!!
Jen
 
K

Ken Snell \(MVP\)

Add HAVING clauses to the query for the field where you want to filter the
results as you indicate. Structure of HAVING clause is same as WHERE clause,
but it goes after the GROUP BY clause (which comes after the WHERE clause).
You don't appear to have a GROUP BY clause, so put it after the WHERE
clause.

If you use the query grid, you do this by putting the criterion in the WHERE
box under the column that contains the AVG field of choice.
 
J

Jen

Gary,
Thanks so much for the idea! It works great, and in Access I now have
the data that I want in the order I want. I really appreciate all your
help!

I am having one problem...I need to then copy the query from Access to
Excel. I am using the function CopyFromRecordset, but the data columns
are reversed. I have tried modifying the last query "qryThin" so that
the columns are backwards in access in the hopes that they would then
be copied correctly in Excel, but that doesn't work either...Would you
happen to have any ideas about that?

Thanks again!
Jen
Gary said:
I don't think so easily....

you are going to have to go "thin"
(then maybe convert back if you want)

save following (say as "qryunThin")

(change "qJen" to name of your shown query)

SELECT "DiffP1" As f, DiffP1 As v, Abs(DiffP1) As a FROM qJen
UNION ALL
SELECT "DiffP2", DiffP2, Abs(DiffP2) FROM qJen
UNION ALL
SELECT "DiffP3", DiffP3, Abs(DiffP3) FROM qJen
UNION ALL
SELECT "DiffP4", DiffP4, Abs(DiffP4) FROM qJen
UNION ALL
SELECT "DiffP5", DiffP5, Abs(DiffP5) FROM qJen
UNION ALL
SELECT "DiffP6", DiffP6, Abs(DiffP6) FROM qJen
UNION ALL
SELECT "DiffP7", DiffP7, Abs(DiffP7) FROM qJen
UNION ALL
SELECT "DiffP8", DiffP8, Abs(DiffP8) FROM qJen
UNION ALL
SELECT "DiffP9", DiffP9, Abs(DiffP9) FROM qJen
UNION ALL
SELECT "DiffP10", DiffP10, Abs(DiffP10) FROM qJen;

then following query (say "qryThin")
will then filter and sort "thinly"

SELECT
q.f,
q.v
FROM qryunThin As q
WHERE
q.a >5
ORDER BY
q.a DESC;

which, using your data, would give:

f v
DiffP6 27
DiffP7 -14
DiffP2 12
DiffP1 -10
DiffP10 9
DiffP9 -8
DiffP5 -7
DiffP4 -6

you could jump through some hoops
to make this result wide again, but
if you use a crosstab and pivot off
of f, you will lose the "sort"

TRANSFORM First(qryThin.v) AS FirstOfv
SELECT Null AS <>
FROM qryThin
GROUP BY Null
PIVOT qryThin.f;

would give:

<> DiffP1 DiffP2 DiffP4 DiffP5 DiffP6 DiffP7 DiffP9 DiffP10
-10 12 -6 -7 27 -14 -8 9

hoops w/o desired result....

I cannot immediately think of some simple, sneaky
trick to get back the sort...sorry


Jen said:
I have a complex SQL Query that I am having some trouble with. I have
2 tables TestTable and Actual Table. I need to manipulate data from
each table for 2 different time periods. Using aliases for the tables,
I am able to get the correct values I need for the two time periods.

However, I don't want to show all the results. The average values are
typically between -50 and 50. I want only those whose absolute value
is greater than 5 to be displayed. Is there a way to hide the rest?
Also, is there a way to sort them by the absolute value?

SELECT Avg(T1.Param1-E1.Param1)-Avg(T2.Param1-E2.Param1) AS DiffP1,
Avg(T1.Param2-E1.Param2)-Avg(T2.Param2-E2.Param2) AS
DiffP2,
Avg(T1.Param3-E1.Param3)-Avg(T2.Param3-E2.Param3) AS
DiffP3
Avg(T1.Param4-E1.Param4)-Avg(T2.Param4-E2.Param4) AS
DiffP4
Avg(T1.Param5-E1.Param5)-Avg(T2.Param5-E2.Param5) AS
DiffP5
Avg(T1.Param6-E1.Param6)-Avg(T2.Param6-E2.Param6) AS
DiffP6
Avg(T1.Param7-E1.Param7)-Avg(T2.Param7-E2.Param7) AS
DiffP7
Avg(T1.Param8-E1.Param8)-Avg(T2.Param8-E2.Param8) AS
DiffP8
Avg(T1.Param9-E1.Param9)-Avg(T2.Param9-E2.Param9) AS
DiffP9
Avg(T1.Param10-E1.Param10)-Avg(T2.Param10-E2.Param10) AS
DiffP10
FROM TestTable AS T1 INNER JOIN ActualTable AS E1 ON
T1.DataTime=E1.DataTime, TestTable AS T2 INNER JOIN ActualTable AS E2
ON T2.DataTime=E2.DataTime
WHERE (((T1.DataTime) Between [Start Date] And [End Date]) AND
((T2.DataCaptureTime) Between [Start Date 2] And [End Date 2]));

Right now my results appear as:
DiffP1 DiffP2 DiffP3 DiffP4 DiffP5 DiffP6 DiffP7 DiffP8
DiffP9 DiffP10
-10 12 3 -6 -7 27 -14
-4 -8 9

I would like the following:
DiffP6 DiffP7 DiffP2 DiffP1 DiffP10 DiffP9
DiffP5 DiffP4
27 -14 12 -10 9 -8
-7 -6

And columns with DiffP3, DiffP8 are eliminated.

Any help would be greatly appreciated!!
Jen
Click to expand...
Click to expand...
 
J

Jen

Gary,
Thanks for the info on getting an excel spreadsheet...I'm actually
using Visual C++, but your info helped me get it all working! Thanks
so much!
Jen

Gary said:
I am not an Excel expert but have used
the SQL's similar to the following in several
projects.


to
*create a new Excel table*
(say "FirstTable" in *new* C:\Diff.xls)

SELECT
qryunJen.f As SomeAliasBetterThanf,
qryunJen.v As SomeAliasBetterThanv
INTO
[Excel 8.0;database=C:\Diff.xls].FirstTable
FROM
qryunJen
WHERE
(((qryunJen.a)>5))
ORDER BY
qryunJen.a DESC;

or
(if C:\Diff.xls already exists
and is not open)
to
*add a new sheet *
(say named "2006_07_08")

SELECT
qryunJen.f As SomeAliasBetterThanf,
qryunJen.v As SomeAliasBetterThanv
INTO
[Excel 8.0;database=C:\Diff.xls].2006_07_08
FROM
qryunJen
WHERE
(((qryunJen.a)>5))
ORDER BY
qryunJen.a DESC;

Sadly, you can no longer
append to an Excel sheet.


Jen said:
Thanks so much for the idea! It works great, and in Access I now have
the data that I want in the order I want. I really appreciate all your
help!

I am having one problem...I need to then copy the query from Access to
Excel. I am using the function CopyFromRecordset, but the data columns
are reversed. I have tried modifying the last query "qryThin" so that
the columns are backwards in access in the hopes that they would then
be copied correctly in Excel, but that doesn't work either...Would you
happen to have any ideas about that?

Thanks again!
Jen
Gary said:
I don't think so easily....

you are going to have to go "thin"
(then maybe convert back if you want)

save following (say as "qryunThin")

(change "qJen" to name of your shown query)

SELECT "DiffP1" As f, DiffP1 As v, Abs(DiffP1) As a FROM qJen
UNION ALL
SELECT "DiffP2", DiffP2, Abs(DiffP2) FROM qJen
UNION ALL
SELECT "DiffP3", DiffP3, Abs(DiffP3) FROM qJen
UNION ALL
SELECT "DiffP4", DiffP4, Abs(DiffP4) FROM qJen
UNION ALL
SELECT "DiffP5", DiffP5, Abs(DiffP5) FROM qJen
UNION ALL
SELECT "DiffP6", DiffP6, Abs(DiffP6) FROM qJen
UNION ALL
SELECT "DiffP7", DiffP7, Abs(DiffP7) FROM qJen
UNION ALL
SELECT "DiffP8", DiffP8, Abs(DiffP8) FROM qJen
UNION ALL
SELECT "DiffP9", DiffP9, Abs(DiffP9) FROM qJen
UNION ALL
SELECT "DiffP10", DiffP10, Abs(DiffP10) FROM qJen;

then following query (say "qryThin")
will then filter and sort "thinly"

SELECT
q.f,
q.v
FROM qryunThin As q
WHERE
q.a >5
ORDER BY
q.a DESC;

which, using your data, would give:

f v
DiffP6 27
DiffP7 -14
DiffP2 12
DiffP1 -10
DiffP10 9
DiffP9 -8
DiffP5 -7
DiffP4 -6

you could jump through some hoops
to make this result wide again, but
if you use a crosstab and pivot off
of f, you will lose the "sort"

TRANSFORM First(qryThin.v) AS FirstOfv
SELECT Null AS <>
FROM qryThin
GROUP BY Null
PIVOT qryThin.f;

would give:

<> DiffP1 DiffP2 DiffP4 DiffP5 DiffP6 DiffP7 DiffP9 DiffP10
-10 12 -6 -7 27 -14 -8 9

hoops w/o desired result....

I cannot immediately think of some simple, sneaky
trick to get back the sort...sorry


:
I have a complex SQL Query that I am having some trouble with. I have
2 tables TestTable and Actual Table. I need to manipulate data from
each table for 2 different time periods. Using aliases for the tables,
I am able to get the correct values I need for the two time periods.

However, I don't want to show all the results. The average values are
typically between -50 and 50. I want only those whose absolute value
is greater than 5 to be displayed. Is there a way to hide the rest?
Also, is there a way to sort them by the absolute value?

SELECT Avg(T1.Param1-E1.Param1)-Avg(T2.Param1-E2.Param1) AS DiffP1,
Avg(T1.Param2-E1.Param2)-Avg(T2.Param2-E2.Param2) AS
DiffP2,
Avg(T1.Param3-E1.Param3)-Avg(T2.Param3-E2.Param3) AS
DiffP3
Avg(T1.Param4-E1.Param4)-Avg(T2.Param4-E2.Param4) AS
DiffP4
Avg(T1.Param5-E1.Param5)-Avg(T2.Param5-E2.Param5) AS
DiffP5
Avg(T1.Param6-E1.Param6)-Avg(T2.Param6-E2.Param6) AS
DiffP6
Avg(T1.Param7-E1.Param7)-Avg(T2.Param7-E2.Param7) AS
DiffP7
Avg(T1.Param8-E1.Param8)-Avg(T2.Param8-E2.Param8) AS
DiffP8
Avg(T1.Param9-E1.Param9)-Avg(T2.Param9-E2.Param9) AS
DiffP9
Avg(T1.Param10-E1.Param10)-Avg(T2.Param10-E2.Param10) AS
DiffP10
FROM TestTable AS T1 INNER JOIN ActualTable AS E1 ON
T1.DataTime=E1.DataTime, TestTable AS T2 INNER JOIN ActualTable AS E2
ON T2.DataTime=E2.DataTime
WHERE (((T1.DataTime) Between [Start Date] And [End Date]) AND
((T2.DataCaptureTime) Between [Start Date 2] And [End Date 2]));

Right now my results appear as:
DiffP1 DiffP2 DiffP3 DiffP4 DiffP5 DiffP6 DiffP7 DiffP8
DiffP9 DiffP10
-10 12 3 -6 -7 27 -14
-4 -8 9

I would like the following:
DiffP6 DiffP7 DiffP2 DiffP1 DiffP10 DiffP9
DiffP5 DiffP4
27 -14 12 -10 9 -8
-7 -6

And columns with DiffP3, DiffP8 are eliminated.

Any help would be greatly appreciated!!
Jen
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help with an Unbound Form that executes a storedprocedure to add records 4

Top