Counting alphanumeric data

R

Rab

I have a field that is alphanumeric and contains two types of claims data
auto and workers comp. The claim number field has claim numbers that start
with either AUXXXX for auto and WCXXXX for workers comp. Is it possible to
only count the auto claims by designating the alphanumeric format in the
count function? The query I am using was created by combing the two sources
(auto and wc) into one table by location.
 
K

KARL DEWEY

Try this --
SELECT xxx, yyy, Sum(IIF([claim number] Like "AUX*", 1, 0)) AS AutoClaims,
Sum(IIF([claim number] Like "WC*", 1, 0)) AS WCompClaims
FROM YourTable
GROUP BY xxx, yyy;
 
R

Rab

Thanks it did work to separate the two data, however I have a cost field that
shows only the combined of the two claims type. How do I get the costs
separated to correspond or group with the AutoClaims and WCompClaims fields?

KARL DEWEY said:
Try this --
SELECT xxx, yyy, Sum(IIF([claim number] Like "AUX*", 1, 0)) AS AutoClaims,
Sum(IIF([claim number] Like "WC*", 1, 0)) AS WCompClaims
FROM YourTable
GROUP BY xxx, yyy;

--
Build a little, test a little.


Rab said:
I have a field that is alphanumeric and contains two types of claims data
auto and workers comp. The claim number field has claim numbers that start
with either AUXXXX for auto and WCXXXX for workers comp. Is it possible to
only count the auto claims by designating the alphanumeric format in the
count function? The query I am using was created by combing the two sources
(auto and wc) into one table by location.
 
K

KARL DEWEY

Try this --
SELECT xxx, yyy, Sum(IIF([claim number] Like "AUX*", 1, 0)) AS AutoClaims,
Sum(IIF([claim number] Like "AUX*", [Cost_Field], 0)) AS AutoClaimsCost,
Sum(IIF([claim number] Like "WC*", [Cost_Field], 0)) AS WCompClaims,
Sum(IIF([claim number] Like "WC*", 1, 0)) AS WCompClaimsCost
FROM YourTable
GROUP BY xxx, yyy;

--
Build a little, test a little.


Rab said:
Thanks it did work to separate the two data, however I have a cost field that
shows only the combined of the two claims type. How do I get the costs
separated to correspond or group with the AutoClaims and WCompClaims fields?

KARL DEWEY said:
Try this --
SELECT xxx, yyy, Sum(IIF([claim number] Like "AUX*", 1, 0)) AS AutoClaims,
Sum(IIF([claim number] Like "WC*", 1, 0)) AS WCompClaims
FROM YourTable
GROUP BY xxx, yyy;

--
Build a little, test a little.


Rab said:
I have a field that is alphanumeric and contains two types of claims data
auto and workers comp. The claim number field has claim numbers that start
with either AUXXXX for auto and WCXXXX for workers comp. Is it possible to
only count the auto claims by designating the alphanumeric format in the
count function? The query I am using was created by combing the two sources
(auto and wc) into one table by location.
 

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

Top