Adding lines together to create one line

  • Thread starter Thread starter clalc
  • Start date Start date
C

clalc

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 ?
 
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;
 
I'm running into syntax error. Could this be because of "&" ?

KARL DEWEY said:
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 said:
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 ?
Click to expand...
Click to expand...
 
I'm running into syntax error. Could this be because of "&" ?

KARL DEWEY said:
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;
Click to expand...
Click to expand...

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
 
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 said:
I'm running into syntax error. Could this be because of "&" ?

KARL DEWEY said:
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;
Click to expand...
Click to expand...

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
Click to expand...
 
Back
Top