need to concatenate field

[johnboy7676]

Hi, I need to concatenate a field, and sum another field, grouping the
first field.

Acct legalDescrip acres

77 Parcel-1Text 10
77 Parcel-2Text 20
77 Parcel-3Text 5
80 Parcel-1Text 11
86 Parcel-1Text 12
86 Parcel-2Text 12


The result I need:


Acct legalDescrip acres

77 Parcel-1Text;Parcel-2Text;Parcel3Text 35
80 Parcel-1Text 5
86 Parcel-1Text;Parcel-2Text 24

For some records, the total of the LegalDescriptions will exceed 255
characters, but thats ok to truncate at 255.

Thanks for any suggestions.

 

[Duane Hookom]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Marshall Barton]

Hi, I need to concatenate a field, and sum another field, grouping the
first field.

Acct legalDescrip acres

77 Parcel-1Text 10
77 Parcel-2Text 20
77 Parcel-3Text 5
80 Parcel-1Text 11
86 Parcel-1Text 12
86 Parcel-2Text 12


The result I need:


Acct legalDescrip acres

77 Parcel-1Text;Parcel-2Text;Parcel3Text 35
80 Parcel-1Text 5
86 Parcel-1Text;Parcel-2Text 24

For some records, the total of the LegalDescriptions will exceed 255
characters, but thats ok to truncate at 255.

Using the function at:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

SELECT Acct,
Concatenate("SELECT legalDescrip
FROM thetable
WHERE Acct = '" & Acct & "' ", ";") As D,
Sum(Acres) As TotalAcres
FROM thetable

 

[johnboy7676]

Using the function at:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

SELECT Acct,
Concatenate("SELECT legalDescrip
FROM thetable
WHERE Acct = '" & Acct & "' ", ";") As D,
Sum(Acres) As TotalAcres
FROM thetable

Thanks Duane, Marshall. Seems to work fine. I had seen some prev
postings from Duane Hookom regarding this, but must have missed where
to download it.

Just a side question, out of curiousity, I would have thought it was
not good to use 'concatenate', since there is built in concatenate
function?

Again, thanks

 

[Duane Hookom]

"there is built in concatenate function" I wasn't aware there was such a
function in VBA.

The function might have been better named DConcatenate() or similar.

 

[johnboy7676]

I'm probably using wrong terminology; I guess maybe "&" or "+" means
"to concatenate". Or maybe I was thinking of an excel function.

In any case, it works fine, thanks.