Concatenation of Text as Expression In A Query

Duane: please see me second response below this one!

"Duane Hookom" wrote:

Did my reply resolve your issue?

--
Duane Hookom
MS Access MVP
--

"la knight" wrote in message
...
Yes - ORDER_NO is Text, the only field which is a number is LINE_NUMERIC

"Duane Hookom" wrote:

You missed:
"It would also help to know the data types of all field referenced in the
concatenate function"
I believe ORDER_NO is text since you compare it to "0000472".

Try:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT,
Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=""" & [V_ORDER_TEXT].[ORDER_NO] &
"""",Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


--
Duane Hookom
MS Access MVP
--

"la knight" wrote in message
...
OKAY - Here's where I stand now (I have tried so many things, I think
I've
gone crazy)

This is the SQL (WITHOUT) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


This is the SQL (WITH) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT, Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=" & [V_ORDER_TEXT.ORDER_NO],Chr(13) &
Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;

WITHOUT the Concatenation Expression I get this:

PART ORDER NO LINE NO DESCRIPTION TEXT SEQ
TEXT
15 472 1 DescriptionA
1
ABCD
15 472 1 DescriptionA
2
EFGH
15 472 2 DescriptionB
1
{this maybe blank}
15 472 2 DescriptionB
2
MY DOG
15 472 3 DescriptionB
3
SKIP

THIS IS WHAT I NEED:

PART ORDER NO DESCRIPTION TEXT
15 472 DescriptionA ABCD
EFGH
15 472 DescriptionB
MY DOG
SKIP

When I try to run the query with the Concatenation I get thi error:

Run-time error '-2147217904(80040e10)': No value given for one or more
required parameters

These are the tables: (linked ODBC - I did not create them nor can
change
them)

TABLES: V_ORDER_LINES V_ORDER TEXT
FIELDS: ORDER_NO (primary) 1-M ORDER_NO
DESCRIPTION
LINE_NUMERIC
PART
TEXT_SEQ

TEXT
Any suggestions where I'm going wrong??????

Are you sure you need V_ORDER_TEXT in the main query? I can't understand why
you need V_ORDER_TEXT stuff in the main query when this is your table in the
concatenate function....

Does this work?

SELECT PART, ORDER_NO, DESCRIPTION,
Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=""" & [ORDER_NO] &
""" ORDER BY TEXT_SEQ",Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES
WHERE PART="CUSTOM15" AND
ORDER_NO="0000472"
ORDER BY DESCRIPTION;

--
Duane Hookom
MS Access MVP
--

"la knight" wrote in message
news
Upon trying your new suggestion, I get this error messgae:
"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's clause. Revise the
SELECT
statement of the Subquery to request only one field"

"Duane Hookom" wrote:

You missed:
"It would also help to know the data types of all field referenced in the
concatenate function"
I believe ORDER_NO is text since you compare it to "0000472".

Try:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT,
Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=""" & [V_ORDER_TEXT].[ORDER_NO] &
"""",Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


--
Duane Hookom
MS Access MVP
--

"la knight" wrote in message
...
OKAY - Here's where I stand now (I have tried so many things, I think
I've
gone crazy)

This is the SQL (WITHOUT) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


This is the SQL (WITH) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT, Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=" & [V_ORDER_TEXT.ORDER_NO],Chr(13) &
Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;

WITHOUT the Concatenation Expression I get this:

PART ORDER NO LINE NO DESCRIPTION TEXT SEQ
TEXT
15 472 1 DescriptionA
1
ABCD
15 472 1 DescriptionA
2
EFGH
15 472 2 DescriptionB
1
{this maybe blank}
15 472 2 DescriptionB
2
MY DOG
15 472 3 DescriptionB
3
SKIP

THIS IS WHAT I NEED:

PART ORDER NO DESCRIPTION TEXT
15 472 DescriptionA ABCD
EFGH
15 472 DescriptionB
MY DOG
SKIP

When I try to run the query with the Concatenation I get thi error:

Run-time error '-2147217904(80040e10)': No value given for one or more
required parameters

These are the tables: (linked ODBC - I did not create them nor can
change
them)

TABLES: V_ORDER_LINES V_ORDER TEXT
FIELDS: ORDER_NO (primary) 1-M ORDER_NO
DESCRIPTION
LINE_NUMERIC
PART
TEXT_SEQ

TEXT
Any suggestions where I'm going wrong??????

I tried both your suggestions again and now I'm getting syntax error.

"Duane Hookom" wrote:

Are you sure you need V_ORDER_TEXT in the main query? I can't understand why
you need V_ORDER_TEXT stuff in the main query when this is your table in the
concatenate function....

Does this work?

SELECT PART, ORDER_NO, DESCRIPTION,
Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=""" & [ORDER_NO] &
""" ORDER BY TEXT_SEQ",Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES
WHERE PART="CUSTOM15" AND
ORDER_NO="0000472"
ORDER BY DESCRIPTION;

--
Duane Hookom
MS Access MVP
--

"la knight" wrote in message
news
Upon trying your new suggestion, I get this error messgae:
"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's clause. Revise the
SELECT
statement of the Subquery to request only one field"

