Counting alphanumeric data

[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.

 

[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;

 

[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?

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.

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.

 

[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.

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?

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.

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.