Question regarding Conversion from SQL Server

[pcrabbit999]

Hi,

I am not sure if this question is too basic to be posted here, but I will give a try.
I am trying to convert from MS-SQL to Access. I am very confused as of why Access require so many parenthesis. I have converted most part of the code besides this INNER JOIN:

In SQL:

INNER JOIN SELECT x.model_id
,x.model_version_num
,x.val_fact_type_code
,pur.rep_purpose_name
,vm.val_method_name
,x.nb_rnl_type_code
,x.val_date
,max(x.val_model_load_id) as val_model_load_id
FROM valuation_model_factor x
INNER JOIN reporting_purpose_mst pur
ON x.rep_purpose_id = pur.rep_purpose_id
INNER JOIN valuation_method_mst vm
ON x.val_method_id = vm.val_method_id
GROUP BY x.model_id
,x.model_version_num
,x.val_fact_type_code
,pur.rep_purpose_name
,vm.val_method_name
,x.nb_rnl_type_code
,x.val_date] AS ivmf

ON ivmf.model_id = f.model_id
AND ivmf.model_version_num = f.model_version_num
AND ivmf.val_fact_type_code = f.val_fact_type_code
AND ivmf.rep_purpose_name = pur.rep_purpose_name
AND ivmf.val_method_name = vm.val_method_name
AND ivmf.nb_rnl_type_code = f.nb_rnl_type_code
AND ivmf.val_date = f.val_date
AND ivmf.val_model_load_id = f.val_model_load_id

In Access(Doesn't get parsed for syntax):

INNER JOIN [SELECT x.model_id
,x.model_version_num
,x.val_fact_type_code
,pur.rep_purpose_name
,vm.val_method_name
,x.nb_rnl_type_code
,x.val_date
,max(x.val_model_load_id) as val_model_load_id
FROM ((valuation_model_factor x
INNER JOIN reporting_purpose_mst pur
ON x.rep_purpose_id = pur.rep_purpose_id)
INNER JOIN valuation_method_mst vm
ON x.val_method_id = vm.val_method_id)
GROUP BY x.model_id
,x.model_version_num
,x.val_fact_type_code
,pur.rep_purpose_name
,vm.val_method_name
,x.nb_rnl_type_code
,x.val_date] AS ivmf

ON ivmf.model_id = f.model_id
AND ivmf.model_version_num = f.model_version_num
AND ivmf.val_fact_type_code = f.val_fact_type_code
AND ivmf.rep_purpose_name = pur.rep_purpose_name
AND ivmf.val_method_name = vm.val_method_name
AND ivmf.nb_rnl_type_code = f.nb_rnl_type_code
AND ivmf.val_date = f.val_date
AND ivmf.val_model_load_id = f.val_model_load_id


Despite having comments about Access's syntax, I want to get this working. Does anyone have any idea?

I thank you in advance.