G
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.
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 said: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 said: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!