kris_sk2012
asked on
SQL statement help needed
Hi,
in the below Access SQL code I'm doing a "Where" against c_o after the final_table is generated.
But for that purpose, I'm inserting column c_o in final_table.
How can I change the code so that the column c_o is not inserted in final_table, but still a "Where" can be perfomed on qry1.c_o
I basically don't want to change the table structure of final_table where c_o is not in the first place.
PS: qry1 creates inserts in to final_table
INSERT INTO final_table ( sk, cfk, cfk_up, el_In, C_M, u_c, c_o )
SELECT DISTINCT first_table.sk, first_table.cfk, first_table.cfk_up, first_table.p_el_In,
IIf(Len(Trim([c_num]))>2,I If(Trim([c _num])="ab c","N/A",[ c_num]),"N o") AS C_M, IIf([r_c]="Y","Y","N") AS u_c, [qry1].c_o AS qry1
FROM (first_table LEFT JOIN qry1 ON first_table.cfk_up = [qry1].fk_e) LEFT JOIN el_rg ON first_table.cfk_up = el_rg.fk_e
WHERE (([qry1].c_o)="DEA")
in the below Access SQL code I'm doing a "Where" against c_o after the final_table is generated.
But for that purpose, I'm inserting column c_o in final_table.
How can I change the code so that the column c_o is not inserted in final_table, but still a "Where" can be perfomed on qry1.c_o
I basically don't want to change the table structure of final_table where c_o is not in the first place.
PS: qry1 creates inserts in to final_table
INSERT INTO final_table ( sk, cfk, cfk_up, el_In, C_M, u_c, c_o )
SELECT DISTINCT first_table.sk, first_table.cfk, first_table.cfk_up, first_table.p_el_In,
IIf(Len(Trim([c_num]))>2,I
FROM (first_table LEFT JOIN qry1 ON first_table.cfk_up = [qry1].fk_e) LEFT JOIN el_rg ON first_table.cfk_up = el_rg.fk_e
WHERE (([qry1].c_o)="DEA")
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've removed [qry1] at the end of Iff statement and it worked! Thanks!!
SELECT DISTINCT
first_table.sk, first_table.cfk, first_table.cfk_up, first_table.p_el_In,
IIf(Len(Trim([c_num]))>2,I
FROM (first_table
LEFT JOIN qry1 ON first_table.cfk_up = [qry1].fk_e)
LEFT JOIN el_rg ON first_table.cfk_up = el_rg.fk_e
WHERE
(([qry1].c_o)="DEA")
Btw, you should change the LEFT JOIN on qry1 to an INNER JOIN, since you're checking a column in qry1 for a specific value anyway.