Count Question

[NeonSky via AccessMonster.com]

Hello,

Quick question.

Is there any way to do a count referencing a field like below and outputting
the number to another field, for example...

FieldX
123, 145, 256

Would equal a count of 3

FieldX
254, 655, 673, 4363

Would equal a count of 4

Please note the commas do exist in the field, and each string will be
distinct.

Thank you! And Happy Holidays!

 

[Jeff Boyce]

A field with multiple values in it, separated by commas, is not an optimal
relational design.

If your situation calls for some variable number of values associated with
some other record/row, use a one-to-many related table. This way, you get
the best use of Access' relationally-oriented features and functions, such
as a Totals query that lets you Count how many there are.

While it would be possible to create a procedure that steps through the
contents of your FieldX, summing the number of terms separated by commas,
this is a labor-intensive proposition, especially when compared to a simple
query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[NeonSky via AccessMonster.com]

Hello Jeff,

I certainly agree with your analysis. Though given the context of the output
process it is required that a single record row contain multiple values in a
single field. Our source data is properly indexed.

I dont know how to do it, though wouldnt just counting the number of commas
in the field and then adding a +1 to that count give us the number we are
looking for? Would you know how to do that or perhaps something else?

Thank you for your time and consideration!

A field with multiple values in it, separated by commas, is not an optimal
relational design.

If your situation calls for some variable number of values associated with
some other record/row, use a one-to-many related table. This way, you get
the best use of Access' relationally-oriented features and functions, such
as a Totals query that lets you Count how many there are.

While it would be possible to create a procedure that steps through the
contents of your FieldX, summing the number of terms separated by commas,
this is a labor-intensive proposition, especially when compared to a simple
query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,

[quoted text clipped - 18 lines]

Thank you! And Happy Holidays!

 

[Jeff Boyce]

No guarantee this is an "elegant" solution...

