Adding lines together to create one line

This is probably very simple but somehow I can't remember how to concatenate 
lines, so I have one line.  Here is example:
Table: Notes
OrderNo   LineNo   Note
111           1           aaa
111           2           bbb
111           3           ccc
222           1           xxx
222           2           yyy
333           1           000
333           2           111
333           3           222 
333           4           333

The result should be:
Table: A
OrderNo   Note
111         aaabbbccc
222         xxxyyy
333         000111222333

I've tried with this query but it doesn't do it:
SELECT N1.OrderNo, N1.Note+N2.Note+N3.Note AS Expr1
FROM (Notes AS N1 INNER JOIN Notes AS N2 ON N1.OrderNo = N2.OrderNo) INNER 
JOIN Notes AS N3 ON N2.OrderNo = N3.OrderNo
WHERE (((N1.OrderNo)=[N2].[OrderNo]) AND ((N1.LineNo)<[N2].[LineNo]) AND 
((N2.OrderNo)=[N3.OrderNo]) AND ((N2.LineNo)<[N3].[LineNo]));

Could anybody help me please ?
0
Utf
3/19/2010 9:18:01 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
884 Views

Similar Articles

[PageSpeed] 35

Try this --
SELECT OrderNo IIF([LineNo] = 1 AND [Note] Is Not Null, [Note], "") & 
OrderNo IIF([LineNo] = 2 AND [Note] Is Not Null, [Note], "") & OrderNo 
IIF([LineNo] = 3 AND [Note] Is Not Null, [Note], "") & OrderNo IIF([LineNo] = 
4 AND [Note] Is Not Null, [Note], "") & OrderNo IIF([LineNo] = 5 AND [Note] 
Is Not Null, [Note], "") AS [All_Notes]
FROM Notes;


-- 
Build a little, test a little.


"clalc" wrote:

> This is probably very simple but somehow I can't remember how to concatenate 
> lines, so I have one line.  Here is example:
> Table: Notes
> OrderNo   LineNo   Note
> 111           1           aaa
> 111           2           bbb
> 111           3           ccc
> 222           1           xxx
> 222           2           yyy
> 333           1           000
> 333           2           111
> 333           3           222 
> 333           4           333
> 
> The result should be:
> Table: A
> OrderNo   Note
> 111         aaabbbccc
> 222         xxxyyy
> 333         000111222333
> 
> I've tried with this query but it doesn't do it:
> SELECT N1.OrderNo, N1.Note+N2.Note+N3.Note AS Expr1
> FROM (Notes AS N1 INNER JOIN Notes AS N2 ON N1.OrderNo = N2.OrderNo) INNER 
> JOIN Notes AS N3 ON N2.OrderNo = N3.OrderNo
> WHERE (((N1.OrderNo)=[N2].[OrderNo]) AND ((N1.LineNo)<[N2].[LineNo]) AND 
> ((N2.OrderNo)=[N3.OrderNo]) AND ((N2.LineNo)<[N3].[LineNo]));
> 
> Could anybody help me please ?
0
Utf
3/19/2010 9:49:01 PM
I'm running into syntax error.  Could this be because of "&" ?  

"KARL DEWEY" wrote:

