convert

[geebee]

hi,

i have the following which was a pass-through query (i have modified it
quite a bit to get it to where it is now). it looked like it was written in
TSQL or something... but I now have:

select

z.loan_id,

z.deal_id,

z.vblock,

z.issue_id,

z.issue_code,

z.borrw_name_corrected,

z.exception_requestor_name,

stat_claim_ms,

z.bal_orig,

z.dt_purch,

z.business_line,

z.code_lien_stat,

z.code_origination_desc,

z.loan_stat_srvcr_curr,

z.correspondent_id,

z.seller,

cpty.code_approval_status,

z.res_score,

z.prin_bal_curr_rpt,

z.category_code,

z.referral_dt,

z.completion_code,

z.completion_dt,

z.amt_rcvd,

open_rcf,

rcf_notice_dt,

completed,

completed_notice_dt,

cc.completion_code AS rcf_completion_code,

cc.completion_dt AS rcf_completion_dt,

cc!amt_rcvd AS rcf_amt_recd

from vw_cp_rpt_loan_level_rca AS z

left join dbo_counterparty AS cpty

on z.correspondent_id=cpty.counterparty_id

left join

(

select cli.loan_num_morgan_stanley AS vblocknum, cli.issue_code,

cli.notice_dt, cli.completion_code,cli.completion_dt,cli.amt_rcvd

from dbo_cp_loan_issues AS cli inner join

(

select a.loan_num_morgan_stanley AS vblocknum, min(issue_id) AS issueid,

from dbo_cp_loan_issues AS a

inner join (select loan_num_morgan_stanley, c.notice_dt

from dbo_cp_loan_issues AS c

where c.category_code = 'RCA'

) AS b on a.loan_num_morgan_stanley = b.loan_num_morgan_stanley



where a.notice_dt >= b.notice_dt

and a.category_code = 'RCF'

group by a.loan_num_morgan_stanley

) AS x on cli.issue_id = x.issueid



) AS dd on z.vblock = dd.vblocknum



left join

(

select vblocknum: cli!loan_num_morgan_stanley, cli.issue_code,

cli.notice_dt, cli.completion_code,cli.completion_dt,cli.amt_rcvd

from dbo_cp_loan_issues AS cli inner join

(

select a.loan_num_morgan_stanley AS vblocknum, max(issue_id) AS issue_id

from dbo_cp_loan_issues AS a

inner join (select loan_num_morgan_stanley, c.notice_dt

from dbo_cp_loan_issues AS c

where c.category_code = 'RCA'

) AS b on a.loan_num_morgan_stanley = b.loan_num_morgan_stanley



where a.notice_dt >= b.notice_dt

and a.category_code = 'RCF'

group by a.loan_num_morgan_stanley

) AS x on cli.issue_id = x.issue_id



) cc on z.vblock = cc.vblocknum



where z.category_code = 'RCA'

and z.completion_dt is not null;


i am getting an error message pointing to the following part when I try to
save it:

left join

(

select cli.loan_num_morgan_stanley AS vblocknum, cli.issue_code,

cli.notice_dt, cli.completion_code,cli.completion_dt,cli.amt_rcvd

from dbo_cp_loan_issues AS cli inner join

(

I am not sure what to do, but I just need help changing this into a useable
query in Access.

Thanks in advance,
geebee

 

[John Spencer]

A subquery in Access must be enclosed in parentheses and the alias for
the subquery must be declared outside the parentheses

left join

(
select cli.loan_num_morgan_stanley AS vblocknum, cli.issue_code,
cli.notice_dt, cli.completion_code,cli.completion_dt,cli.amt_rcvd
from dbo_cp_loan_issues
) AS cli inner join

Also Access requires a lot of parentheses when using more than two
tables or subqueries in the FROM clause. For instance with four tables
you would need

FROM ((Table1 Inner Join Table2
On Table1.FieldA = Table2.FieldA)
Inner Join Table3
ON Table2.Fieldb =Table3.FieldB)
INNER JOIN Table4
ON Table1.FieldA = Table4.FieldA

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================