I'm not totally versed in the many wonderful functions/features Access
offers, so I'd probably have to "count" the commas by using a loop. I'd
open a recordset consisting of the field and the record's ID, go to the
first record('s field) and start a For/Next loop using the Mid() function to
step through the characters. Each time a comma showed up, add one to the
count of commas for that row's field.

Hopefully other readers can offer a whiz-bang function in Access that can do
this without the programming...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello Jeff,

I certainly agree with your analysis. Though given the context of the
output
process it is required that a single record row contain multiple values in
a
single field. Our source data is properly indexed.

I dont know how to do it, though wouldnt just counting the number of
commas
in the field and then adding a +1 to that count give us the number we are
looking for? Would you know how to do that or perhaps something else?

Thank you for your time and consideration!

A field with multiple values in it, separated by commas, is not an optimal
relational design.

If your situation calls for some variable number of values associated with
some other record/row, use a one-to-many related table. This way, you get
the best use of Access' relationally-oriented features and functions, such
as a Totals query that lets you Count how many there are.

While it would be possible to create a procedure that steps through the
contents of your FieldX, summing the number of terms separated by commas,
this is a labor-intensive proposition, especially when compared to a
simple
query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,

[quoted text clipped - 18 lines]

Thank you! And Happy Holidays!

 

[NeonSky via AccessMonster.com]

Thanks Jeff, I will read into loops/counters and see what I can come up with
as I am new to many of accesse's more intermediate/advanced functionality
that requires more of a manual touch. Thanks!

No guarantee this is an "elegant" solution...

I'm not totally versed in the many wonderful functions/features Access
offers, so I'd probably have to "count" the commas by using a loop. I'd
open a recordset consisting of the field and the record's ID, go to the
first record('s field) and start a For/Next loop using the Mid() function to
step through the characters. Each time a comma showed up, add one to the
count of commas for that row's field.

Hopefully other readers can offer a whiz-bang function in Access that can do
this without the programming...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello Jeff,

[quoted text clipped - 35 lines]

 

[Marshall Barton]

Is there any way to do a count referencing a field like below and outputting
the number to another field, for example...

FieldX
123, 145, 256

Would equal a count of 3

FieldX
254, 655, 673, 4363

Would equal a count of 4

Please note the commas do exist in the field, and each string will be
distinct.

Len(FieldX) - Len(Replace(FieldX, ",", "")) + 1

 

[NeonSky via AccessMonster.com]

Hello Marshall, thank you for your response. Though my outputs are not quite
correct, for your consideration....

FieldX
123, 145, 256

is equaling 5

FieldX
254, 655

is equaling 3

Is there something else I should do?

Thank you!

Is there any way to do a count referencing a field like below and outputting
the number to another field, for example...

[quoted text clipped - 11 lines]

Please note the commas do exist in the field, and each string will be
distinct.

Len(FieldX) - Len(Replace(FieldX, ",", "")) + 1

 

[NeonSky via AccessMonster.com]

Wait I fixed it. I had a space in my len function after the comma....Thank
you so much, you guys are great!

Hello Marshall, thank you for your response. Though my outputs are not quite
correct, for your consideration....

FieldX
123, 145, 256

is equaling 5

FieldX
254, 655

is equaling 3

Is there something else I should do?

Thank you!
[quoted text clipped - 3 lines]

Len(FieldX) - Len(Replace(FieldX, ",", "")) + 1

 

[Jeff Boyce]

When you post the same question to different newsgroups, you discourage
folks from answering your posts in the future.

Not only does posting this way result in more than one volunteer spending
time and effort on a question that may have already been resolved, but YOU
have to look in multiple places to see if you have an answer.

If you feel you must ask in more than one group (rarely necessary), select
the groups in the "To:" ("Newsgroups:") section. Then, an answer in one
shows up in the other(s).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff, I will read into loops/counters and see what I can come up
with
as I am new to many of accesse's more intermediate/advanced functionality
that requires more of a manual touch. Thanks!

No guarantee this is an "elegant" solution...

I'm not totally versed in the many wonderful functions/features Access
offers, so I'd probably have to "count" the commas by using a loop. I'd
open a recordset consisting of the field and the record's ID, go to the
first record('s field) and start a For/Next loop using the Mid() function
to
step through the characters. Each time a comma showed up, add one to the
count of commas for that row's field.

Hopefully other readers can offer a whiz-bang function in Access that can
do
this without the programming...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello Jeff,

[quoted text clipped - 35 lines]

Thank you! And Happy Holidays!

 

[John Spencer]

How about using this expression?

Len([TheField]) - Len(Replace([TheField,",","")) + 1

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

No guarantee this is an "elegant" solution...

I'm not totally versed in the many wonderful functions/features Access
offers, so I'd probably have to "count" the commas by using a loop. I'd
open a recordset consisting of the field and the record's ID, go to the
first record('s field) and start a For/Next loop using the Mid() function to
step through the characters. Each time a comma showed up, add one to the
count of commas for that row's field.

Hopefully other readers can offer a whiz-bang function in Access that can do
this without the programming...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello Jeff,

I certainly agree with your analysis. Though given the context of the
output
process it is required that a single record row contain multiple values in
a
single field. Our source data is properly indexed.

I dont know how to do it, though wouldnt just counting the number of
commas
in the field and then adding a +1 to that count give us the number we are
looking for? Would you know how to do that or perhaps something else?

Thank you for your time and consideration!

A field with multiple values in it, separated by commas, is not an optimal
relational design.

If your situation calls for some variable number of values associated with
some other record/row, use a one-to-many related table. This way, you get
the best use of Access' relationally-oriented features and functions, such
as a Totals query that lets you Count how many there are.

While it would be possible to create a procedure that steps through the
contents of your FieldX, summing the number of terms separated by commas,
this is a labor-intensive proposition, especially when compared to a
simple
query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,

[quoted text clipped - 18 lines]
Thank you! And Happy Holidays!