[AccessD] Counting multiple items in a field

Thanks again. I ended up using the InStr function to pick out each code in the field for each record then added them to a temp table as an individual record. This is how they should be stored anyway in a normal inviornment. Then I can just do a grouping query.
 








May God bless you beyond your imagination!
Lonnie Johnson
ProDev, Professional Development of MS Access Databases
Visit me at ==> http://www.prodev.us






 



----- Original Message ----
From: Gustav Brock <Gustav at cactus.dk>
To: accessd at databaseadvisors.com
Sent: Wednesday, February 14, 2007 7:25:43 AM
Subject: Re: [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.


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


 
____________________________________________________________________________________
Don't pick lemons.
See all the new 2007 cars at Yahoo! Autos.
http://autos.yahoo.com/new_cars.html