> Try this --
> SELECT OrderNo IIF([LineNo] = 1 AND [Note] Is Not Null, [Note], "") & 
> OrderNo IIF([LineNo] = 2 AND [Note] Is Not Null, [Note], "") & OrderNo 
> IIF([LineNo] = 3 AND [Note] Is Not Null, [Note], "") & OrderNo IIF([LineNo] = 
> 4 AND [Note] Is Not Null, [Note], "") & OrderNo IIF([LineNo] = 5 AND [Note] 
> Is Not Null, [Note], "") AS [All_Notes]
> FROM Notes;
> 
> 
> -- 
> Build a little, test a little.
> 
> 
> "clalc" wrote:
> 
> > This is probably very simple but somehow I can't remember how to concatenate 
> > lines, so I have one line.  Here is example:
> > Table: Notes
> > OrderNo   LineNo   Note
> > 111           1           aaa
> > 111           2           bbb
> > 111           3           ccc
> > 222           1           xxx
> > 222           2           yyy
> > 333           1           000
> > 333           2           111
> > 333           3           222 
> > 333           4           333
> > 
> > The result should be:
> > Table: A
> > OrderNo   Note
> > 111         aaabbbccc
> > 222         xxxyyy
> > 333         000111222333
> > 
> > I've tried with this query but it doesn't do it:
> > SELECT N1.OrderNo, N1.Note+N2.Note+N3.Note AS Expr1
> > FROM (Notes AS N1 INNER JOIN Notes AS N2 ON N1.OrderNo = N2.OrderNo) INNER 
> > JOIN Notes AS N3 ON N2.OrderNo = N3.OrderNo
> > WHERE (((N1.OrderNo)=[N2].[OrderNo]) AND ((N1.LineNo)<[N2].[LineNo]) AND 
> > ((N2.OrderNo)=[N3.OrderNo]) AND ((N2.LineNo)<[N3].[LineNo]));
> > 
> > Could anybody help me please ?
0
Utf
3/20/2010 1:01:01 AM
On Fri, 19 Mar 2010 18:01:01 -0700, clalc <clalc@discussions.microsoft.com>
wrote:

>I'm running into syntax error.  Could this be because of "&" ?  
>
>"KARL DEWEY" wrote:
>
>> Try this --
>> SELECT OrderNo IIF([LineNo] = 1 AND [Note] Is Not Null, [Note], "") & 
>> OrderNo IIF([LineNo] = 2 AND [Note] Is Not Null, [Note], "") & OrderNo 
>> IIF([LineNo] = 3 AND [Note] Is Not Null, [Note], "") & OrderNo IIF([LineNo] = 
>> 4 AND [Note] Is Not Null, [Note], "") & OrderNo IIF([LineNo] = 5 AND [Note] 
>> Is Not Null, [Note], "") AS [All_Notes]
>> FROM Notes;
>> 
>> 

You've got a whole huge RAFT of syntax errors there. You're repeating OrderNo
over and over again, you don't have any commas or ampersands between things,
and it's not clear to me what you're even trying to do!

If you want to concatenate values from multiple records into one field, you'll
need some VBA code:

http://www.mvps.org/access/modules/mdl0004.htm 

-- 

             John W. Vinson [MVP]
0
John
3/20/2010 4:43:04 AM
You are right, I opened later and did not know how I got there.

Here are two queries that I tested and will handle up to 5 notes per orderno 
--
    Query-clalc_1 --
SELECT Notes.OrderNo, Max(Len(IIf([Notes].[LineNo]=1 And [Notes].[Note] Is 
Not Null,[Notes].[Note],"") & IIf([Notes_1].[LineNo]=2 And [Notes_1].[Note] 
Is Not Null,[Notes_1].[Note],"") & IIf([Notes_2].[LineNo]=3 And 
[Notes_2].[Note] Is Not Null,[Notes_2].[Note],"") & IIf([Notes_3].[LineNo]=4 
And [Notes_3].[Note] Is Not Null,[Notes_3].[Note],"") & 
IIf([Notes_4].[LineNo]=5 And [Notes_4].[Note] Is Not 
Null,[Notes_4].[Note],""))) AS Expr1
FROM (((Notes LEFT JOIN Notes AS Notes_1 ON Notes.OrderNo = Notes_1.OrderNo) 
LEFT JOIN Notes AS Notes_2 ON Notes_1.OrderNo = Notes_2.OrderNo) LEFT JOIN 
Notes AS Notes_3 ON Notes_2.OrderNo = Notes_3.OrderNo) LEFT JOIN Notes AS 
Notes_4 ON Notes_3.OrderNo = Notes_4.OrderNo
GROUP BY Notes.OrderNo;

