transform data to string (or something like that (-: )

  • Thread starter Thread starter Wouter
  • Start date Start date
W

Wouter

Hi,

I hope this is a coding problem and not a macro or query
problem. I have to analyse medical data and I need to
transform a huge amount of data into something I can
understand.

I have got 2 tables:
Table A with a unique patient id and some other basic data.
Table B with discrete variables telling me for every minute
for 24 hours if the blood pressure is l(ow), n(ormal) or
h(igh) and the id (from table A) linking these values to a
patient, and a time index telling me how long the patient
is present at the intensive care unit. (up to 24 hours)

My problem is the following:
I need to transform the subsequent blood pressure values
into a string with the following format. If you have the
following entries:
id blood pressure time on icu (in hours)
6 low 6
6 low 6
6 low 6
6 low 6
6 low 6
6 normal 6
6 normal 6
6 high 6
6 high 7
6 high 7
it should be transformed into the String:
l5,n2,h3

In other words a value in combination with the number of
equal subsequent values. I have to do this for both the
total 24 hours as well as the first 6 hours where the
string for the 6 hour problem (for this example) should be
l5,n2,h1.

The values described above are the result of a query. Can
anybody tell me how to transform these results into the
result I need? And how to implement it into my query.

Thanks,
Wouter
 
I'm not sure about your example. I would do it with vba using a control
break. it's an old practice used by cobol guys. basically you accumulate
values in variables until a key changes, then write to a file the values you
accumulated.

With a little clearer example or sample i may be able to be more detailed.

thanks
 
In other words a value in combination with the number of
equal subsequent values. I have to do this for both the
total 24 hours as well as the first 6 hours where the
string for the 6 hour problem (for this example) should be
l5,n2,h1.
Click to expand...

Like Lance, I'm not quite sure about your output. If you just need the
number of l, n and h records then it's a trivial function calling a handful
of DCount() calls. On the other hand, if this string continues into
something like

N4,L3,VL3,L2,N1,L2,N1

etc., then I'd look seriously at bypassing that stage altogether. Such a
long string is hardly human-intelligible, so it's probably not a bad idea
to feed directly into what ever is the next stage; perhaps even with
superior results. My guess is that it ends up as an area-under-the-curve or
mean-deviation or total-hours-low, which you can get more easily, more
accurately and more precisely from the original data. This kind of data
looks ideal for someone drawing it up by hand from physical records, but
once the data are digital there is precious little reason to continue to
use the same methods.

All the best


Tim F
 
Back
Top