asked on

make query updateable

I have an update query that I get the error query not updateable when I run it. I have posted the code below. How can I make this updateable? Do I need to split it into more than one query?

UPDATE qryAvgPrev12MoAVS INNER JOIN (tblClients INNER JOIN tblAVS ON tblClients.ClientNumber = tblAVS.ClientNumber) ON qryAvgPrev12MoAVS.ClientNumber = tblClients.ClientNumber SET tblAVS.UnearnedsDiscountsDlrReservesEtcPct = UnearnedsDiscountsDlrReservesEtcPctReturnCalc([UnearnedsDiscountsDlrReservesEtcDolrs],[tblAVS].[EndingGrossAR]), tblAVS.IneligiblesPct = IneligiblesPctReturnCalc([tblAVS].[ClientNumber],[tblAVS].[EndingGrossAR],[IneligiblesDolrs],[IneligiblesReported],[IneligiblesUnreported]), tblAVS.EligibleReceivables = EligiblereceivablesReturnCalc([tblAVS].[ClientNumber],[tblAVS].[EndingGrossAR],[UnearnedsDiscountsDlrReservesEtcDolrs],[IneligiblesDolrs],[AccruedInterest],[UnearnedInterestDolrs],[UnearnedFinanceCharges],[UnearnedInsuranceCommissions],[IneligiblesReported],[IneligiblesUnreported]), tblAVS.UnearnedInterestPct = UnearnedInterestPctReturnCalc([UnearnedInterestDolrs],[tblAVS].[EndingGrossAR]), tblAVS.CollectionsPctOfGrossAR = CollectionsPctToGrossARReturnCalc([CashCollectionsDolrs],[SecuritizCash],[tblAVS].[EndingGrossAR]), tblAVS.NetAvailability = NetAvailabilityReturnCalc([ExcessAvailability],[LetterOfCredit]), tblAVS.RenewalRatesPct = RenewalRatesPctReturnCalc([BeginningGrossAR],[Renewals]), tblAVS.CashCollectionsPct = CashCollectionsPctReturnCalc([tblAVS].[ClientNumber],[CashCollectionsDolrs],[BeginningGrossAR],[tblAVS].[EndingGrossAR]), tblAVS.ExcessPct = ExcessPctReturnCalc([ExcessAvailability],[TotalAvailability]), tblAVS.UnearnedPct = UnearnedPctReturnCalc([tblAVS].[EndingGrossAR],[UnearnedFinanceCharges],[UnearnedInsuranceCommissions]), tblAVS.CashCollectionToLoanBalancePct = CashCollectionsToLoanBalancePctReturnCalc([tblAVS].[ClientNumber],[CashCollectionsDolrs],[LoanBalance],[ExcessAvailability],[TotalAvailability],[LetterOfCredit],[LB],[CCD])
WHERE (((tblAVS.DateOfData)=[Forms]![frmSpreads]![txtDOD]));

Open in new window

YZlat

try this

UPDATE tblAVS

SET tblAVS.UnearnedsDiscountsDlrReservesEtcPct = UnearnedsDiscountsDlrReservesEtcPctReturnCalc([UnearnedsDiscountsDlrReservesEtcDolrs],[tblAVS].[EndingGrossAR]),
tblAVS.IneligiblesPct = IneligiblesPctReturnCalc([tblAVS].[ClientNumber],[tblAVS].[EndingGrossAR],[IneligiblesDolrs],[IneligiblesReported],[IneligiblesUnreported]),
tblAVS.EligibleReceivables = EligiblereceivablesReturnCalc([tblAVS].[ClientNumber],[tblAVS].[EndingGrossAR],[UnearnedsDiscountsDlrReservesEtcDolrs],[IneligiblesDolrs],[AccruedInterest],[UnearnedInterestDolrs],[UnearnedFinanceCharges],[UnearnedInsuranceCommissions],[IneligiblesReported],[IneligiblesUnreported]),
tblAVS.UnearnedInterestPct = UnearnedInterestPctReturnCalc([UnearnedInterestDolrs],[tblAVS].[EndingGrossAR]),
tblAVS.CollectionsPctOfGrossAR = CollectionsPctToGrossARReturnCalc([CashCollectionsDolrs],[SecuritizCash],[tblAVS].[EndingGrossAR]),
tblAVS.NetAvailability = NetAvailabilityReturnCalc([ExcessAvailability],[LetterOfCredit]),
tblAVS.RenewalRatesPct = RenewalRatesPctReturnCalc([BeginningGrossAR],[Renewals]),
tblAVS.CashCollectionsPct = CashCollectionsPctReturnCalc([tblAVS].[ClientNumber],[CashCollectionsDolrs],[BeginningGrossAR],[tblAVS].[EndingGrossAR]),
tblAVS.ExcessPct = ExcessPctReturnCalc([ExcessAvailability],[TotalAvailability]),
tblAVS.UnearnedPct = UnearnedPctReturnCalc([tblAVS].[EndingGrossAR],[UnearnedFinanceCharges],[UnearnedInsuranceCommissions]),
tblAVS.CashCollectionToLoanBalancePct = CashCollectionsToLoanBalancePctReturnCalc([tblAVS].[ClientNumber],[CashCollectionsDolrs],[LoanBalance],[ExcessAvailability],[TotalAvailability],[LetterOfCredit],[LB],[CCD])

