If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count Question
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! -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
Count Question
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" u23580@uwe wrote in message news:7cfcf44a83498@uwe... 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! -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Count Question
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! Jeff Boyce wrote: 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! -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
Count Question
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 "NeonSky via AccessMonster.com" u23580@uwe wrote in message news:7cfd294a319b0@uwe... 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! Jeff Boyce wrote: 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! -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
Count Question
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! Jeff Boyce wrote: 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! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200712/1 |
#6
|
|||
|
|||
Count Question
NeonSky via AccessMonster.com wrote:
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 -- Marsh MVP [MS Access] |
#7
|
|||
|
|||
Count Question
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! Marshall Barton wrote: 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 -- Message posted via http://www.accessmonster.com |
#8
|
|||
|
|||
Count Question
Wait I fixed it. I had a space in my len function after the comma....Thank
you so much, you guys are great! NeonSky wrote: 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 - 3 lines] Len(FieldX) - Len(Replace(FieldX, ",", "")) + 1 -- Message posted via http://www.accessmonster.com |
#9
|
|||
|
|||
Count Question
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 "NeonSky via AccessMonster.com" u23580@uwe wrote in message news:7cfd788d6f18c@uwe... 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! Jeff Boyce wrote: 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! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200712/1 |
#10
|
|||
|
|||
Count Question
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 '================================================= === Jeff Boyce wrote: 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 "NeonSky via AccessMonster.com" u23580@uwe wrote in message news:7cfd294a319b0@uwe... 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! Jeff Boyce wrote: 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! -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|