[AccessD] Counting multiple items in a field

Hi Lonnie

I'm not sure I fully understand what you wish to accomplish.
Could you provide a sample of data and of your requested output from that sample?

/gustav

>>> prodevmg at yahoo.com 14-02-2007 13:58:46 >>>
Thanks for the quick response Gustav.

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 give me a lot of columns and also would entail modifying it when a new code came into play. I wanted something more dynamic.
Thanks again.


----- Original Message ----
From: Gustav Brock <Gustav at cactus.dk>
To: accessd at databaseadvisors.com 
Sent: Wednesday, February 14, 2007 5:35:47 AM
Subject: Re: [AccessD] Counting multiple items in a field


Hi Lonnie

You could, in a query, have three expressions:

  CountA: Abs(Instr(Field1, "AAA") > 0)
  CountB: Abs(Instr(Field1, "BBB") > 0)
  CountC: Abs(Instr(Field1, "CCC") > 0)

and then find the three sums of CountA to CountC.

/gustav


>>> prodevmg at yahoo.com 14-02-2007 12:18:25 >>>
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.