Would like to tabulate occurrences of words in a table

[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

[Jeffrey R. Bailey]

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.

--
Jeffrey R. Bailey
"Peter Bungart" <pb******@qwest.net> wrote in message
news:fO***************@news.uswest.net...

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 Bungart]

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.

"Jeffrey R. Bailey" <mr**********@yahoo.com> wrote in message
news:Gr********************@twister.tampabay.rr.co m...

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.

--
Jeffrey R. Bailey
"Peter Bungart" <pb******@qwest.net> wrote in message
news:fO***************@news.uswest.net...

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

[Dale Fye]

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
"Peter Bungart" <pb******@qwest.net> wrote in message
news:fO***************@news.uswest.net...
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

[RobFMS]

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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

"Peter Bungart" <pb******@qwest.net> wrote in message
news:fO***************@news.uswest.net...

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

[Pavel Romashkin]

I think you could approach this in a simpler way than splitting the
data, which was suggested above.
You said that you want to just get the *occurrence*, i.e. the number of
cases the plant is mentioned in the table per geographic area. There is
no need to further process table data for that.
Assuming that myTable has fields [PlantsListing] and [Area] then you
should be able to do this:

SELECT Count(*) AS NumOfCacti FROM myTable WHERE Area = "Southeast" AND
PlantsListing LIKE "*cact*"

Of course the LIKE parameter as well as the area filter should be
accessible via a form for convenience.
If you need to do firther processing of those records (to see what other
plants there are, for instance) then work on the recordset returned by
the WHERE AND LIKE combination.
Good luck,
Pavel

Peter Bungart wrote:

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

[Suzanne S. Barnhill]

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.

"Peter Bungart" <pb******@qwest.net> wrote in message
news:VL****************@news.uswest.net...

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.

"Jeffrey R. Bailey" <mr**********@yahoo.com> wrote in message
news:Gr********************@twister.tampabay.rr.co m...

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.

--
Jeffrey R. Bailey
"Peter Bungart" <pb******@qwest.net> wrote in message
news:fO***************@news.uswest.net...

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

[Jeffrey R. Bailey]

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.

--
Jeffrey R. Bailey
"Suzanne S. Barnhill" <sb*******@mvps.org> wrote in message
news:Ov**************@TK2MSFTNGP09.phx.gbl...

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.

"Peter Bungart" <pb******@qwest.net> wrote in message
news:VL****************@news.uswest.net...

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.

"Jeffrey R. Bailey" <mr**********@yahoo.com> wrote in message
news:Gr********************@twister.tampabay.rr.co m...

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.

--
Jeffrey R. Bailey
"Peter Bungart" <pb******@qwest.net> wrote in message
news:fO***************@news.uswest.net...
> 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
>
>
>

[Suzanne S. Barnhill]

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.

"Jeffrey R. Bailey" <mr**********@yahoo.com> wrote in message
news:V5******************@twister.tampabay.rr.com. ..

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.

--
Jeffrey R. Bailey
"Suzanne S. Barnhill" <sb*******@mvps.org> wrote in message
news:Ov**************@TK2MSFTNGP09.phx.gbl...

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.

"Peter Bungart" <pb******@qwest.net> wrote in message
news:VL****************@news.uswest.net...

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.

"Jeffrey R. Bailey" <mr**********@yahoo.com> wrote in message
news:Gr********************@twister.tampabay.rr.co m...
> 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.
>
> --
> Jeffrey R. Bailey
> "Peter Bungart" <pb******@qwest.net> wrote in message
> news:fO***************@news.uswest.net...
> > 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
> >
> >
> >
>
>