Extra Column in SQL Query.

[netrusher]

The below SQL is giving me a Column named:

X7YZ_1. Revised Planned Implementation Date.
I cannot figure out how and where. Can anyone point out
my mistake so I can get rid of this column.

SELECT ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[Planned Implementation Date], ECNDetailtbl.[Revised Planned Implementation Date], "" as [Revised Planned Implementation Date], ECNPartstbl.[Part #], ECNPartstbl.[Part Description], ECNBCNVIPtbl.[ECN Analyst], ECNPartstbl.Replaces
FROM ECNBCNVIPtbl INNER JOIN (ECNDetailtbl INNER JOIN ECNPartstbl ON ECNDetailtbl.[ECNBCNVIP ID] = ECNPartstbl.[ECNBCNVIP ID]) ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID]
WHERE (((ECNBCNVIPtbl.[ECN Number])<>"sample") AND ((ECNDetailtbl.[Planned Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) AND ((ECNPartstbl.[Part #]) Not Like "*.*" And (ECNPartstbl.[Part #]) Not Like "m-list*") AND ((ECNPartstbl.[Part Description]) Not Like "pl*" And (ECNPartstbl.[Part Description]) Not Like "sl*" And (ECNPartstbl.[Part Description]) Not Like "arr*" And (ECNPartstbl.[Part Description]) Not Like "l*" And (ECNPartstbl.[Part Description]) Not Like "pl*") AND ((ECNBCNVIPtbl.[ECN Analyst])<>"Ken Perman") AND ((ECNPartstbl.[Rev Lev]) Like "N*") AND ((ECNPartstbl.Resp)="PUR") AND ((ECNBCNVIPtbl.[Do Not Process])="yes"))
UNION
SELECT ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[Planned Implementation Date], "", ECNDetailtbl.[Revised Planned Implementation Date], ECNPartstbl.[Part #], ECNPartstbl.[Part Description], ECNPartstbl.Replaces
FROM ECNBCNVIPtbl INNER JOIN (ECNDetailtbl INNER JOIN ECNPartstbl ON ECNDetailtbl.[ECNBCNVIP ID] = ECNPartstbl.[ECNBCNVIP ID]) ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID]
WHERE (((ECNBCNVIPtbl.[ECN Analyst])<>"Ken Perman") AND ((ECNBCNVIPtbl.[ECN Number])<>"sample") AND ((ECNDetailtbl.[Revised Planned Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) AND ((ECNPartstbl.[Part #]) Not Like "*.*" And (ECNPartstbl.[Part #]) Not Like "m-list*") AND ((ECNPartstbl.[Part Description]) Not Like "pl*" And (ECNPartstbl.[Part Description]) Not Like "sl*" And (ECNPartstbl.[Part Description]) Not Like "arr*" And (ECNPartstbl.[Part Description]) Not Like "l*" And (ECNPartstbl.[Part Description]) Not Like "pl*") AND ((ECNPartstbl.[Rev Lev]) Like "N*") AND ((ECNPartstbl.Resp)="PUR") AND ((ECNBCNVIPtbl.[Do Not Process])="yes"))
ORDER BY ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[Planned Implementation Date], ECNPartstbl.[Part #];

 

[MichaelRed]

Look at the column order in the output. Check the adjacent columns names. the term providung the alais column name is adjacent to the ones in the list.




MichaelRed

 

[Golom]

If you reformat your first SELECT ...

SELECT 
   ECNBCNVIPtbl.[ECN Number], 
   ECNDetailtbl.[Planned Implementation Date], 
   [red]ECNDetailtbl.[Revised Planned Implementation Date], 
   "" as [Revised Planned Implementation Date], [/red]
   ECNPartstbl.[Part #], 
   ECNPartstbl.[Part Description], 
   ECNBCNVIPtbl.[ECN Analyst], 
   ECNPartstbl.Replaces

You will probably notice that you have attempted to assign an alias with the same name as an existing field.