Combine multiple records in one field in Access report

[John Walsh]

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.

 

[Marshall Barton]

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

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/...Generic Function To Concatenate Child Records'

 

[John Walsh]

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.

 

[Marshall Barton]

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

 

[John Walsh]

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.
--
Marsh
MVP [MS Access]

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.

 

[Marshall Barton]

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