By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,247 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

Would like to tabulate occurrences of words in a table

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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


Nov 12 '05 #3

P: n/a
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

Nov 12 '05 #4

P: n/a
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

Nov 12 '05 #5

P: n/a
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

Nov 12 '05 #6

P: n/a
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




Nov 12 '05 #7

P: n/a
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
>
>
>



Nov 12 '05 #8

P: n/a
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
> >
> >
> >
>
>




Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.