Would like to tabulate occurrences of words in a table

  • Thread starter Thread starter Peter Bungart
  • Start date Start date
P

Peter Bungart

I have a text field in Access (XP) that lists the occurrences of various
plants, which are delimited within the field by commas (e.g., cactus,
mesquite, yucca, cliffrose). Is there a way to tabulate the occurrence of
each type of plant within the field, essentially allowing me to consider the
text as data which can be analyzed across a geographic area? Would Word or
Excel be a better tool for this? I hope my question is clear--its a little
difficult to articulate.

Thanks,
Peter
 
Peter,
Why not use the Split function from VBA to break the delimited field out
into a table (you could call it Plants) that has a one to many relationship
with you geographic area table? Then you could write a query using the SQL
aggregate function "Count" sum the occurences of a given plant.

A field in a table that contains more than one value violates the 1st normal
form of table integrity and generally leads to the type of problem that you
are experiencing. In fact, to completely eliminate redundancy (in the case
you describe) you would need at least two more tables, one that lists each
plant, and then a many to many table between the geographic area table and
plants. But you could reach at least the 1st normal form by doing what is
described in the 1st paragraph.

Also, I believe that cross posting to more than one news group is considered
bad form.
 
Thank you Jeffrey, and my apologies for poor etiquette. Frankly, I wasn't
even sure which newsgroup was most appropriate, so I was just trying to
cover my bases.
 
Peter,

If you create a table of keywords, you could create a query that would
allow you to identify which records contained each of the keywords in
your plant field. It might look something like:

SELECT K.Plant, T.ID, T.Plants
FROM Keywords K, yourTable T
WHERE T.Plants LIKE '*' & K.Plant & '*'

The results might look like:
Plant ID Plants
cactus 1 cactus, mesquite, yucca, cliffrose
mesquite 1 cactus, mesquite, yucca, cliffrose
yucca 1 cactus, mesquite, yucca, cliffrose
cliffrose 1 cactus, mesquite, yucca, cliffrose
cactus 2 cactus, mesquite
mesquite 2 cactus, mesquite
yucca 3 yucca, mesquite
mesquite 3 yucca, mesquite

--
HTH

Dale Fye


I have a text field in Access (XP) that lists the occurrences of
various
plants, which are delimited within the field by commas (e.g., cactus,
mesquite, yucca, cliffrose). Is there a way to tabulate the occurrence
of
each type of plant within the field, essentially allowing me to
consider the
text as data which can be analyzed across a geographic area? Would
Word or
Excel be a better tool for this? I hope my question is clear--its a
little
difficult to articulate.

Thanks,
Peter
 
Peter

One solution would be to use the Split() function (use comma as your
delimiter marker) to extract each of the words out. Insert the words into a
temporary table. Use a query to group and count the frequency of the words.

HTH

--

Rob
FMS, Inc.

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Need to launch your application in the appropriate version of Access?
http://www.fmsinc.com/Products/startup/index.asp

Need software tools for Access, VB, SQL or .NET?
http://www.fmsinc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
There is no objection to cross-posting. Multi-posting (posting the same
question *separately* to more than one NG) is what is deprecated.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://www.mvps.org/word
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.
 
Thanks Suzanne, I understand the difference, and I guess the distinction is
that in a cross post a reply can be seen in all groups.
 
Precisely. The objection to multi-posting is that a reader seeing a question
in one group may waste time answering it, unaware that a satisfactory answer
has already been posted in another group. So instead we waste time writing
"See reply in x group" to warn readers that a question has already been
answered. Not to mention that the OP must track down answers in several NGs.
Moreover, all these posts are separately archived in Google, where a variety
of useful and correct answers will be spread over several threads. Worse
still, some of these threads may contain *incorrect* answers that have not
been challenged or corrected.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://www.mvps.org/word
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.
 
Back
Top