SELECT Notes.OrderNo, IIf([Notes].[LineNo]=1 And [Notes].[Note] Is Not 
Null,[Notes].[Note],"") & IIf([Notes_1].[LineNo]=2 And [Notes_1].[Note] Is 
Not Null,[Notes_1].[Note],"") & IIf([Notes_2].[LineNo]=3 And [Notes_2].[Note] 
Is Not Null,[Notes_2].[Note],"") & IIf([Notes_3].[LineNo]=4 And 
[Notes_3].[Note] Is Not Null,[Notes_3].[Note],"") & IIf([Notes_4].[LineNo]=5 
And [Notes_4].[Note] Is Not Null,[Notes_4].[Note],"") AS All_Notes
FROM [Query-clalc_1] INNER JOIN ((((Notes LEFT JOIN Notes AS Notes_1 ON 
Notes.OrderNo = Notes_1.OrderNo) LEFT JOIN Notes AS Notes_2 ON 
Notes_1.OrderNo = Notes_2.OrderNo) LEFT JOIN Notes AS Notes_3 ON 
Notes_2.OrderNo = Notes_3.OrderNo) LEFT JOIN Notes AS Notes_4 ON 
Notes_3.OrderNo = Notes_4.OrderNo) ON [Query-clalc_1].OrderNo = Notes.OrderNo
WHERE (((Len(IIf([Notes].[LineNo]=1 And [Notes].[Note] Is Not 
Null,[Notes].[Note],"") & IIf([Notes_1].[LineNo]=2 And [Notes_1].[Note] Is 
Not Null,[Notes_1].[Note],"") & IIf([Notes_2].[LineNo]=3 And [Notes_2].[Note] 
Is Not Null,[Notes_2].[Note],"") & IIf([Notes_3].[LineNo]=4 And 
[Notes_3].[Note] Is Not Null,[Notes_3].[Note],"") & IIf([Notes_4].[LineNo]=5 
And [Notes_4].[Note] Is Not Null,[Notes_4].[Note],"")))=[Expr1]))
GROUP BY Notes.OrderNo, IIf([Notes].[LineNo]=1 And [Notes].[Note] Is Not 
Null,[Notes].[Note],"") & IIf([Notes_1].[LineNo]=2 And [Notes_1].[Note] Is 
Not Null,[Notes_1].[Note],"") & IIf([Notes_2].[LineNo]=3 And [Notes_2].[Note] 
Is Not Null,[Notes_2].[Note],"") & IIf([Notes_3].[LineNo]=4 And 
[Notes_3].[Note] Is Not Null,[Notes_3].[Note],"") & IIf([Notes_4].[LineNo]=5 
And [Notes_4].[Note] Is Not Null,[Notes_4].[Note],"");

-- 
Build a little, test a little.


"John W. Vinson" wrote:

> On Fri, 19 Mar 2010 18:01:01 -0700, clalc <clalc@discussions.microsoft.com>
> wrote:
> 
> >I'm running into syntax error.  Could this be because of "&" ?  
> >
> >"KARL DEWEY" wrote:
> >
> >> Try this --
> >> SELECT OrderNo IIF([LineNo] = 1 AND [Note] Is Not Null, [Note], "") & 
> >> OrderNo IIF([LineNo] = 2 AND [Note] Is Not Null, [Note], "") & OrderNo 
> >> IIF([LineNo] = 3 AND [Note] Is Not Null, [Note], "") & OrderNo IIF([LineNo] = 
> >> 4 AND [Note] Is Not Null, [Note], "") & OrderNo IIF([LineNo] = 5 AND [Note] 
> >> Is Not Null, [Note], "") AS [All_Notes]
> >> FROM Notes;
> >> 
> >> 
> 
> You've got a whole huge RAFT of syntax errors there. You're repeating OrderNo
> over and over again, you don't have any commas or ampersands between things,
> and it's not clear to me what you're even trying to do!
> 
> If you want to concatenate values from multiple records into one field, you'll
> need some VBA code:
> 
> http://www.mvps.org/access/modules/mdl0004.htm 
> 
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
3/21/2010 4:41:01 PM
Reply:

Similar Artilces:

