I’m trying to run a query from within a query(But Run One Last)

[TBC]

I’m trying to run a query from within a query "But Run it Last".

Basically I’m wondering if I can run a query or sql in a order so that all filters and formulas in a query will run, but before it’s finished it will look at the last formula

This is the statement I would like to run at the end, after all the filters and stuff has run. If you know that this is something I can’t do for, please let me know that also and I will move on and try something different

this is the statment I would like to run last:
[sql]Master_Table_Join_to_Peer_Groups_tbl.[Past_12_Months$], Master_Table_Join_to_Peer_Groups_tbl.[Past_12_Months#], 1+DCount("*","([Master_Table_Join_to_Peer_Groups_tbl])","([Past_3_Month#])<" & ([Past_3_Month#])) AS 3Rank
[/sql]

[sql]
SELECT Master_Table_Join_to_Peer_Groups_tbl.[OMNI Number], Master_Table_Join_to_Peer_Groups_tbl.[LAST FINANCIALS RCVD], Master_Table_Join_to_Peer_Groups_tbl.[NET WORTH OF LAST FINANCIALS RCVD], Master_Table_Join_to_Peer_Groups_tbl.[Fiscal Report Date], Master_Table_Join_to_Peer_Groups_tbl.[NEXT RE-CERT LTR], Master_Table_Join_to_Peer_Groups_tbl.[US Bank -VA Sponsor], Master_Table_Join_to_Peer_Groups_tbl.[VA AUTO APPROVED], Master_Table_Join_to_Peer_Groups_tbl.[US Bank - Authorized Agent Relationship], Master_Table_Join_to_Peer_Groups_tbl.[FHA DE APPROVED], Master_Table_Join_to_Peer_Groups_tbl.[CONV DELEGATED APPROVAL DATE], Master_Table_Join_to_Peer_Groups_tbl.[CONV DELEGATED "TIER"], Master_Table_Join_to_Peer_Groups_tbl.[Easy D Approved], Master_Table_Join_to_Peer_Groups_tbl.[Easy D Lite], Master_Table_Join_to_Peer_Groups_tbl.[BRANCH #], Master_Table_Join_to_Peer_Groups_tbl.FUNDING, Master_Table_Join_to_Peer_Groups_tbl.CORRESPONDENT, Master_Table_Join_to_Peer_Groups_tbl.[ACCOUNT EXECUTIVE], Master_Table_Join_to_Peer_Groups_tbl.STATUS, Master_Table_Join_to_Peer_Groups_tbl.[DATE APPROVED], Master_Table_Join_to_Peer_Groups_tbl.BRANCH, Master_Table_Join_to_Peer_Groups_tbl.branch_name, Master_Table_Join_to_Peer_Groups_tbl.TotalLoanAmount, Master_Table_Join_to_Peer_Groups_tbl.Jan_Loan_Amount, Master_Table_Join_to_Peer_Groups_tbl.Jan_App_Count, Master_Table_Join_to_Peer_Groups_tbl.Feb_Loan_Amount, Master_Table_Join_to_Peer_Groups_tbl.Feb_App_Count, Master_Table_Join_to_Peer_Groups_tbl.Mar_Loan_Amount, Master_Table_Join_to_Peer_Groups_tbl.Mar_App_Count, Master_Table_Join_to_Peer_Groups_tbl.Apr_Loan_Amount, Master_Table_Join_to_Peer_Groups_tbl.Apr_App_Count, Master_Table_Join_to_Peer_Groups_tbl.May_Loan_Amount, Master_Table_Join_to_Peer_Groups_tbl.May_App_Count, Master_Table_Join_to_Peer_Groups_tbl.Jun_Loan_Amount, Master_Table_Join_to_Peer_Groups_tbl.Jun_App_Count, Master_Table_Join_to_Peer_Groups_tbl.Jul_Loan_Amount, Master_Table_Join_to_Peer_Groups_tbl.Jul_App_Count, Master_Table_Join_to_Peer_Groups_tbl.Aug_Loan_Amount, Master_Table_Join_to_Peer_Groups_tbl.Aug_App_Count, Master_Table_Join_to_Peer_Groups_tbl.Sept_Loan_Amount, Master_Table_Join_to_Peer_Groups_tbl.Sept_App_Count, Master_Table_Join_to_Peer_Groups_tbl.Oct_Loan_Amount, Master_Table_Join_to_Peer_Groups_tbl.Oct_App_Count, Master_Table_Join_to_Peer_Groups_tbl.Nov_Loan_Amount, Master_Table_Join_to_Peer_Groups_tbl.Nov_App_Count, Master_Table_Join_to_Peer_Groups_tbl.Dec_Loan_Amount, Master_Table_Join_to_Peer_Groups_tbl.Dec_App_Count, Master_Table_Join_to_Peer_Groups_tbl.First_3_Month_Loan_Amount, Master_Table_Join_to_Peer_Groups_tbl.First_3_Month_App_Count, Master_Table_Join_to_Peer_Groups_tbl.First_6_Month_Loan_Amount, Master_Table_Join_to_Peer_Groups_tbl.First_6_Month_App_Count, Master_Table_Join_to_Peer_Groups_tbl.First_9_Month_Loan_Amount, Master_Table_Join_to_Peer_Groups_tbl.[9Month_App_Count], Master_Table_Join_to_Peer_Groups_tbl.[12_Month_Loan_Amount_10], Master_Table_Join_to_Peer_Groups_tbl.[12Month_App_Count_10], Master_Table_Join_to_Peer_Groups_tbl.First_12_Month_App_Count, Master_Table_Join_to_Peer_Groups_tbl.[Past_3_Month$], Master_Table_Join_to_Peer_Groups_tbl.[Past_3_Month#], Master_Table_Join_to_Peer_Groups_tbl.[Past_6_Month$], Master_Table_Join_to_Peer_Groups_tbl.[Past_6_Month#], Master_Table_Join_to_Peer_Groups_tbl.[Past_9_Month$], Master_Table_Join_to_Peer_Groups_tbl.[Past_9_Month#], Master_Table_Join_to_Peer_Groups_tbl.[Past_12_Months$], Master_Table_Join_to_Peer_Groups_tbl.[Past_12_Months#], 1+DCount("*","([Master_Table_Join_to_Peer_Groups_tbl])","([Past_3_Month#])<" & ([Past_3_Month#])) AS 3Rank
FROM Master_Table_Join_to_Peer_Groups_tbl
WHERE (((Master_Table_Join_to_Peer_Groups_tbl.[BRANCH #])=49) AND ((Master_Table_Join_to_Peer_Groups_tbl.FUNDING)="TABLE") AND ((Master_Table_Join_to_Peer_Groups_tbl.STATUS)="active")) OR (((Master_Table_Join_to_Peer_Groups_tbl.[BRANCH #])=50) AND ((Master_Table_Join_to_Peer_Groups_tbl.FUNDING)="TABLE") AND ((Master_Table_Join_to_Peer_Groups_tbl.STATUS)="active")) OR (((Master_Table_Join_to_Peer_Groups_tbl.[BRANCH #])=51) AND ((Master_Table_Join_to_Peer_Groups_tbl.FUNDING)="TABLE") AND ((Master_Table_Join_to_Peer_Groups_tbl.STATUS)="active")) OR (((Master_Table_Join_to_Peer_Groups_tbl.[BRANCH #])=52) AND ((Master_Table_Join_to_Peer_Groups_tbl.FUNDING)="TABLE") AND ((Master_Table_Join_to_Peer_Groups_tbl.STATUS)="active"))
ORDER BY Master_Table_Join_to_Peer_Groups_tbl.[Past_3_Month#] DESC;[/sql]

Thanks for taking the time to help
TCB

 

[jzwp22]

SQL will always run subqueries first. The DCount() function is essentially based on a query. So when SQL sees the DCount() it will evaluate that first.

You cannot change the order in which SQL executes. The only thing you can do is to use your main query as a subquery in another query that gets you to the final result you want.

 

[vbaInet]

[sql][/sql] are not valid tags in this forum. You use [ CODE ] [ /Code ] (without the spaces)

E.g.

SELECT [OMNI Number], [LAST FINANCIALS RCVD], [NET WORTH OF LAST FINANCIALS RCVD], [Fiscal Report Date], [NEXT RE-CERT LTR], [US Bank -VA Sponsor], [VA AUTO APPROVED], [US Bank - Authorized Agent Relationship], [FHA DE APPROVED], [CONV DELEGATED APPROVAL DATE], [CONV DELEGATED "TIER"], [Easy D Approved], [Easy D Lite], [BRANCH #], FUNDING, CORRESPONDENT , [ACCOUNT EXECUTIVE], STATUS, [DATE APPROVED], BRANCH, branch_name, TotalLoanAmou nt, Jan_Loan_Amou nt, Jan_App_Count , Feb_Loan_Amou nt, Feb_App_Count , Mar_Loan_Amou nt, Mar_App_Count , Apr_Loan_Amou nt, Apr_App_Count , May_Loan_Amou nt, May_App_Count , Jun_Loan_Amou nt, Jun_App_Count , Jul_Loan_Amou nt, Jul_App_Count , Aug_Loan_Amou nt, Aug_App_Count , Sept_Loan_Amo unt, Sept_App_Coun t, Oct_Loan_Amou nt, Oct_App_Count , Nov_Loan_Amou nt, Nov_App_Count , Dec_Loan_Amou nt, Dec_App_Count , First_3_Month _Loan_Amount, First_3_Month _App_Count, First_6_Month _Loan_Amount, First_6_Month _App_Count, First_9_Month _Loan_Amount, [9Month_App_Count], [12_Month_Loan_Amount_10], [12Month_App_Count_10], First_12_Mont h_App_Count, [Past_3_Month$], [Past_3_Month#], [Past_6_Month$], [Past_6_Month#], [Past_9_Month$], [Past_9_Month#], [Past_12_Months$], [Past_12_Months#], 1+DCount("*","([Master_Table_Join_to_Peer_Groups_tbl])","([Past_3_Month#])<" & ([Past_3_Month#])) AS 3Rank
FROM Master_Table_Join_to_Peer_Groups_tbl
WHERE ((([BRANCH #])=49) AND ((FUNDING)="T ABLE") AND ((STATUS)="ac tive")) OR ((([BRANCH #])=50) AND ((FUNDING)="T ABLE") AND ((STATUS)="ac tive")) OR ((([BRANCH #])=51) AND ((FUNDING)="T ABLE") AND ((STATUS)="ac tive")) OR ((([BRANCH #])=52) AND ((FUNDING)="T ABLE") AND ((STATUS)="ac tive"))
ORDER BY [Past_3_Month#] DESC;

By the way, if Master_Table_Join_to_Peer_Groups_tbl is a table, then it is not normalized.

 

[TBC]

thanks Guys, I will try that