Link to home
Start Free TrialLog in
Avatar of kris_sk2012
kris_sk2012Flag for United States of America

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,IIf(Trim([c_num])="abc","N/A",[c_num]),"No") 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")
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
INSERT INTO final_table ( sk, cfk, cfk_up, el_In, C_M, u_c )
SELECT DISTINCT
    first_table.sk, first_table.cfk, first_table.cfk_up, first_table.p_el_In,
IIf(Len(Trim([c_num]))>2,IIf(Trim([c_num])="abc","N/A",[c_num]),"No") AS C_M, IIf([r_c]="Y","Y","N") AS u_c
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.
Avatar of kris_sk2012

ASKER

I've removed [qry1] at the end of Iff statement and it worked! Thanks!!