Calculation in Query: How do I do this?

[Guest]

I have a query that extracts specific information out of my Diag2005 table.
The sql view is provided, and the resulting output. What I need to be able to
do is get a sum of PDX by PA, ie. I need to have the sum of PDX diagnoses
102+103 by PA (ASC-PA), sum of PDX diagnoses 104+108+109+110 by PA (SIL-PA),
and then be able to provide a ratio by PA ASC-PA/SIL-PA. How do I do this?

SELECT Diag2005.PA, Diag2005.PDX, Count(Diag2005.PDX) AS Tot_PA
FROM Diag2005
GROUP BY Diag2005.PA, Diag2005.PDX
HAVING (((Diag2005.PDX)=102 Or (Diag2005.PDX)=103 Or (Diag2005.PDX)=104 Or
(Diag2005.PDX)=108 Or (Diag2005.PDX)=109 Or (Diag2005.PDX)=110))
ORDER BY Diag2005.PA;


PA PDX Tot_PA
13 102 390
13 103 39
13 104 246
13 108 65
13 109 3
13 110 2
16 102 84
16 103 5
16 104 44
16 108 10
18 102 532
18 103 40
18 104 298
18 108 35
18 109 2
22 102 75
22 103 6
22 104 34
22 108 2

 

[Guest]

Where did ASC-PA & SIL-PA come from?

 

[Guest]

That is just what I will call the resultant sum of each of those items I
provided , so the sum of PDX diagnoses 102+103 by PA I want to call "ASC-PA".

 

[Guest]

If I understand you correctly you need to create three views. One that only
sums the ASC-PA, one that sums the SIL-PA and a third that CROSS JOINS the
two. In the third you will need to add a function that ratios the two
desired fields.

 

[Guest]

Yes...at least I think so...LOL. But I can't seem to get this to work.

 

[John Spencer (MVP)]

Not necessarily the best way, but this will probably work


SELECT Diag2005.PA,
Abs(Sum(Diag2005.PDX In (102,103))) as [ASC-PA],
Abs(Sum(Diag2005.PDX in (104,108,109,110))) as [SIL-PA],
Count(Diag2005.PDX) AS Tot_PA,
Abs(Sum(Diag2005.PDX In (102,103)))/Abs(Sum(Diag2005.PDX in (104,108,109,110)))
as Ratio
FROM Diag2005
WHERE Diag2005.PDX in (102,103,104,108,109,110)
GROUP BY Diag2005.PA
ORDER BY Diag2005.PA;

 

[Guest]

By view, do you mean separate queries?

If I understand you correctly you need to create three views. One that only
sums the ASC-PA, one that sums the SIL-PA and a third that CROSS JOINS the
two. In the third you will need to add a function that ratios the two
desired fields.

 

[Guest]

Yes, bravo! That is it exactly. Thank you so much!

Not necessarily the best way, but this will probably work


SELECT Diag2005.PA,
Abs(Sum(Diag2005.PDX In (102,103))) as [ASC-PA],
Abs(Sum(Diag2005.PDX in (104,108,109,110))) as [SIL-PA],
Count(Diag2005.PDX) AS Tot_PA,
Abs(Sum(Diag2005.PDX In (102,103)))/Abs(Sum(Diag2005.PDX in (104,108,109,110)))
as Ratio
FROM Diag2005
WHERE Diag2005.PDX in (102,103,104,108,109,110)
GROUP BY Diag2005.PA
ORDER BY Diag2005.PA;

I have a query that extracts specific information out of my Diag2005 table.
The sql view is provided, and the resulting output. What I need to be able to
do is get a sum of PDX by PA, ie. I need to have the sum of PDX diagnoses
102+103 by PA (ASC-PA), sum of PDX diagnoses 104+108+109+110 by PA (SIL-PA),
and then be able to provide a ratio by PA ASC-PA/SIL-PA. How do I do this?

SELECT Diag2005.PA, Diag2005.PDX, Count(Diag2005.PDX) AS Tot_PA
FROM Diag2005
GROUP BY Diag2005.PA, Diag2005.PDX
HAVING (((Diag2005.PDX)=102 Or (Diag2005.PDX)=103 Or (Diag2005.PDX)=104 Or
(Diag2005.PDX)=108 Or (Diag2005.PDX)=109 Or (Diag2005.PDX)=110))
ORDER BY Diag2005.PA;

PA PDX Tot_PA
13 102 390
13 103 39
13 104 246
13 108 65
13 109 3
13 110 2
16 102 84
16 103 5
16 104 44
16 108 10
18 102 532
18 103 40
18 104 298
18 108 35
18 109 2
22 102 75
22 103 6
22 104 34
22 108 2

 

[Guest]

One final question...LOL....maybe....

How can I set conditional formatting to highlight in red a ratio that say
for example is greater than 2.0?

Yes, bravo! That is it exactly. Thank you so much!

Not necessarily the best way, but this will probably work


SELECT Diag2005.PA,
Abs(Sum(Diag2005.PDX In (102,103))) as [ASC-PA],
Abs(Sum(Diag2005.PDX in (104,108,109,110))) as [SIL-PA],
Count(Diag2005.PDX) AS Tot_PA,
Abs(Sum(Diag2005.PDX In (102,103)))/Abs(Sum(Diag2005.PDX in (104,108,109,110)))
as Ratio
FROM Diag2005
WHERE Diag2005.PDX in (102,103,104,108,109,110)
GROUP BY Diag2005.PA
ORDER BY Diag2005.PA;