cannot create profiles in win 7 mail 32 bit
I upgraded to WIndows 7 professional 64 bit and still cannot get my MS Office 2003 to work properly. All the programs except Outlook seems to work fine. I am totally unable to open outlook without mail profiles. I click on mail in control panel and I get low memoery or resources too low. That's impossible. I have 4GB RAM and 1GB VRAM. In addition, I noticed to night that while I created a profile for myself as Admin, I do ot have admin permissions EVEN IF I say 'run as admin" If I open my profile, it syas Admin under my name yet shows up as standard user. I...

Update one table from another
I am trying to update one table that has one record for each employee(table 1) with available vacation time. The other table records every time off request(table 2) and how much time they want off. I have the update query and it works fine. The problem is that everytime it is ran every requested time off amount(from table2) is subtracted from the available time(table1) again and again. I want the records for requested time(table2) to update the employee available time off(table1) only once, but keep the records on the table as that is the basis for a report. Thank You, Brett ----=...

Text to columns
Once I use the Text to columns feature in Excel, it seems there is no way to turn it off. Anyone know if there is a way to reset this so that newly pasted text will not continue to get broken up (for example by the space delimiter) Presently the only way is to exit Excel and restart Excel - then pasted text all goes into one cell regardless of spaces. Hope I explained that well enough Al I may have been to hasty in making this assumption, it appears that the problem I described below is only happening on one workstation - this may indicate that the Excel Registry keys are in need of...

Creating a table
There's probably an easier way to do it but... I have a series of numbers in column A (150 in all) and a series of letters in column B (22 in all). In total there are 4800 rows. What I am trying to do is create a table with the number down the left hand side and the letters across the top. In each cell within the table I need to count how many times the combination of number/letter appears. Eg: A B C 201 0 2 2 202 3 0 1 203 3 6 4 I tried combining the number/letter into a single text field using the CONCATENATE function then filtering ...

Creating a letter from template
Is it possible in CRM? So far the only way i figured out to use templated is in an email and /KB-article. (And order offcourse) The thing i'm looking for is when selecting a contact, somehow i can make a letter activity and call a template that puts name & adress data etc. After that i should be able to print the letter, to put it in an enveloppe and send it someplace... So far it seems to me this cannot be done, but then again, i'm certainly not a CRM expert... Is it possible? If so, can anyone provide me the actions to be taken step by step ? thanks Kas "_Kas_&...

Adding Contacts Folder as Address Book in Exchange Server
I am trying to add my contacts folder to my address book in Outlook XP and don't see the Tools, Options I used to see in Outlook 2000. How can I add this folder to my address book? Is that an option you can turn on or off for a profile in Exchange Server? Thanks!! -- Remove 'spam' from email address to contact me directly Right click on your Contacts folder, go to properties, outlook address book. Can you tick the box there? If not, go to tools, e-mail accounts, view/change address books, and make sure you have the Outlook Address Book option there. Then try right-clicking...

Simple Question-How to create more than one transaction on the Acc
If there is a question already posted let me know. The question is: I created a bank account information on the Account list icon and want to have more than (one)transactions listed and see each payee displayed separately on each page so i could have all the months posted with due dates and total listed. Thank you. In microsoft.public.money, a.j. wrote: >If there is a question already posted let me know. The question is: I created >a bank account information on the Account list icon and want to have more >than (one)transactions listed and see each payee displayed separately ...

How to create an "and" rule in Query Based Distribution Groups
Hi, With Exchange 2003 Query Based Distribution groups, is it possible to create an "and" rule? ie, all users who are based in "London" "and" have the first name "John"? Thanks, Curtis. -- Please reply to news group only. Thank you. Sure. (&(attribute1=blah)(attribute2=blah)) http://msdn.microsoft.com/library/en-us/adsi/adsi/search_filter_syntax.asp?frame=true -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "Curtis Fray" <xxx@xxx.com> wrote in message news:OjVc...

Email accounts are not created for new user
I had a severe system crash several days ago because of bad memory in my domain controller (Server 2003 R2)/Exchange server 2003 (small site - no extra systems). I had to reinstall Exchange 2003 and reload the exchange store from backup. For existing users everything works great. Everybody can send and receive email, the calender works correctly, etc. OWA works. However, when I create a new user account in Active Directory Computers and Users, the account is created and the new user can log onto a system. However, Outlook 2003 cannot be configured to use the exchange server. In t...

