Concatenation of Text as Expression In A Query

[Guest]

ORDER_LINE and LINE_NUMERIC are both in the same table V_ORDER_TEXT. If I
query ORDER_LINE it gives me values such as 0010, 0020, 0030 and if I query
LINE_NUMERIC it gives me values such as 1, 2, 3. The only field in the table
setup up as a "number" field is LINE_NUMERIC, all the others are "text". I'm
guessing whoever built the tables created them to mean the same thing just as
different value displays because 0010 = 1, and 0020 = 2 and 0030 = 3, etc...
I also set the SQL to exclude "ZZZZ" in the ORDER_LINE beacuse that brings up
LINE_NUMERIC with the value of "0" and I don't need to include those lines.

Where did the field "ORDER_LINE" come from? It is fairly evident that this
was a very significant field in your desired result? Is the ORDER_LINE field
also available in the V_ORDER_LINES table?

First create a query
===qselOrders======
SELECT ORDER_NO, ORDER_LINE
FROM V_ORDER_TEXT
WHERE ORDER_NO="0000472" AND ORDER_LINE<>"ZZZZ"
GROUP BY ORDER_NO, ORDER_LINE;

Then create a query:
SELECT ORDER_NO, ORDER_LINE,
Concatenate("SELECT [TEXT] FROM V_ORDER_TEXT WHERE ORDER_NO =""" & ORDER_NO
& """ AND ORDER_LINE=""" & ORDER_LINE & """ ORDER BY TEXT_SEQ") as TextLines
FROM qselOrders;

If ORDER_NO and ORDER_LINE are both in the V_ORDER_LINES table then you
should be able to replace the first query with V_ORDER_LINES.

--
Duane Hookom
MS Access MVP
--

Let me start over and try to simplify this query into a smaller piece
which I
can then use to call upon in another query. My thought is perhaps the way
this table was constructed, I need to string together multiple fields and
not
just one to get it to work. I boiled it down to using only ONE TABLE
instead
V_ORDER_TEXT

If I use this SQL ---------

SELECT V_ORDER_TEXT.ORDER_NO, V_ORDER_TEXT.ORDER_LINE,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT
FROM V_ORDER_TEXT
GROUP BY V_ORDER_TEXT.ORDER_NO, V_ORDER_TEXT.ORDER_LINE,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT
HAVING (((V_ORDER_TEXT.ORDER_NO)="0000472") AND
((V_ORDER_TEXT.ORDER_LINE)<>"ZZZZ"))
ORDER BY V_ORDER_TEXT.ORDER_LINE, V_ORDER_TEXT.TEXT_SEQ;

I get this ------------

ORDER_NO ORDER_LINE TEXT_SEQ TEXT
0000472 0010 001 ABCD
0000472 0010 002 EFGH
0000472 0030 001 UVW
0000472 0030 002 XYZ

And obviously I want this -----------

ORDER_NO ORDER_LINE TEXT_SEQ TEXT
0000472 0010 001,002
ABCD,EFGH
0000472 0030 001,002 UVW,XYZ

Maybe the table was constructed so that both TEXT_SEQ and TEXT need to be
concatenated???? Just a thought.

P.S. - I REALLY appreciate ALL you help!

 

[Guest]

SORRY - Didn't notice your second question:
ORDER_LINE and LINE_NUMERIC are ONLY IN the table V_ORDER_TEXT
and not V_ORDER_LINES

Where did the field "ORDER_LINE" come from? It is fairly evident that this
was a very significant field in your desired result? Is the ORDER_LINE field
also available in the V_ORDER_LINES table?

First create a query
===qselOrders======
SELECT ORDER_NO, ORDER_LINE
FROM V_ORDER_TEXT
WHERE ORDER_NO="0000472" AND ORDER_LINE<>"ZZZZ"
GROUP BY ORDER_NO, ORDER_LINE;

Then create a query:
SELECT ORDER_NO, ORDER_LINE,
Concatenate("SELECT [TEXT] FROM V_ORDER_TEXT WHERE ORDER_NO =""" & ORDER_NO
& """ AND ORDER_LINE=""" & ORDER_LINE & """ ORDER BY TEXT_SEQ") as TextLines
FROM qselOrders;

If ORDER_NO and ORDER_LINE are both in the V_ORDER_LINES table then you
should be able to replace the first query with V_ORDER_LINES.

--
Duane Hookom
MS Access MVP
--

Let me start over and try to simplify this query into a smaller piece
which I
can then use to call upon in another query. My thought is perhaps the way
this table was constructed, I need to string together multiple fields and
not
just one to get it to work. I boiled it down to using only ONE TABLE
instead
V_ORDER_TEXT

If I use this SQL ---------

SELECT V_ORDER_TEXT.ORDER_NO, V_ORDER_TEXT.ORDER_LINE,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT
FROM V_ORDER_TEXT
GROUP BY V_ORDER_TEXT.ORDER_NO, V_ORDER_TEXT.ORDER_LINE,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT
HAVING (((V_ORDER_TEXT.ORDER_NO)="0000472") AND
((V_ORDER_TEXT.ORDER_LINE)<>"ZZZZ"))
ORDER BY V_ORDER_TEXT.ORDER_LINE, V_ORDER_TEXT.TEXT_SEQ;

I get this ------------

ORDER_NO ORDER_LINE TEXT_SEQ TEXT
0000472 0010 001 ABCD
0000472 0010 002 EFGH
0000472 0030 001 UVW
0000472 0030 002 XYZ

And obviously I want this -----------

ORDER_NO ORDER_LINE TEXT_SEQ TEXT
0000472 0010 001,002
ABCD,EFGH
0000472 0030 001,002 UVW,XYZ

Maybe the table was constructed so that both TEXT_SEQ and TEXT need to be
concatenated???? Just a thought.

P.S. - I REALLY appreciate ALL you help!

 

[Duane Hookom]

You also seemed to have missed the time I spent creating these queries for
you to test.

First create a query
===qselOrders======
SELECT ORDER_NO, ORDER_LINE
FROM V_ORDER_TEXT
WHERE ORDER_NO="0000472" AND ORDER_LINE<>"ZZZZ"
GROUP BY ORDER_NO, ORDER_LINE;

Then create a query:
SELECT ORDER_NO, ORDER_LINE,
Concatenate("SELECT [TEXT] FROM V_ORDER_TEXT WHERE ORDER_NO =""" & ORDER_NO
& """ AND ORDER_LINE=""" & ORDER_LINE & """ ORDER BY TEXT_SEQ") as TextLines
FROM qselOrders;

--
Duane Hookom
MS Access MVP
--

SORRY - Didn't notice your second question:
ORDER_LINE and LINE_NUMERIC are ONLY IN the table V_ORDER_TEXT
and not V_ORDER_LINES

Where did the field "ORDER_LINE" come from? It is fairly evident that
this
was a very significant field in your desired result? Is the ORDER_LINE
field
also available in the V_ORDER_LINES table?

First create a query
===qselOrders======
SELECT ORDER_NO, ORDER_LINE
FROM V_ORDER_TEXT
WHERE ORDER_NO="0000472" AND ORDER_LINE<>"ZZZZ"
GROUP BY ORDER_NO, ORDER_LINE;

Then create a query:
SELECT ORDER_NO, ORDER_LINE,
Concatenate("SELECT [TEXT] FROM V_ORDER_TEXT WHERE ORDER_NO =""" &
ORDER_NO
& """ AND ORDER_LINE=""" & ORDER_LINE & """ ORDER BY TEXT_SEQ") as
TextLines
FROM qselOrders;

If ORDER_NO and ORDER_LINE are both in the V_ORDER_LINES table then you
should be able to replace the first query with V_ORDER_LINES.

--
Duane Hookom
MS Access MVP
--

Let me start over and try to simplify this query into a smaller piece
which I
can then use to call upon in another query. My thought is perhaps the
way
this table was constructed, I need to string together multiple fields
and
not
just one to get it to work. I boiled it down to using only ONE TABLE
instead
V_ORDER_TEXT

If I use this SQL ---------

SELECT V_ORDER_TEXT.ORDER_NO, V_ORDER_TEXT.ORDER_LINE,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT
FROM V_ORDER_TEXT
GROUP BY V_ORDER_TEXT.ORDER_NO, V_ORDER_TEXT.ORDER_LINE,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT
HAVING (((V_ORDER_TEXT.ORDER_NO)="0000472") AND
((V_ORDER_TEXT.ORDER_LINE)<>"ZZZZ"))
ORDER BY V_ORDER_TEXT.ORDER_LINE, V_ORDER_TEXT.TEXT_SEQ;

I get this ------------

ORDER_NO ORDER_LINE TEXT_SEQ TEXT
0000472 0010 001
ABCD
0000472 0010 002
EFGH
0000472 0030 001 UVW
0000472 0030 002 XYZ

And obviously I want this -----------

ORDER_NO ORDER_LINE TEXT_SEQ TEXT
0000472 0010 001,002
ABCD,EFGH
0000472 0030 001,002
UVW,XYZ

Maybe the table was constructed so that both TEXT_SEQ and TEXT need to
be
concatenated???? Just a thought.

P.S. - I REALLY appreciate ALL you help!

 

[Guest]

NO - I haven't forgotten.

You also seemed to have missed the time I spent creating these queries for
you to test.

First create a query
===qselOrders======
SELECT ORDER_NO, ORDER_LINE
FROM V_ORDER_TEXT
WHERE ORDER_NO="0000472" AND ORDER_LINE<>"ZZZZ"
GROUP BY ORDER_NO, ORDER_LINE;

Then create a query:
SELECT ORDER_NO, ORDER_LINE,
Concatenate("SELECT [TEXT] FROM V_ORDER_TEXT WHERE ORDER_NO =""" & ORDER_NO
& """ AND ORDER_LINE=""" & ORDER_LINE & """ ORDER BY TEXT_SEQ") as TextLines
FROM qselOrders;

--
Duane Hookom
MS Access MVP
--

SORRY - Didn't notice your second question:
ORDER_LINE and LINE_NUMERIC are ONLY IN the table V_ORDER_TEXT
and not V_ORDER_LINES

Where did the field "ORDER_LINE" come from? It is fairly evident that
this
was a very significant field in your desired result? Is the ORDER_LINE
field
also available in the V_ORDER_LINES table?

First create a query
===qselOrders======
SELECT ORDER_NO, ORDER_LINE
FROM V_ORDER_TEXT
WHERE ORDER_NO="0000472" AND ORDER_LINE<>"ZZZZ"
GROUP BY ORDER_NO, ORDER_LINE;

Then create a query:
SELECT ORDER_NO, ORDER_LINE,
Concatenate("SELECT [TEXT] FROM V_ORDER_TEXT WHERE ORDER_NO =""" &
ORDER_NO
& """ AND ORDER_LINE=""" & ORDER_LINE & """ ORDER BY TEXT_SEQ") as
TextLines
FROM qselOrders;

If ORDER_NO and ORDER_LINE are both in the V_ORDER_LINES table then you
should be able to replace the first query with V_ORDER_LINES.

--
Duane Hookom
MS Access MVP
--

Let me start over and try to simplify this query into a smaller piece
which I
can then use to call upon in another query. My thought is perhaps the
way
this table was constructed, I need to string together multiple fields
and
not
just one to get it to work. I boiled it down to using only ONE TABLE
instead
V_ORDER_TEXT

If I use this SQL ---------

SELECT V_ORDER_TEXT.ORDER_NO, V_ORDER_TEXT.ORDER_LINE,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT
FROM V_ORDER_TEXT
GROUP BY V_ORDER_TEXT.ORDER_NO, V_ORDER_TEXT.ORDER_LINE,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT
HAVING (((V_ORDER_TEXT.ORDER_NO)="0000472") AND
((V_ORDER_TEXT.ORDER_LINE)<>"ZZZZ"))
ORDER BY V_ORDER_TEXT.ORDER_LINE, V_ORDER_TEXT.TEXT_SEQ;

I get this ------------

ORDER_NO ORDER_LINE TEXT_SEQ TEXT
0000472 0010 001
ABCD
0000472 0010 002
EFGH
0000472 0030 001 UVW
0000472 0030 002 XYZ

And obviously I want this -----------

ORDER_NO ORDER_LINE TEXT_SEQ TEXT
0000472 0010 001,002
ABCD,EFGH
0000472 0030 001,002
UVW,XYZ

Maybe the table was constructed so that both TEXT_SEQ and TEXT need to
be
concatenated???? Just a thought.

P.S. - I REALLY appreciate ALL you help!

 

[Guest]

The 2nd portion of the query gives me this -------

ORDER_NO ORDER_LINE TextLines
0000472 0010 EFGH, EFGH
0000472 0030 XYZ, XYZ

instead of this -----------

ORDER_NO ORDER_LINE TextLines
0000472 0010 ABCD, EFGH
0000472 0030 UVW, XYZ

It seems to be ignoring the TEXT_SEQ ORDER BY

 

[Duane Hookom]

Just for my comfort, please post back with the SQL views of your queries as
well as the code in your Concatenate function. Have you attempted to set a
break point and step through the code?

 

[Guest]

SQL for 1st Query ------

SELECT V_ORDER_TEXT.ORDER_NO, V_ORDER_TEXT.ORDER_LINE
FROM V_ORDER_TEXT
WHERE (((V_ORDER_TEXT.ORDER_NO)="0000472") AND
((V_ORDER_TEXT.ORDER_LINE)<>"ZZZZ"))
GROUP BY V_ORDER_TEXT.ORDER_NO, V_ORDER_TEXT.ORDER_LINE;

SQL for 2nd Query -------

SELECT ORDER_NO, ORDER_LINE,
Concatenate("SELECT [TEXT] FROM V_ORDER_TEXT WHERE ORDER_NO =""" & ORDER_NO
& """ AND ORDER_LINE=""" & ORDER_LINE & """ ORDER BY TEXT_SEQ") as TextLines
FROM qselOrders;

The code from your module:

Option Compare Database

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

If I do need to do something extra with the code, I wouldn't know what that
would be since I'm not familiar with the VBA side.

 

[Duane Hookom]

To insert a break point, you would click in the vertical bar to the left of
your code. For instance, you could click to the left of
| |
| O | With rs
| |
This will add the dot and highlight the line of code. When you run the
query, the code will pause and allow you to see the value of variables by
placing your mouse over the code. You can step through the code by pressing
F8 for one line at a time or F5 to process until the next break point or the
end of the code.

If you are totally lost, I would consider getting a sampling of your data in
a compacted and zipped MDB file. You would need to send me a private email
if you would like to do this.

--
Duane Hookom
MS Access MVP
--

SQL for 1st Query ------

SELECT V_ORDER_TEXT.ORDER_NO, V_ORDER_TEXT.ORDER_LINE
FROM V_ORDER_TEXT
WHERE (((V_ORDER_TEXT.ORDER_NO)="0000472") AND
((V_ORDER_TEXT.ORDER_LINE)<>"ZZZZ"))
GROUP BY V_ORDER_TEXT.ORDER_NO, V_ORDER_TEXT.ORDER_LINE;

SQL for 2nd Query -------

SELECT ORDER_NO, ORDER_LINE,
Concatenate("SELECT [TEXT] FROM V_ORDER_TEXT WHERE ORDER_NO =""" &
ORDER_NO
& """ AND ORDER_LINE=""" & ORDER_LINE & """ ORDER BY TEXT_SEQ") as
TextLines
FROM qselOrders;

The code from your module:

Option Compare Database

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

If I do need to do something extra with the code, I wouldn't know what
that
would be since I'm not familiar with the VBA side.

Just for my comfort, please post back with the SQL views of your queries
as
well as the code in your Concatenate function. Have you attempted to set
a
break point and step through the code?