Concatenate Field, Count Characters and Shorten Value

[Dean]

Hi, I have a table with non-unique identifiers. I need to take all
the values with the same ID's and combine them into one field with a
semicolon as a seperator. These values may exceed 255 characters. I
then need to count the values in the cell and see if it adds up to 240
or more and then shorten that field by cutting off the excess and
adding only "...and other". So, to recap, I need to summarise values
based on the ID field, count the characters and then cut the field off
and add the text.

I was thinking I could summarize the table somehow, then use a update
query or script to add a character count to a new field in the table
and then shorten those to 240 characters. Then update the field by
adding the text. This all said, I have absolutely no idea how to do
this! Please help.

Dean

[Salad]

Dean wrote:

Hi, I have a table with non-unique identifiers. I need to take all
the values with the same ID's and combine them into one field with a
semicolon as a seperator. These values may exceed 255 characters. I
then need to count the values in the cell and see if it adds up to 240
or more and then shorten that field by cutting off the excess and
adding only "...and other". So, to recap, I need to summarise values
based on the ID field, count the characters and then cut the field off
and add the text.

I was thinking I could summarize the table somehow, then use a update
query or script to add a character count to a new field in the table
and then shorten those to 240 characters. Then update the field by
adding the text. This all said, I have absolutely no idea how to do
this! Please help.

Dean

Have you considered a MEMO field? You can hold lots of data in a memo.
You can even display it on a form with scroll bars.

[Bob Quintal]

Dean <de**********@yahoo.comwrote in
news:11**********************@50g2000hsm.googlegro ups.com:

Hi, I have a table with non-unique identifiers. I need to take
all the values with the same ID's and combine them into one field
with a semicolon as a seperator. These values may exceed 255
characters. I then need to count the values in the cell and see
if it adds up to 240 or more and then shorten that field by
cutting off the excess and adding only "...and other". So, to
recap, I need to summarise values based on the ID field, count the
characters and then cut the field off and add the text.

I was thinking I could summarize the table somehow, then use a
update query or script to add a character count to a new field in
the table and then shorten those to 240 characters. Then update
the field by adding the text. This all said, I have absolutely no
idea how to do this! Please help.

Dean

Find the fConcatChild() function by Google search on this group,
That summarises the row, use the Left() function to limit to 240
characters.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Dean]

On Oct 9, 9:49 am, Salad <[e-mail]:

Dean wrote:

Hi, I have a table with non-unique identifiers. I need to take all
the values with the same ID's and combine them into one field with a
semicolon as a seperator. These values may exceed 255 characters. I
then need to count the values in the cell and see if it adds up to 240
or more and then shorten that field by cutting off the excess and
adding only "...and other". So, to recap, I need to summarise values
based on the ID field, count the characters and then cut the field off
and add the text.

I was thinking I could summarize the table somehow, then use a update
query or script to add a character count to a new field in the table
and then shorten those to 240 characters. Then update the field by
adding the text. This all said, I have absolutely no idea how to do
this! Please help.

Dean

Have you considered a MEMO field? You can hold lots of data in a memo.
You can even display it on a form with scroll bars.- Hide quoted text -

- Show quoted text -

Hi ...Salad.

Yes, I'm aware of the memo field type, but the thing is that it needs
to go back into software, which doesn't support memo fields. The
largest it can go there is 255 in a text field. It's quite a bugger,
otherwise it would've been a lot easier I guess.

Cheers anyway mate.

Dean

[Dean]

Hi Bob,

Right, first of all, I can't find any reference to fConcatChild under
Access Help. Do I need to add this from a library? I got the posts
on this site though, but it seems to all over the place and not really
the answer I need. Also, I need to identify the cells with more than
240 characters, so I can add the text "...and other" to the end of
it. Is there a way to count characters?

If I have a field named PARCEL_ID in a table named OWNERS_JOINED, then
how do I concatenate the OWNER_NAME field based on the PARCEL_ID
field?

Cheers mate

Dean

[rquintal]

On Oct 8, 10:39 pm, Dean <[e-mail]:

Hi Bob,

Right, first of all, I can't find any reference to fConcatChild under
Access Help. Do I need to add this from a library? I got the posts
on this site though, but it seems to all over the place and not really
the answer I need. Also, I need to identify the cells with more than
240 characters, so I can add the text "...and other" to the end of
it. Is there a way to count characters?

You will not find any reference to it in access, it's a user-defined
function written by Dev Ashish that you will need to paste into a
code
module as described on the web page where it lives.
http://www.mvps.org/access/modules/mdl0004.htm

To count characters, you just use the len() function. To cut the
string
to 240 characters use the left function.

I suggest that first you will need to create a temporary table, with
the Parcel_ID and a memo type field to hold the whole string, then
use
the temp table to trim to length and add the "...and other" to the
long
strings, then move the results to their new home.

If I have a field named PARCEL_ID in a table named OWNERS_JOINED, then
how do I concatenate the OWNER_NAME field based on the PARCEL_ID
field?

Cheers mate

Dean

The following queries will do that

INSERT into Temp1 (PARCEL_ID,OWNERS_JOINED)
SELECT DISTINCT PARCEL_ID, fConcatChild("OWNERS_JOINED",
"PARCEL_ID","OWNER_NAME","STRING",[PARCEL_ID]) FROM OWNERS_JOINED

INSERT into NewTable1 (PARCEL_ID,OWNERS_JOINED)
SELECT PARCEL_ID, IIF( LEN(OWNERS_JOINED)>240,LEFT(OWNERS_JOINED,240)
&
"...and more",OWNERS_JOINED) FROM temp1