Combine multiple records in one field in Access report

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.                           
0
Utf
4/24/2007 10:26:03 PM
access.reports 4434 articles. 0 followers. Follow

5 Replies
3053 Views

Similar Articles

[PageSpeed] 11

John Walsh <John Walsh@discussions.microsoft.com> wrote:

>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/Otherdownload.asp?SampleName='Generic%20Function%20To%20Concatenate%20Child%20Records'

-- 
Marsh
MVP [MS Access]
0
Marshall
4/25/2007 4:45:16 PM
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" wrote:

> John Walsh <John Walsh@discussions.microsoft.com> wrote:
> 
> >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/Otherdownload.asp?SampleName='Generic%20Function%20To%20Concatenate%20Child%20Records'
> 
> -- 
> Marsh
> MVP [MS Access]
> 
0
Utf
4/25/2007 7:56:00 PM
I can't type worth dog's breath so I copy/pasted it after
verifying that it worked.
-- 
Marsh
MVP [MS Access]


John Walsh wrote:
>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" wrote:
>
>> John Walsh <John Walsh@discussions.microsoft.com> wrote:
>> 
>> >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/Otherdownload.asp?SampleName='Generic%20Function%20To%20Concatenate%20Child%20Records'
>> 
0
Marshall
4/25/2007 9:19:39 PM
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.

"Marshall Barton" wrote:

> I can't type worth dog's breath so I copy/pasted it after
> verifying that it worked.
> -- 
> Marsh
> MVP [MS Access]
> 
> 
> John Walsh wrote:
> >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" wrote:
> >
> >> John Walsh <John Walsh@discussions.microsoft.com> wrote:
> >> 
> >> >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/Otherdownload.asp?SampleName='Generic%20Function%20To%20Concatenate%20Child%20Records'
> >> 
> 
0
Utf
4/26/2007 3:06:01 PM
John Walsh wrote:

>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

-- 
Marsh
MVP [MS Access]
0
Marshall
4/26/2007 4:09:15 PM
Reply:

Similar Artilces:

multiple flyers per page?
Hello. I need to make a custom template with publisher. It will be one 8 1/2 by 11 paper divided horizontally into three parts so that there are three fliers that can be printed. each flier needs to be double printed so I need to make it a double sided project like a postcard or brochure layout. I can figure out text and image stuff. But I don't know how to make a basic custom project that is divided into three panels horizontally (so 8 inches by 3.83 inches roughly) Also an additional customization would divide each 3.83 by 8 inch flyer into two parts so one 8 by 11 page would ...

Business Portal Error-SQL server does not exist or access denied
Hi, We are running business portal 4.0 for one of our customer. It was running correctly, however, they have changed the SQL server port (previously it was set as default 1433). After that the business portal becomes very slow and while creating a new request (purchase requisition) if we open the item pop up; it is showing exception "SQL Server does not exist or access denied...." Can any body tell me how can I provide the new port number to business portal connection to the database. Thanks and Regards, Waliullah, Thanks for using the newsgroups. I have a...

