Count multiple items in a field

[LonnieJohnson]

I have a field that is horribly constructed. It looks like so...


RECORDID FIELD1
1 AAA, BBB
2 BBB
3 AAA, BBB, CCC
4 BBB, CCC
5 AAA, CCC
6 CCC, BBB

Is there a quick way to count all the AAA's and all the BBB's without reconstructing (normalizing) the table? This would entail redoing their input forms and reports. Time is of the essence. Thanks.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[SuicidED]

Hi

SELECT Count(Tablename.Field1) AS CountOfAAA
FROM Tablename
WHERE (((Tablename.Field1) Like "*AAA*"));

and so on

 

[LonnieJohnson]

Thanks for the quick response.

Actually there are about 50 of the AAA type codes and I need them to print out in the footer of a report.

That would seem to be a very long union query and also would entail modifying it when a new code came into play. I wanted something more dynamic.

Thanks again.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[CautionMP]

LonnieJohnson,
Write a User Defined Function?

If your looking at doing this for all the records in a report and you are comfortable with code this might be the way to go. Post back if you want to try this route.

Q: With 50 odd codes do you have enough space in your footer for a code summary?

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[rubbernilly]

I agree that it sounds like a function.

Parse out the field info using the Split() function, and write the unique results back to a dynamic array. Write each code only once (so if AAA already exists, you would not write it again).

Then step through that array to do whatever sort of querying/totalling you need to, and do it on a dynamic basis. Construct your SQL in the code, allowing for references to your array... like this:

dim a as integer
dim MyArray() as String

[green]'code to redim and fill the array omitted[/green]

If len(join(MyArray)=0 then exit sub

for a = 0 to Ubound(MyArray)
  CurrentDB.Execute "SELECT Count(Tablename.Field1) AS CountOfCode " & _
    "FROM Tablename WHERE Tablename.Field1 Like '*'" & MyArray(a) & "'*'"

[green]'code to use this value omitted[/green]
Next a

You could build a long string with your returned information and put that string into the footer of your report.

Hope this helps.

 

[PHV]

If you follow the UDF way you may consider a Scripting.Dictionary object to collect the codes and count them in a single pass.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LonnieJohnson]

Thanks guys. I ended up creatking a fuction that used InStr to pick out each code in the field for each record a and put them in to a temp table as an individual record.

This is how they should have been stored anyway in a normal enviornment.

From there an aggregate query is all I need.



ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!