Data-type issue

[kejo41]

The clinic I work for regularly gets data imported from the local lab. The
problem with the import is that the field that contains the "value" of the
test results is "memo" type, as there are many different types of information
in this field (ie numbers, Y/N, even some sentences.

E.g

Identifier Test-Type Test-Value
1 Platelet count 350
2 CD4 Count 0.235
3 cell Conc Complete
4 Platelet count 210
5 HEPATITIS C PCR/RNA Processed

You get the picture, apparentlly they have no intention of fixing this any
time soon. However, It is my job to do the queries to keep everyone happy.

The one saving grace is that I know that each test type will always have the
same type of test value (ie the test value for platelet count is always a
number while cell conc is always text). I want for example to query patients
who have a platelet count of less than 100. When I try and do this in a query
I obviously get data-type errors. I have tried to CInt(), CStr(), to no avail.

I hope this makes sense. Any help would be much appreciated.

Thanks.

 

[Tom Wickerath]

Hi Kejo,

Looks to me like you are going to need to write some custom VBA code to get
the data from the memo field into a properly normalized format for querying /
reporting. Can you always rely on some word, such as "Identifier" with the
test results starting on the following lines? Is it possible for sentences to
be in the middle of this data, or are the sentences either before or after
all of the data?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[kejo41]

Hi Tom,

Thanks for the response, Unfortunatley it seems like in the Test-Value field
anything goes. The only real constant is what I described earlier, most test
types will always have a specific data-type in the test value field (there
are a couple of exceptions). In this case the identifier field is simply the
patient identifier.

 

[Tom Wickerath]

Hi Ken,

WHERE Test-Type = "Platelet count"
AND Val([Test-Value]) < 100
AND IsNumeric([Test-Value])

Doesn't this assume that the OP has a field named "Test-Type"? My
understanding is that ALL of his data is in one memo-type field.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

The Val function won't produce an error if applied to a non-numeric text
value; it will return a zero in fact. So for the example you've cited you
could query for:

WHERE Test-Type = "Platelet count"
AND Val([Test-Value]) < 100
AND IsNumeric([Test-Value])

Calling the IsNumeric function won't actually be necessary if, as you say,
the values for "Platelet count" are always numeric, but you might want to
leave it in just in case this isn't always so.

There can be exceptions which the IsNumeric function wouldn't catch, e.g.
"123E4" is a number; its 123 multiplied by 10 to the power of 4, but I don't
imagine that's very likely in your case.

Ken Sheridan
Stafford, England

 

[kejo41]

Hi Ken,

You are right each column heading represents a different field. The "problem
field" is only the test-value field (which containes everything under the
sun). I will certainly try this tommorrow at work.

Tom: Sorry for the poor wording, I appreciate your input as well.


KeJo41

I interpret it differently on the basis that:

(a) they'd included column headings.
(b) they'd said 'the field that contains the "value" of the
test results', with "value" apparently emphasised.

but you could be right. Hopefully they'll clarify.

Ken Sheridan
Stafford, England

Hi Ken,

WHERE Test-Type = "Platelet count"
AND Val([Test-Value]) < 100
AND IsNumeric([Test-Value])

Doesn't this assume that the OP has a field named "Test-Type"? My
understanding is that ALL of his data is in one memo-type field.

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

The Val function won't produce an error if applied to a non-numeric text
value; it will return a zero in fact. So for the example you've cited you

[quoted text clipped - 14 lines]

Ken Sheridan
Stafford, England

--



.