What's happened to the findfirst function in Access 2010?
I have a working Access 2007 Application which is now failing miserably in 2010. It hinges on location the first available working document in a table runing the following VBA code: With Me.Recordset .FindFirst "([fld1] + [fld2]) = 0" If .NoMatch Then .FindLast "([fdl1] + [fld2]) <> 0" Exit Sub End If .... FURTHER PROCESSING ... What must I do to correct this? Go through a record by record search? End With In 2007, this works correctly, stopping at the appropriate record (approc. rec 1385 in the recordset...

Removing multiple users from Outlook
We did some computer switching, which involved uninstalling a copy of Office XP and replacing it with a copy of Office 2003. Now when I open Outlook, it makes me choose a user. This wasn't in the .pst file which was transferred for the other computer, but apparenly it was on this computer someplace. How can I get Outlook to simply open for the one account without that dialog box popping up? Aloha Annie, Control Panel | Mail | Show Profiles...remove any profiles you don't want. Select the one you do want and set it as the default. (if there are more than one left) -Ben-...

open two different Access reports
Hi, I was wondering if i can get some help here. I have two different reports that i want to open when a user clicks a button to view the reports for printing. Is there any way of popping them up at the same time in VBA? Thank you in advance Associates wrote: >Hi, > >I was wondering if i can get some help here. I have two different reports >that i want to open when a user clicks a button to view the reports for >printing. Is there any way of popping them up at the same time in VBA? > >Thank you in advance Yes. Call the DoCmd.OpenReport command twic...

Multiple stacked bar charts
I have a project where i have to chart the compliance of three groups over a number of months. So for each data set there is a Compliance#1 stacked upon NonCompliance#1, then Compliance#2 stacked upon NonCompliance#2 and finally, you guessed it, Compliance#3 stacked upon NonCompliance#3. When i design a chart in excel it wants to place everything on top of each other producing one column of six not the required three columns of two for each week in the display. Can anyone point me i the right direction for this one?? Hi you can achieve this effect by having two layers in your x axis the at...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

Access 2007 and Vista code problem
I am developing a database on a XP SP2 machine. On a form I have created some buttons with event procedures that run 2 queries and open a form. I sent the db to someone running Access 2007 on a Vista machine and nothing happens when she clicks the buttons and there are no messages. I know nothing about Vista so thanks in advance for any help. Tim Might be a reference problem, have them open the open a code window and click tools references. See what says missing and tell them what should be there, Add it and it should work. Duff "Tim Reid" <TimReid@discussions.micr...

forward to: field
Hi Everyone, im looking for the AD attribute in which the 'forward to:' field of the delivery options is stored. Thank you in advance kind regards marc -- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/ Its in the altRecipient attribute. -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Marc Wenger" <haga(at).iesg--nspm-noa9h.gmx.ch> wrote in message news:opr64hs7z6fyi4rt@news.microsoft.com... > Hi Everyone, > > im looking for the AD attribute in which the 'forward to:' field of ...

OWA jumping mouse
I have two computers on my network that are having an interesting issue. When they are creating a message and typing away in the body of the message, randomly when they hit a key, any key, they system will act as if the mouse was clicked. If the mouse is over the send button, it would have sent the email. If the mouse was over the start button, the start menu would have come up. Where ever the mouse was left at, it will act as if it was clicked. It is very random but often. This only happens in OWA, not in outlook or any other place. Both systems are running XP pro SP2 with IE 6 SP2. ...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

custom field for projects with tasks past due
I am wondering how/what formula would be needed for creating a custom field at the project level to show when a project has at least one task that is past due. I have a custom field at the task level (IIf([Finish]<[Current Date] And [% Complete]<100,1,2)) that will show when an individual task is overdue, but I want to add a custom field to the Project Center > Summary view to show when a project has any overdue tasks. The PM can then drill down from there into the project and see exactly what tasks are overdue. Once I figure out the formula for this I want to assign ...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Multiple Simultaneous Outlook Sessions
Is there a way for Outlook 2007 or 2003 to have multiple Simultaneous Outlook Sessions open at one time? If so, how is it setup to send and receive from the same profile I would choose and save the replies back into the same profile? thx "Art" <Art@discussions.microsoft.com> wrote in message news:433A0AAA-9DB0-4411-954F-5165374B049D@microsoft.com... > Is there a way for Outlook 2007 or 2003 to have multiple Simultaneous > Outlook > Sessions open at one time? If so, how is it setup to send and receive from > the same profile I would choose and sa...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

Getting the BCC field by default.
I would like the BCC field to show by default but cannot find the setting for this in 2007. Anyone know where this is located? In a new message window, use the Options chunk to display the "Show BCC." --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, JC HARRIS asked: | I would like the BCC field to show by default but cannot find the | setting for this in 2007. Anyone know where this is located? Thanks Milly. I had...

Multiple copies of email in "Forward to"contact
HIe, I am running Exchnage 2003. My CEO has a conatct in our organisation that refer to his blackberry contact. I have set the forwarding to also keep a copy in his mailbox. What happens is that three copies of his forwarded email hit his blackberry account! Only one hits his internal mailbox. What I ahve determined is that the front end Exchange server seems to be creating the additional copies. Is this how its meant to be (don think so cos its such a nuisance and my CEO keeps complaining about it). How do I tell the front end server that this guy's mail server is the one that...

printing multiple copies of Sigel GmbH
I have made a tent card in a small size of 5.588cm x 4.191cm for a swing tag, using Sigel GmbH in the "other labels" section. It prints one copy perfectly, leaving room on the page for at least about another 5 copies (which is what I want), but when in the print page section it doesn't give me the option to print multiple copies on the page. This will be an enormous waste of paper if I have to print the number of copies individually on each page. HELP! -- candere What is the product number for this label? I cannot find this size label on my 2007 version. -- Mary Sau...

Associating Multiple Existing Contacts to an Account, Until CRM 2.x
I have racked my pea-sized brain for endless days for a way to associate multiple existing contacts (or accounts) to an account. My client has accounts that have many contacts (or accounts) involved at different stages of acquiring a new account. The logical method would be to add a custom attribute with lookup functionality into the existing CRM contact or account objects but this is not possible with CRM 1.x. The resulting UI would allow an end user to associate different contacts (or accounts) to an account. Example: Account: ABC - AccountPhysician: (existing account or con...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Input to custom reports
Hello, I'd like to be able to input values to a custom report like the Year. Is there any way to package a custom report and provide input to it? Every year the user has to go into the custom report and change the Year restriction. Alternatively, (this is a payroll report), is there any global variable or some other back door that I can use to get the current year in a custom report? Given the report writer may not be the ticket what would the recommendation be to create custom reports that require input? VB.NET? -- Russ Using VBA you can allow for input in the custom reports. B...

compact database in VBA access 2007
Hello, I have recently upgrade to office 2007 from office 2003. To compact a database from within the database itself, I used the follwing code. Unfortunately it no longer works in access 2007. Is there some similar code that will work? Public Function FncCompactTheCurrentDB() CommandBars("Menu Bar"). _ Controls("Tools"). _ Controls("Database utilities"). _ Controls("Compact and repair database..."). _ accDoDefaultAction End Function Thank You, SL On Thu, 28 Jan 2010 17:34:01 -0800, SL <SL@discussions.microsoft....

Sort by Credit Card Type on EDC Detail Report
What do I have to do to get my EDC Detail Report to show different credit card types, like Visa, MC or Amex. Now under Tender Type, it just shows Credit card...well...I kinda knew that. How do I fix it? add tender types for each credit card type rather than just "credit card". then z out. all transactions after this change will reflect what you want in the reports. "tl" <tl@discussions.microsoft.com> wrote in message news:A54EEC95-208C-4962-A089-84B22A67632A@microsoft.com... > What do I have to do to get my EDC Detail Report to show different credit >...

Report to show Item Class Distribution Amounts
We would like to create a report, using Crystal Reports, that would show the following: dollar amount break down of the Sales Distribution accounts (COGS and Sales) per item class based on a date range. What is the most accurate way of going about this? We could only think of this method: (in short) sum the Ext Price based on SOP30300.CSLSINDX and SLSINDX and hope it matches the SOP10102 summed distribution amounts. Any advice would be appreciated. Thanks in advance. With the SLSINDX you would use the Extended Price and the CSLSINDX you would use Extended Cost. You would probably ...