Another problem. Start with a table with 3 numeric fields - Item, Before, After.

If I run this query : | Item | Before | After | | CALC count | <3, CALC count|<3, CALC count|

The query will count the number of Item's and present that next to the number of Before's and After's with a value less than 3 - from this I can make a nice little bar chart.

But, I also want these two queries |Item | Before| After | |CALC count| <6, CALC count| <6, CALC count|

and |Item |Before | After | |CALC count |<9, CALC count|<9, CALC count|

The problem is how to get the three queries to produce the results all on the same bar chart, with the Y axis giving the total number of Item's and the X axis comparing the three sets of Before and After totals. This is the preferred chart. Or along the lines of Before/After fields with <3, Calc count >3,<6, Calc count >6,<9,calc count

Or by defining the chart(?) but that doesn't look too hopeful. All suggestions (polite) welcome. rt

TouchΘ. ThatÆs polite French hopefully with the accent if this pastes correctly.

IÆll put the actual problem in the context of my previous example. This is part of a medical db which stores the results of two blood tests (in a range say .50 to 9.99) hence the fields ôBeforeö and ôAfterö as they refer to treatment. The ôItemö field would correspond with the patient reference number and is only included as it will show the total number of patients on the db and thus hopefully put the number of patients treated into some sort of perspective. The chart is not to show patient specific information but to illustrate the overall effectiveness of treatment and indirectly the efficacy of the needle happy buxom beauty in the slinky black leather nurses outfit. So back to my first example û the simple bar chart would show ôItemö -calc count- on the Y axis with ôBeforeö and ôAfterö on the X, both showing <3, calc count. In other words it will display the total number of patients against the number of those ôBeforeö treatment (with a blood test showing less than 3) and the number (hopefully reduced etc) ôAfterö treatment ditto. The other two queries <6 and <9 are similar and ought (should?) produce even more impressive medical statistics û although so far it appears to show the nurse is a total waste of money!!!! So, the complete chart would have a total ôItemö on the Y and all three sets of ôBeforeö and ôAfterö along the X. Does that help? I can probably get you blood test trade if you want. rt

This looks interesting -- but I don't understand what you're trying to produce. Could you please^{1} give a more complete description of the bar chart? For example, kindly^{2} describe what would happen to items with, eg, Before>3 and After<3.

-Al.

^{1} notice: polite.

^{2} more politeness.

[This message has been edited by A. I. Breveleri (edited February 12, 2001).]

You're looking at the strength of correlation between two measurements ('before' and 'after' treatment) where you hope that each 'before' measurement will most strongly correspond to a certain 'after' measurement which is higher. Correlation between two measurements is best viewed in a crosstab, or in a chart based on a crosstab.

To demonstrate the correlation, follow Nurse to the dining room, which has a checked tile floor pattern. Let us mark out a rectangular section of the floor. We will establish a row for each 'before' measurement and a column for each 'after' measurement. Naturally, if the measurements are three or four digits, we do not have enougn rows of tiles for every possibility, so we group them. What is the range of the measurements? About 0 to 12? Okay, we have about 25 clear tiles once we have folded up the chairs and tables. So the first row will collect measurements from 0 to 0.5, the second from 0.5 to 1, the third from 1 to 1.5, and so forth. While Nurse is marking out the chart and labeling the rows and columns with erasable markers, you organize your data sheets, and I'll slip out to the kitchen and gather up all the soup plates I can find.

Now, for each patient, you read off the 'before' and 'after' measurements, I will point out the proper row and column, and Nurse will stack a soup plate on that tile. As the piles of plates mount, you can very readily see why Nurse has the task of handling the plates: only her five-inch spike heels can navigate safely between the plates already placed.

When we have thus processed all the test results that you can remember, Nurse will drape a rubber bed sheet -- no, not that thing, Nurse, get one of the double-wide ones, from your private stock, there's a good girl -- over the plates. This is called a three-dimensional summary surface chart. The contours revealed by the sheet will show a ridge if there is a correlation... and there it is. Good work, Nurse.

See how the ridge runs from around the ('before'=0, 'after'=3.5) tile down to about ('before'=8.5, 'after'=12)? That means the mean observed improvement is approximately 3.5. The ridge is fairly straight, which means that the average expected amount of improvement is independent of the original condition of the patient. The ridge is higher and sharper near (0, 3.5) and lower and broader at the (8.5, 12) end, which means that the higher the before test, the less predictable the result will be for an individual patient. As Nurse is fond of saying, you can deduce much from the final condition of a rubber bed sheet.

If Nurse will take this torch (flashlight to us Yanks) and shine it just along the top of the rubber ridge, while I hold up this tablecloth on the other side, you will see that the shadow is close to a bell curve. This is the sum of all the improvements, adjusted for different Begin measurements. This is the curve you would draw when the Committee demands that you "keep it simple for us non-technical folks".

There is a great deal more to be learned here but I think perhaps we had better stop now. If Matron were to walk in at this point, I don't think she'd stand still for the mathematical explanation.

So you can rub out the marks, and set out the chairs and tables again. Nurse and I are going back to my office to discuss other possible uses for this rubber bed sheet. Don't forget to put the plates back in the kitchen.