INNER JOIN tblClients ON tblClients.ClientNumber = tblAVS.ClientNumber
INNER JOIN qryAvgPrev12MoAVS ON qryAvgPrev12MoAVS.ClientNumber = tblClients.ClientNumber
WHERE (((tblAVS.DateOfData)=[Forms]![frmSpreads]![txtDOD]));

nfstrong

ASKER

I received an error: Syntax error (missing operator) in query expression on the CashCollectionsToLoanBalancePctReturnCalc line
Any ideas?

nfstrong

ASKER

Also, this may help. I have attached the code for qryAvgPrev12MoAVS

SELECT Avg(tblAVS.LoanBalance) AS LB, Avg(tblAVS.CashCollectionsDolrs) AS CCD, tblAVS.ClientNumber
FROM tblAVS
WHERE (((tblAVS.DateOfData)>=DateAdd('yyyy',-1,[Forms]![frmSpreads]![txtDOD]) And (tblAVS.DateOfData)<[Forms]![frmSpreads]![txtDOD]+1))
GROUP BY tblAVS.ClientNumber;

Open in new window

YZlat

try this:

UPDATE tblAVS

SET tblAVS.UnearnedsDiscountsDlrReservesEtcPct = UnearnedsDiscountsDlrReservesEtcPctReturnCalc([UnearnedsDiscountsDlrReservesEtcDolrs],[tblAVS].[EndingGrossAR]),
tblAVS.IneligiblesPct = IneligiblesPctReturnCalc([tblAVS].[ClientNumber],[tblAVS].[EndingGrossAR],[IneligiblesDolrs],[IneligiblesReported],[IneligiblesUnreported]),
tblAVS.EligibleReceivables = EligiblereceivablesReturnCalc([tblAVS].[ClientNumber],[tblAVS].[EndingGrossAR],[UnearnedsDiscountsDlrReservesEtcDolrs],[IneligiblesDolrs],[AccruedInterest],[UnearnedInterestDolrs],[UnearnedFinanceCharges],[UnearnedInsuranceCommissions],[IneligiblesReported],[IneligiblesUnreported]),
tblAVS.UnearnedInterestPct = UnearnedInterestPctReturnCalc([UnearnedInterestDolrs],[tblAVS].[EndingGrossAR]),
tblAVS.CollectionsPctOfGrossAR = CollectionsPctToGrossARReturnCalc([CashCollectionsDolrs],[SecuritizCash],[tblAVS].[EndingGrossAR]),
tblAVS.NetAvailability = NetAvailabilityReturnCalc([ExcessAvailability],[LetterOfCredit]),
tblAVS.RenewalRatesPct = RenewalRatesPctReturnCalc([BeginningGrossAR],[Renewals]),
tblAVS.CashCollectionsPct = CashCollectionsPctReturnCalc([tblAVS].[ClientNumber],[CashCollectionsDolrs],[BeginningGrossAR],[tblAVS].[EndingGrossAR]),
tblAVS.ExcessPct = ExcessPctReturnCalc([ExcessAvailability],[TotalAvailability]),
tblAVS.UnearnedPct = UnearnedPctReturnCalc([tblAVS].[EndingGrossAR],[UnearnedFinanceCharges],[UnearnedInsuranceCommissions]),
tblAVS.CashCollectionToLoanBalancePct = CashCollectionsToLoanBalancePctReturnCalc([tblAVS].[ClientNumber],[CashCollectionsDolrs],[LoanBalance],[ExcessAvailability],[TotalAvailability],[LetterOfCredit],[LB],[CCD])
FROM tblAVS
INNER JOIN tblClients ON tblClients.ClientNumber = tblAVS.ClientNumber
INNER JOIN qryAvgPrev12MoAVS ON qryAvgPrev12MoAVS.ClientNumber = tblClients.ClientNumber
WHERE (((tblAVS.DateOfData)=[Forms]![frmSpreads]![txtDOD]));

