[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL statement help needed

Posted on 2012-08-23
3
Medium Priority
?
558 Views
Last Modified: 2012-08-23
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")
0
Comment
Question by:kris_sk2012
3 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 38327119
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, [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")
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38327234
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.
0
 

Author Closing Comment

by:kris_sk2012
ID: 38327355
I've removed [qry1] at the end of Iff statement and it worked! Thanks!!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question