Somehow I created a Macro in a worksheet.
I created a macro in an Excel worksheet somehow. I didn't try to, it just happened. Now everytime I open that workbook, it asks me if I want to run the macro, disable it, etc. How the hell do I get rid of the macro? It doesn't show up under tools, macros. And it apparently doesn't do anything either because I can disable it and nothing different happens. Who invented this system anyway? Thanks, V When you record a macro, a module is created to store the macro code. There are instructions here for removing the module that is causing the prompt to appear: http://www.c...

Adding an "I'm out of the Office" Message
Re Outlook Express. I can't find anywhere on the index about how to send one of those "I will be out of the office from July blah blah to August blah blah." Anyone help, please? thanks-- Richard Hi - This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Internet Explorer and is a quite different program, despite its similar name.. You will probably get a faster and more expert answer if you post this to an Outlook Express news group. Try posting in one of these newsgroups: microsoft.public.windows.inetexplorer.ie5.outlookexp...

we do not create seperate http virtual server anymore?
Hi all, Just want to confirm that we do not need to create the seperate http virtual server for FE/BE (backend two node A/P Exchange 2003 cluster), right? I remember I need to create it in Exchange 2000 FE/BE clustering envir. Thanks in advance. On Wed, 2 Aug 2006 11:44:26 -0700, John <John@discussions.microsoft.com> wrote: >Hi all, > >Just want to confirm that we do not need to create the seperate http virtual >server for FE/BE (backend two node A/P Exchange 2003 cluster), right? I >remember I need to create it in Exchange 2000 FE/BE clustering envir. > >...

Error creating new task
Error .. "Could not complete operation. One or more parameter values are not vaild" ...

Creating a print-macro that'll only print page 1?
Hi, I have a spreadsheet that contains multiple sheets. I want to add a button that runs a macro, which only prints page 1 on the first sheet (called Scorekort). The "Scorekort" sheet contains 13 pages in total, but I only want to print the first page. Any ideas for this macro? Anders hi, Sub Macro1() Sheets("Scorekort").PrintOut From:=1, To:=1, Copies:=1, Preview:=True, Collate:=True End Sub if you don't want a print preview, Preview:=False -- isabelle Le 2011-05-21 04:16, Anders M a �crit : > Hi, > > I have a spreadsheet that contains multiple ...

create main configuration file for solution.
Hello. How can I create main configuration file for my solution (Win-Form : VS 2008. VB.NET), like a general ini-file. Thanks :) "Mr. X." wrote: > Hello. > How can I create main configuration file for my solution (Win-Form : VS > 2008. VB.NET), like a general ini-file. > It's called an app.config. There is a name space you can use to mainulupate the app.config from a program. I see it on the project source directory (not for solution). I cannot see it on the release directory. Is there any convention to read / write from app.config...

Creating Contacts For Accounts Using...
Hopefully as Microsoft CRM matures, many small time saving features will be added. One that should be a priority is the ability to add a new contact for an existing account using the common account information, i.e. address, phone number, web address, etc. Retyping the same company address in each time is not very productive. Thank you, Ed Podowski ---------------- 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" button in the message pane. If you do not see the button, f...