nfstrong

ASKER

still getting the same error on the same line

YZlat

UPDATE tblAVS
INNER JOIN tblClients ON tblClients.ClientNumber = tblAVS.ClientNumber
INNER JOIN qryAvgPrev12MoAVS ON qryAvgPrev12MoAVS.ClientNumber = tblClients.ClientNumber
SET tblAVS.UnearnedsDiscountsDlrReservesEtcPct = UnearnedsDiscountsDlrReservesEtcPctReturnCalc([UnearnedsDiscountsDlrReservesEtcDolrs],[tblAVS].[EndingGrossAR]),
tblAVS.IneligiblesPct = IneligiblesPctReturnCalc([tblAVS].[ClientNumber],[tblAVS].[EndingGrossAR],[IneligiblesDolrs],[IneligiblesReported],[IneligiblesUnreported]),
tblAVS.EligibleReceivables = EligiblereceivablesReturnCalc([tblAVS].[ClientNumber],[tblAVS].[EndingGrossAR],[UnearnedsDiscountsDlrReservesEtcDolrs],[IneligiblesDolrs],[AccruedInterest],[UnearnedInterestDolrs],[UnearnedFinanceCharges],[UnearnedInsuranceCommissions],[IneligiblesReported],[IneligiblesUnreported]),
tblAVS.UnearnedInterestPct = UnearnedInterestPctReturnCalc([UnearnedInterestDolrs],[tblAVS].[EndingGrossAR]),
tblAVS.CollectionsPctOfGrossAR = CollectionsPctToGrossARReturnCalc([CashCollectionsDolrs],[SecuritizCash],[tblAVS].[EndingGrossAR]),
tblAVS.NetAvailability = NetAvailabilityReturnCalc([ExcessAvailability],[LetterOfCredit]),
tblAVS.RenewalRatesPct = RenewalRatesPctReturnCalc([BeginningGrossAR],[Renewals]),
tblAVS.CashCollectionsPct = CashCollectionsPctReturnCalc([tblAVS].[ClientNumber],[CashCollectionsDolrs],[BeginningGrossAR],[tblAVS].[EndingGrossAR]),
tblAVS.ExcessPct = ExcessPctReturnCalc([ExcessAvailability],[TotalAvailability]),
tblAVS.UnearnedPct = UnearnedPctReturnCalc([tblAVS].[EndingGrossAR],[UnearnedFinanceCharges],[UnearnedInsuranceCommissions]),
tblAVS.CashCollectionToLoanBalancePct = CashCollectionsToLoanBalancePctReturnCalc([tblAVS].[ClientNumber],[CashCollectionsDolrs],[LoanBalance],[ExcessAvailability],[TotalAvailability],[LetterOfCredit],[LB],[CCD])
WHERE (((tblAVS.DateOfData)=[Forms]![frmSpreads]![txtDOD]));

nfstrong

ASKER

now I receive this error:
Syntax error (missing operator) in query expression 'tblClients.ClientNumber = tblAVS.ClientNumber INNER JOIN qryAvgPrev12MoAVS ON qryAvgPrev12MoAVS.ClientNumber = tblClients.ClientNumber

YZlat

