Excel MS Query using Access DB: issue is Too Few Paramters. Expected 1

[makeitwork09]

Hello,

I am using Microsoft Excel and Access 2002 on Windows EP.

Before recording the query I'm going to build, I am first testing the query using Excel MS Query. The code element below in bold is resulting in an error message of Too few parameters. Expected 1. I know using SQL Server this would work, but Access, or Excel, doesn't seem to like it.

SELECT Dec_2009.accounting_date, Dec_2009.loan, Dec_2009.loan_xref, Dec_2009.loan_kind, Dec_2009.loan_name, Dec_2009.borrower, Dec_2009.closed_date, Dec_2009.mat_date, Dec_2009.curr_int_rate, Dec_2009.curr_loan_term, Dec_2009.rem_loan_term, Dec_2009.amrt_perd, Dec_2009.io_period, Dec_2009.interest_type, Dec_2009.prim_ind, Dec_2009.property, Dec_2009.region, Dec_2009.city, Dec_2009.state, Dec_2009.county, Dec_2009.prop_type, Dec_2009.system_type, Dec_2009.acli_region, Dec_2009.loan_final_value, Dec_2009.ltv, Dec_2009.units, Dec_2009.statement_date, Dec_2009.dcr, Dec_2009.net_income, Dec_2009.ann_debt_serv, Dec_2009.occ, Dec_2009.debt_yield, Dec_2009.field_office, Dec_2009.funding_year, Dec_2009.loanlevel_prin, Dec_2009.orig_principal,[b]curr_prin.prin[/b]
FROM `C:\Initiatives\2009\Datamart\regdatamart`.Dec_2009 Dec_2009
LEFT OUTER JOIN  (select Dec_2009_2.loan,sum([Dec_2009_2.curr_principal]) AS prin
from `C:\Initiatives\2009\Datamart\regdatamart`.Dec_2009 AS Dec_2009_2
GROUP BY Dec_2009_2.loan) AS curr_prin on
curr_prin.loan = Dec_2009.loan
WHERE Dec_2009.prim_ind = 'y' and
not(Dec_2009.loan_kind like 'sf%')
GROUP BY
Dec_2009.accounting_date, Dec_2009.loan, Dec_2009.loan_xref, Dec_2009.loan_kind, Dec_2009.loan_name, Dec_2009.borrower, Dec_2009.closed_date, Dec_2009.mat_date, Dec_2009.curr_int_rate, Dec_2009.curr_loan_term, Dec_2009.rem_loan_term, Dec_2009.amrt_perd, Dec_2009.io_period, Dec_2009.interest_type, Dec_2009.prim_ind, Dec_2009.property, Dec_2009.region, Dec_2009.city, Dec_2009.state, Dec_2009.county, Dec_2009.prop_type, Dec_2009.system_type, Dec_2009.acli_region, Dec_2009.loan_final_value, Dec_2009.ltv, Dec_2009.units, Dec_2009.statement_date, Dec_2009.dcr, Dec_2009.net_income, Dec_2009.ann_debt_serv, Dec_2009.occ, Dec_2009.debt_yield, Dec_2009.field_office, Dec_2009.funding_year, Dec_2009.loanlevel_prin, Dec_2009.orig_principal,curr_prin.loan

In place of curr_prin.prin I put curr_prin.loan and that worked.

Thanks in advance for the help.

 

[SkipVought]

Hi,

I know using SQL Server this would work, but Access, or Excel, doesn't seem to like it.

I never coded a query with a table linked to SQL Server in MS Access.

I have, however coded queries linked to Oracle and DB2, and what happens, is that your code needs to CHANGE, using Jet SQL. So I am certain that you will need to change your syntax in some cases.

But it seems that you have prin in the Select clause and ;oan in the Group By clause...

SELECT
  A.accounting_date
, A.loan
, A.loan_xref
, A.loan_kind
, A.loan_name
, A.borrower
, A.closed_date
, A.mat_date
, A.curr_int_rate
, A.curr_loan_term
, A.rem_loan_term
, A.amrt_perd
, A.io_period
, A.interest_type
, A.prim_ind
, A.property
, A.region
, A.city
, A.state
, A.county
, A.prop_type
, A.system_type
, A.acli_region
, A.loan_final_value
, A.ltv
, A.units
, A.statement_date
, A.dcr
, A.net_income
, A.ann_debt_serv
, A.occ
, A.debt_yield
, A.field_office
, A.funding_year
, A.loanlevel_prin
, A.orig_principal [b][red]
, C.prin[/red][/b]

FROM `C:\Initiatives\2009\Datamart\regdatamart`.Dec_2009 A
LEFT OUTER JOIN  
(
select
  B.loan
, sum([B.curr_principal]) AS prin

from `C:\Initiatives\2009\Datamart\regdatamart`.Dec_2009 B

GROUP BY B.loan
) C
on C.loan = A.loan

WHERE A.prim_ind = 'y'
  and A.loan_kind not like 'sf%'

GROUP BY
  A.accounting_date
, A.loan
, A.loan_xref
, A.loan_kind
, A.loan_name
, A.borrower
, A.closed_date
, A.mat_date
, A.curr_int_rate
, A.curr_loan_term
, A.rem_loan_term
, A.amrt_perd
, A.io_period
, A.interest_type
, A.prim_ind
, A.property
, A.region
, A.city
, A.state
, A.county
, A.prop_type
, A.system_type
, A.acli_region
, A.loan_final_value
, A.ltv
, A.units
, A.statement_date
, A.dcr
, A.net_income
, A.ann_debt_serv
, A.occ
, A.debt_yield
, A.field_office
, A.funding_year
, A.loanlevel_prin
, A.orig_principal [b][red]
, C.loan[/red][/b]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}