WdfUsbTargetDeviceCreate creates NULL Control Pipe Handle
Hi, We have a usb composite device which has one mass storage interface and another as a network interface. We are developing a WDF driver (NDIS-USB) for the network interface. Immediately after WdfUsbTargetDeviceCreate if I break into the debugger and examine the newly created device, then I see that the Control Pipe Handle is NULL! Here is the actual output: -------- kd> !WDFUSBDEVICE 0x0000057f`fe5905f8 WDFUSBDEVICE 0000057ffe5905f8 ============================= Config descriptor fffffa80037216b0, device descriptor fffffa8001a6fb58 Control USBD_PIPE_HANDLE 000000000000...

Create individual files from a row
I have an excel file with several thousand entries, which contain data in several columns. I would like to be able to create an individual xml or html file for each row, but with predifined formatting around so Mr A bloggs, A street, A town, AA1 1AA Could become Abloggs.html <head></head> <name>Mr A bloggs</name> <street>A street</street> Any other info here as well </html> etc. Is this possible and any suggestions how? thanks, Graham. -- GrahamN ------------------------------------------------------------------------ GrahamN's Profile: ...

Cannot create mailbox for new user accounts
I am running Exchange 2003 on Server 2003. I was able to move all existing mailboxes from my old Exchange 2000 server to my new 2003 server. Whenever I try to create a new user it creates the user normally and asks if I want to create a mailbox for the user. I check yes, and it says account created successfully. However, the mailbox never appears in Exchange, no email address is assigned to the new user, and I cannot send mail to the user account either. If I try to access any Exchange properties from ADUC it shows everyhting as if a mailbox exists, but there is no mailbox. I am ...

Scroll horizontaly with mouse, create same system used to scroll .
Hi, I think it would be great if mouses adopted a second scroll button, for horizontal scrolling, just like the vertical one .... Indeed, when you work with wide Excel spreadsheets, you can easily scroll down but to scroll from left to right or vice-versa, you have to use the scroll bar or arrows and it's annoying... So, am I a millionnaire yet??? Hi Frederic, > I think it would be great if mouses adopted a second scroll button, for > horizontal scrolling, just like the vertical one .... Indeed, when you work > with wide Excel spreadsheets, you can easily scroll down b...

Adding a button with a function on protected sheets.
How do i add a button to 'reset/clear' the data on a worksheet that is protected and uses VLookup data (from another worksheet). Everyday this table will have data chosen from combo boxes or manually entered in allowable editable fields and at the end of the day after the files is saved, I need to clear that data for use on the next day. How is this Reset button applied with allowable edit ranges, VLookup data and a protected worksheet? Thanks There's several ways to do this. 1. Instead of straight vlookups, use =IF(ISBLANK(VLOOKUP(....)),"",VLOOKUP(....)) wher...

Page break preview & blue line
I using Excel 2003 In page break preview I have always do some thing wrong I'm just pull the blue line by using mouse all page was destroyed It there any tips to return the blue line to the Default pages size setting As far as I know the 'default' print area is all the data, and once you have changed the print area and saved the file you can not get it to go back to the previous setting. If you have problems with this or any other proceedure do try to remember to save your file before you do the awkward bit and then you can go back to the saved copy it it does not work. ...

copy-paste from excel to powerpoint
Office 97 When I copy a number of cells from Excel to powerpoint, I can't get rid of the grid lines. There are no borders. When I'm in Excel, I see the faint grid lines as you normally would. Unfortunately, these lines also display in powerpoint. How do I stop that behaviour. Thanks so much for any help. Diana Select all the cells you are copying. Then: Format > Cells... > Patterns Then select white color ( bottom right) When the backgound color is set the gridlines vanish unless borders are turned on -- Gary''s Student "Cowtoon" wrote: > Off...

How do I create custom postcards in Word 2007?
I want to create my own postcards using the right size and type of paper for mailing. This depends entirely on your printer. Can it handle the heavy weight of card used in postcards, and can it handle the unusual size of a postcard? On Apr 18, 2:54=A0pm, Create Custom Postcards <Create Custom Postca...@discussions.microsoft.com> wrote: > I want to create my own postcards using the right size and type of paper = for > mailing. Perhaps the easiest way to start is to get some Avery (or similar) postcard stock, which will have two postcards per sheet. Then find the a...

Sequentially number lines automatically
Version: v.X Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How can automatically number the lines as I enter data. <br><br>so box A1 would automatically be 1 <br> and the next time I created an entry on another line that line would be 2, etc. <br><br>The reason is so that I can sort, delete and the numbers will remain, so I can also go back to the order than things were entered into. <br><br>Could do this manually, but a pain. <br><br>Or could possibly do this with a date and time? <br><br>Thanks Steevee Se...