UPDATE (tblAVS
INNER JOIN tblClients ON tblClients.ClientNumber = tblAVS.ClientNumber)
INNER JOIN qryAvgPrev12MoAVS ON qryAvgPrev12MoAVS.ClientNumber = tblClients.ClientNumber
SET tblAVS.UnearnedsDiscountsDlrReservesEtcPct = UnearnedsDiscountsDlrReservesEtcPctReturnCalc([UnearnedsDiscountsDlrReservesEtcDolrs],[tblAVS].[EndingGrossAR]),
tblAVS.IneligiblesPct = IneligiblesPctReturnCalc([tblAVS].[ClientNumber],[tblAVS].[EndingGrossAR],[IneligiblesDolrs],[IneligiblesReported],[IneligiblesUnreported]),
tblAVS.EligibleReceivables = EligiblereceivablesReturnCalc([tblAVS].[ClientNumber],[tblAVS].[EndingGrossAR],[UnearnedsDiscountsDlrReservesEtcDolrs],[IneligiblesDolrs],[AccruedInterest],[UnearnedInterestDolrs],[UnearnedFinanceCharges],[UnearnedInsuranceCommissions],[IneligiblesReported],[IneligiblesUnreported]),
tblAVS.UnearnedInterestPct = UnearnedInterestPctReturnCalc([UnearnedInterestDolrs],[tblAVS].[EndingGrossAR]),
tblAVS.CollectionsPctOfGrossAR = CollectionsPctToGrossARReturnCalc([CashCollectionsDolrs],[SecuritizCash],[tblAVS].[EndingGrossAR]),
tblAVS.NetAvailability = NetAvailabilityReturnCalc([ExcessAvailability],[LetterOfCredit]),
tblAVS.RenewalRatesPct = RenewalRatesPctReturnCalc([BeginningGrossAR],[Renewals]),
tblAVS.CashCollectionsPct = CashCollectionsPctReturnCalc([tblAVS].[ClientNumber],[CashCollectionsDolrs],[BeginningGrossAR],[tblAVS].[EndingGrossAR]),
tblAVS.ExcessPct = ExcessPctReturnCalc([ExcessAvailability],[TotalAvailability]),
tblAVS.UnearnedPct = UnearnedPctReturnCalc([tblAVS].[EndingGrossAR],[UnearnedFinanceCharges],[UnearnedInsuranceCommissions]),
tblAVS.CashCollectionToLoanBalancePct = CashCollectionsToLoanBalancePctReturnCalc([tblAVS].[ClientNumber],[CashCollectionsDolrs],[LoanBalance],[ExcessAvailability],[TotalAvailability],[LetterOfCredit],[LB],[CCD])
WHERE (((tblAVS.DateOfData)=[Forms]![frmSpreads]![txtDOD]));

YZlat

or

UPDATE tblAVS
INNER JOIN
(tblClients INNER JOIN qryAvgPrev12MoAVS ON qryAvgPrev12MoAVS.ClientNumber = tblClients.ClientNumber)
ON tblAVS.ClientNumber=tblClients.ClientNumber
SET tblAVS.UnearnedsDiscountsDlrReservesEtcPct = UnearnedsDiscountsDlrReservesEtcPctReturnCalc([UnearnedsDiscountsDlrReservesEtcDolrs],[tblAVS].[EndingGrossAR]),
tblAVS.IneligiblesPct = IneligiblesPctReturnCalc([tblAVS].[ClientNumber],[tblAVS].[EndingGrossAR],[IneligiblesDolrs],[IneligiblesReported],[IneligiblesUnreported]),
tblAVS.EligibleReceivables = EligiblereceivablesReturnCalc([tblAVS].[ClientNumber],[tblAVS].[EndingGrossAR],[UnearnedsDiscountsDlrReservesEtcDolrs],[IneligiblesDolrs],[AccruedInterest],[UnearnedInterestDolrs],[UnearnedFinanceCharges],[UnearnedInsuranceCommissions],[IneligiblesReported],[IneligiblesUnreported]),
tblAVS.UnearnedInterestPct = UnearnedInterestPctReturnCalc([UnearnedInterestDolrs],[tblAVS].[EndingGrossAR]),
tblAVS.CollectionsPctOfGrossAR = CollectionsPctToGrossARReturnCalc([CashCollectionsDolrs],[SecuritizCash],[tblAVS].[EndingGrossAR]),
tblAVS.NetAvailability = NetAvailabilityReturnCalc([ExcessAvailability],[LetterOfCredit]),
tblAVS.RenewalRatesPct = RenewalRatesPctReturnCalc([BeginningGrossAR],[Renewals]),
tblAVS.CashCollectionsPct = CashCollectionsPctReturnCalc([tblAVS].[ClientNumber],[CashCollectionsDolrs],[BeginningGrossAR],[tblAVS].[EndingGrossAR]),
tblAVS.ExcessPct = ExcessPctReturnCalc([ExcessAvailability],[TotalAvailability]),
tblAVS.UnearnedPct = UnearnedPctReturnCalc([tblAVS].[EndingGrossAR],[UnearnedFinanceCharges],[UnearnedInsuranceCommissions]),
tblAVS.CashCollectionToLoanBalancePct = CashCollectionsToLoanBalancePctReturnCalc([tblAVS].[ClientNumber],[CashCollectionsDolrs],[LoanBalance],[ExcessAvailability],[TotalAvailability],[LetterOfCredit],[LB],[CCD])
WHERE (((tblAVS.DateOfData)=[Forms]![frmSpreads]![txtDOD]));

nfstrong

ASKER

Your second option worked, but I'm still getting a query not updateable error.

ASKER CERTIFIED SOLUTION

nfstrong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial