Counting alphanumeric data

  • Thread starter Thread starter Rab
  • Start date Start date
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.
 
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;
 
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.
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top