Combine multiple records in one field in Access report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Walsh - 24 Apr 2007 23:26 GMT

I'm having trouble designing a report. I have a report that sorts the data
according to "Change Order Number", however, a change order can have multiple
"Trend Numbers", "RFI Numbers", and other records associated with it and I
want all of the "Trend Numbers" (and these other records) to fit on one line.
For example, "Change Order Number 1" has 2 "Trend Numbers" associated with
it, and the report currently shows this information in two rows with all of
the other information for Change Order 1 repeating. I've done hide duplicate
values to get rid of the repeating information, but I really need the 2 Trend
Numbers to show up in the same row (separated by a comma, ex: 1, 2). Here's
how it looks:

CO Trend Number Value
1 1 $500
1 2 $500

I need it to look like this:

CO Trend Number Value
1 1,2 $500

Sorry for the formatting, its hard to do it in this box. Please help me if
you can. Thanks.

Reply to this Message

Marshall Barton - 25 Apr 2007 17:44 GMT

>I'm having trouble designing a report. I have a report that sorts the data
>according to "Change Order Number", however, a change order can have multiple
[quoted text clipped - 15 lines]
>CO Trend Number Value
>1 1,2 $500

Remove the trend table from the report's record source
query. Then use a function to concatenate the trend values.
A nice function is available at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Generic%20Funct
ion%20To%20Concatenate%20Child%20Records'

Signature

Marsh
MVP [MS Access]

Reply to this Message

John Walsh - 25 Apr 2007 20:56 GMT

Marshall,

Thanks for the advice, but that url does not work I get this message when it
opens in another window:

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

/Otherdownload.asp, line 32

Please retype the url again. Thanks.

> >I'm having trouble designing a report. I have a report that sorts the data
> >according to "Change Order Number", however, a change order can have multiple
[quoted text clipped - 20 lines]
> A nice function is available at
> http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Generic%20Funct
ion%20To%20Concatenate%20Child%20Records'

Reply to this Message

Marshall Barton - 25 Apr 2007 22:19 GMT

I can't type worth dog's breath so I copy/pasted it after
verifying that it worked.

Signature

Marsh
MVP [MS Access]

>Thanks for the advice, but that url does not work I get this message when it
>opens in another window:
[quoted text clipped - 32 lines]
>> A nice function is available at
>> http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Generic%20Funct
ion%20To%20Concatenate%20Child%20Records'

Reply to this Message

John Walsh - 26 Apr 2007 16:06 GMT

Could you maybe run through what links you went to get there on
rogersaccesslibrary.com? Again, that URL doesn't work, so I have no way of
getting to that information. Does it matter that I'm using Access 2003? It
looks like most of the info is for 97 or 2000. Please let me know. Thanks
Marshall.

> I can't type worth dog's breath so I copy/pasted it after
> verifying that it worked.
[quoted text clipped - 34 lines]
> >> A nice function is available at
> >> http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Generic%20Funct
ion%20To%20Concatenate%20Child%20Records'

Reply to this Message

Marshall Barton - 26 Apr 2007 17:09 GMT

>Could you maybe run through what links you went to get there on
>rogersaccesslibrary.com? Again, that URL doesn't work, so I have no way of
>getting to that information. Does it matter that I'm using Access 2003? It
>looks like most of the info is for 97 or 2000.

I start here:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Then click on the link:
Generic Function To Concatenate Child Records

Signature

Marsh
MVP [MS Access]

Reply to this Message