Problem Adding a field based on expression

  • Thread starter Thread starter ttp
  • Start date Start date
T

ttp

I have (2) tables that I have joined in a query. The link is based on
Order_Num. I would like the results to come back in a single line. But, my
current results are showing as separate lines. Can anyone assist?

Table 1:
ORDER_NUM
1245407

Table 2:
ORDER_NUM ORDER_TYPE TURBINE_TECH LINE_TYPE
1245407 Performance Services - GEII STEAM CMU Line Type
1245407 Performance Services - GEII STEAM CMU Bill Line
1245407 Performance Services - GEII STEAM Flow Line Type

Required Output:

ORDER_NUM ORDER_TYPE TURBINE_TECH LINE_TYPE1 LINE_TYPE2
1245407 Performance Services STEAM CMU Bill Line Flow Line Type

Basically I would like to take from a alphabetical list the 1st and last
line type. I also insert a new field allocated_detail based upon the
following expression:

IIf((Mid([ERP export]![LINE_TYPE],1,4)="Flow" Or Mid([ERP
export]![LINE_TYPE],1,6)="Eng Fl" Or Mid([ERP
export]![LINE_TYPE],1,3)="CMUl") And Left([ERP
export]![TURBINE_TECH],3)="STE","Steam Flow"," ")
Click to expand...

After trying to troubleshoot the issue, I find this expression in the field
is causing the output to be displays as 2 separate lines.
 
Try these two queries --
ttp_Order ---
SELECT Q.ORDER_NUM, Q.ORDER_TYPE, Q.TURBINE_TECH, Q.LINE_TYPE, (select
count(*) from ttp as Q1
where Q1.ORDER_NUM = Q.ORDER_NUM
and Q1.ORDER_TYPE >= Q.ORDER_TYPE
and Q1.TURBINE_TECH >= Q.TURBINE_TECH
and Q1.LINE_TYPE >= Q.LINE_TYPE) AS rank
FROM ttp AS Q
GROUP BY Q.ORDER_NUM, Q.LINE_TYPE, Q.ORDER_TYPE, Q.TURBINE_TECH;


SELECT ttp_Order.ORDER_NUM, ttp_Order.ORDER_TYPE AS ORDER_TYPE_1,
ttp_Order.TURBINE_TECH AS TURBINE_TECH_1, ttp_Order.LINE_TYPE AS LINE_TYPE_1,
ttp_Order_1.ORDER_TYPE AS ORDER_TYPE_2, ttp_Order_1.TURBINE_TECH AS
TURBINE_TECH_2, ttp_Order_1.LINE_TYPE AS LINE_TYPE_2, ttp_Order_2.ORDER_TYPE
AS ORDER_TYPE_3, ttp_Order_2.TURBINE_TECH AS TURBINE_TECH_3,
ttp_Order_2.LINE_TYPE AS LINE_TYPE_3
FROM (ttp_Order LEFT JOIN ttp_Order AS ttp_Order_1 ON ttp_Order.ORDER_NUM =
ttp_Order_1.ORDER_NUM) LEFT JOIN ttp_Order AS ttp_Order_2 ON
ttp_Order.ORDER_NUM = ttp_Order_2.ORDER_NUM
WHERE (((ttp_Order.rank)=1) AND ((ttp_Order_1.rank)=2) AND
((ttp_Order_2.rank)=3));

--
KARL DEWEY
Build a little - Test a little


ttp said:
I have (2) tables that I have joined in a query. The link is based on
Order_Num. I would like the results to come back in a single line. But, my
current results are showing as separate lines. Can anyone assist?

Table 1:
ORDER_NUM
1245407

Table 2:
ORDER_NUM ORDER_TYPE TURBINE_TECH LINE_TYPE
1245407 Performance Services - GEII STEAM CMU Line Type
1245407 Performance Services - GEII STEAM CMU Bill Line
1245407 Performance Services - GEII STEAM Flow Line Type

Required Output:

ORDER_NUM ORDER_TYPE TURBINE_TECH LINE_TYPE1 LINE_TYPE2
1245407 Performance Services STEAM CMU Bill Line Flow Line Type

Basically I would like to take from a alphabetical list the 1st and last
line type. I also insert a new field allocated_detail based upon the
following expression:

IIf((Mid([ERP export]![LINE_TYPE],1,4)="Flow" Or Mid([ERP
export]![LINE_TYPE],1,6)="Eng Fl" Or Mid([ERP
export]![LINE_TYPE],1,3)="CMUl") And Left([ERP
export]![TURBINE_TECH],3)="STE","Steam Flow"," ")
Click to expand...

After trying to troubleshoot the issue, I find this expression in the field
is causing the output to be displays as 2 separate lines.
When I add the field expression in design view, the results are displayed on
separate lines - CMU on one row and Flow on another. Without the expression,
it is fine. How can I allow this field to be different values and still have
the results on the same line?
Click to expand...
Click to expand...
 
Back
Top