"Duane Hookom" wrote:

You missed:
"It would also help to know the data types of all field referenced in the
concatenate function"
I believe ORDER_NO is text since you compare it to "0000472".

Try:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT,
Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=""" & [V_ORDER_TEXT].[ORDER_NO] &
"""",Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


--
Duane Hookom
MS Access MVP
--

"la knight" wrote in message
...
OKAY - Here's where I stand now (I have tried so many things, I think
I've
gone crazy)

This is the SQL (WITHOUT) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


This is the SQL (WITH) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT, Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=" & [V_ORDER_TEXT.ORDER_NO],Chr(13) &
Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;

WITHOUT the Concatenation Expression I get this:

PART ORDER NO LINE NO DESCRIPTION TEXT SEQ
TEXT
15 472 1 DescriptionA
1
ABCD
15 472 1 DescriptionA
2
EFGH
15 472 2 DescriptionB
1
{this maybe blank}
15 472 2 DescriptionB
2
MY DOG
15 472 3 DescriptionB
3
SKIP

THIS IS WHAT I NEED:

PART ORDER NO DESCRIPTION TEXT
15 472 DescriptionA ABCD
EFGH
15 472 DescriptionB
MY DOG
SKIP

When I try to run the query with the Concatenation I get thi error:

Run-time error '-2147217904(80040e10)': No value given for one or more
required parameters

These are the tables: (linked ODBC - I did not create them nor can
change
them)

TABLES: V_ORDER_LINES V_ORDER TEXT
FIELDS: ORDER_NO (primary) 1-M ORDER_NO
DESCRIPTION
LINE_NUMERIC
PART
TEXT_SEQ

TEXT
Any suggestions where I'm going wrong??????

I am having trouble seeing your "syntax error" (or your syntax) and really
don't know if you need V_ORDER_TEXT in your main query.

How about some help?

--
Duane Hookom
MS Access MVP
--

"la knight" wrote in message
...
I tried both your suggestions again and now I'm getting syntax error.

"Duane Hookom" wrote:

Are you sure you need V_ORDER_TEXT in the main query? I can't understand
why
you need V_ORDER_TEXT stuff in the main query when this is your table in
the
concatenate function....

Does this work?

SELECT PART, ORDER_NO, DESCRIPTION,
Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=""" & [ORDER_NO] &
""" ORDER BY TEXT_SEQ",Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES
WHERE PART="CUSTOM15" AND
ORDER_NO="0000472"
ORDER BY DESCRIPTION;

--
Duane Hookom
MS Access MVP
--

"la knight" wrote in message
news
Upon trying your new suggestion, I get this error messgae:
"You have written a subquery that can return more than one field
without
using the EXISTS reserved word in the main query's clause. Revise the
SELECT
statement of the Subquery to request only one field"

"Duane Hookom" wrote:

You missed:
"It would also help to know the data types of all field referenced in
the
concatenate function"
I believe ORDER_NO is text since you compare it to "0000472".

Try:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT,
Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=""" & [V_ORDER_TEXT].[ORDER_NO] &
"""",Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


--
Duane Hookom
MS Access MVP
--

"la knight" wrote in message
...
OKAY - Here's where I stand now (I have tried so many things, I
think
I've
gone crazy)

This is the SQL (WITHOUT) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO
=
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


This is the SQL (WITH) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT, Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=" & [V_ORDER_TEXT.ORDER_NO],Chr(13) &
Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO
=
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;

WITHOUT the Concatenation Expression I get this:

PART ORDER NO LINE NO DESCRIPTION TEXT SEQ
TEXT
15 472 1 DescriptionA
1
ABCD
15 472 1 DescriptionA
2
EFGH
15 472 2 DescriptionB
1
{this maybe blank}
15 472 2 DescriptionB
2
MY DOG
15 472 3 DescriptionB
3
SKIP

THIS IS WHAT I NEED:

PART ORDER NO DESCRIPTION TEXT
15 472 DescriptionA ABCD
EFGH
15 472 DescriptionB
MY
DOG
SKIP

When I try to run the query with the Concatenation I get thi error:

Run-time error '-2147217904(80040e10)': No value given for one or
more
required parameters

These are the tables: (linked ODBC - I did not create them nor can
change
them)

TABLES: V_ORDER_LINES V_ORDER
TEXT
FIELDS: ORDER_NO (primary) 1-M ORDER_NO
DESCRIPTION
LINE_NUMERIC
PART
TEXT_SEQ

TEXT
Any suggestions where I'm going wrong??????

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!

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
--

"la knight" wrote in message
...
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!

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.

"Duane Hookom" wrote:

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
--

"la knight" wrote in message
...
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!

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

"Duane Hookom" wrote:

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
--

"la knight" wrote in message
...
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!

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
--

"la knight" wrote in message
...
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

"Duane Hookom" wrote:

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
--

"la knight" wrote in message
...
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!

NO - I haven't forgotten.

"Duane Hookom" wrote:

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
--

"la knight" wrote in message
...
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

"Duane Hookom" wrote:

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
--

"la knight" wrote in message
...
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!