I have a query that extracts specific information out of my Diag2005 table.
The sql view is provided, and the resulting output. What I need to be able to
do is get a sum of PDX by PA, ie. I need to have the sum of PDX diagnoses
102+103 by PA (ASC-PA), sum of PDX diagnoses 104+108+109+110 by PA (SIL-PA),
and then be able to provide a ratio by PA ASC-PA/SIL-PA. How do I do this?

SELECT Diag2005.PA, Diag2005.PDX, Count(Diag2005.PDX) AS Tot_PA
FROM Diag2005
GROUP BY Diag2005.PA, Diag2005.PDX
HAVING (((Diag2005.PDX)=102 Or (Diag2005.PDX)=103 Or (Diag2005.PDX)=104 Or
(Diag2005.PDX)=108 Or (Diag2005.PDX)=109 Or (Diag2005.PDX)=110))
ORDER BY Diag2005.PA;

PA PDX Tot_PA
13 102 390
13 103 39
13 104 246
13 108 65
13 109 3
13 110 2
16 102 84
16 103 5
16 104 44
16 108 10
18 102 532
18 103 40
18 104 298
18 108 35
18 109 2
22 102 75
22 103 6
22 104 34
22 108 2

 

[John Spencer (MVP)]

In a query, I don't think you can. Not sure, because I wouldn't do it in a
query even if it were possible. I would set conditional formatting in a form or

One final question...LOL....maybe....

How can I set conditional formatting to highlight in red a ratio that say
for example is greater than 2.0?

Yes, bravo! That is it exactly. Thank you so much!

Not necessarily the best way, but this will probably work


SELECT Diag2005.PA,
Abs(Sum(Diag2005.PDX In (102,103))) as [ASC-PA],
Abs(Sum(Diag2005.PDX in (104,108,109,110))) as [SIL-PA],
Count(Diag2005.PDX) AS Tot_PA,
Abs(Sum(Diag2005.PDX In (102,103)))/Abs(Sum(Diag2005.PDX in (104,108,109,110)))
as Ratio
FROM Diag2005
WHERE Diag2005.PDX in (102,103,104,108,109,110)
GROUP BY Diag2005.PA
ORDER BY Diag2005.PA;

jsullinger wrote:

I have a query that extracts specific information out of my Diag2005 table.
The sql view is provided, and the resulting output. What I need to be able to
do is get a sum of PDX by PA, ie. I need to have the sum of PDX diagnoses
102+103 by PA (ASC-PA), sum of PDX diagnoses 104+108+109+110 by PA (SIL-PA),
and then be able to provide a ratio by PA ASC-PA/SIL-PA. How do I do this?

SELECT Diag2005.PA, Diag2005.PDX, Count(Diag2005.PDX) AS Tot_PA
FROM Diag2005
GROUP BY Diag2005.PA, Diag2005.PDX
HAVING (((Diag2005.PDX)=102 Or (Diag2005.PDX)=103 Or (Diag2005.PDX)=104 Or
(Diag2005.PDX)=108 Or (Diag2005.PDX)=109 Or (Diag2005.PDX)=110))
ORDER BY Diag2005.PA;

PA PDX Tot_PA
13 102 390
13 103 39
13 104 246
13 108 65
13 109 3
13 110 2
16 102 84
16 103 5
16 104 44
16 108 10
18 102 532
18 103 40
18 104 298
18 108 35
18 109 2
22 102 75
22 103 6
22 104 34
22 108 2

 

[Guest]

Ah...gotcha. Thanks again.

In a query, I don't think you can. Not sure, because I wouldn't do it in a
query even if it were possible. I would set conditional formatting in a form or

One final question...LOL....maybe....

How can I set conditional formatting to highlight in red a ratio that say
for example is greater than 2.0?

Yes, bravo! That is it exactly. Thank you so much!

:

Not necessarily the best way, but this will probably work


SELECT Diag2005.PA,
Abs(Sum(Diag2005.PDX In (102,103))) as [ASC-PA],
Abs(Sum(Diag2005.PDX in (104,108,109,110))) as [SIL-PA],
Count(Diag2005.PDX) AS Tot_PA,
Abs(Sum(Diag2005.PDX In (102,103)))/Abs(Sum(Diag2005.PDX in (104,108,109,110)))
as Ratio
FROM Diag2005
WHERE Diag2005.PDX in (102,103,104,108,109,110)
GROUP BY Diag2005.PA
ORDER BY Diag2005.PA;

jsullinger wrote:

I have a query that extracts specific information out of my Diag2005 table.
The sql view is provided, and the resulting output. What I need to be able to
do is get a sum of PDX by PA, ie. I need to have the sum of PDX diagnoses
102+103 by PA (ASC-PA), sum of PDX diagnoses 104+108+109+110 by PA (SIL-PA),
and then be able to provide a ratio by PA ASC-PA/SIL-PA. How do I do this?

SELECT Diag2005.PA, Diag2005.PDX, Count(Diag2005.PDX) AS Tot_PA
FROM Diag2005
GROUP BY Diag2005.PA, Diag2005.PDX
HAVING (((Diag2005.PDX)=102 Or (Diag2005.PDX)=103 Or (Diag2005.PDX)=104 Or
(Diag2005.PDX)=108 Or (Diag2005.PDX)=109 Or (Diag2005.PDX)=110))
ORDER BY Diag2005.PA;

PA PDX Tot_PA
13 102 390
13 103 39
13 104 246
13 108 65
13 109 3
13 110 2
16 102 84
16 103 5
16 104 44
16 108 10
18 102 532
18 103 40
18 104 298
18 108 35
18 109 2
22 102 75
22 103 6
22 104 34
22 108 2