I have a query that is being created in code. When the query is
executed I get the error: "The specified field 'Acct' could refer to
more than one table listed in the FROM clause".
As you can see below, the 'Acct' field is fully qualified in the
subselect. What is interesting is if I cut and re-paste the same exact
query in the SQL view of design mode for the query object, it works
fine. It even works fine if all I do is remove the semi-colon at the
end.
Here is the query:
INSERT INTO DelinquencyData ( FirstInCollections, Acct, PrimaryID,
HostDatabaseID, AcctGrp, AcctType, ProdType, LastName, FirstName,
PersonalID, BirthDate, Address, City, State, PostalCode, Country,
Phone1, Phone2, DelqAmnt, DelqDate, DaysDelq, ChargeOffAmnt,
ChargeOffDate, DaysSinceChargeOff, CurrBalance, PayoffAmnt,
PayoffGoodUntil, NextPayoffAmnt, DelqStatus, LastPaymDate,
LastPaymAmnt, PaymTotal, RecoveryFlag, OpenDate, OpenAmnt, ClosedDate,
CreditLimit, PointScore, Branch, Officer, SpecialDesc, Times15DaysDelq,
Times30DaysDelq, Times60DaysDelq, Times90DaysDelq, Times120DaysDelq,
Times150DaysDelq, Times180DaysDelq, CredBurFlag, Dealer, InterestRate,
PerDiem, GLAcct, RegPmtAmnt, OrigNumPmts, NSFCount, LastNSFDate,
LastNSFCheck, LastNSFAmnt, LastPaymNSF, InterestDate, InterestDue,
MaturityDate, OverdraftProtection, ATMFlag, TextFld1, TextFld2,
TextFld3, TextFld4, TextFld7, TextFld8, TextFld9, TextFld10, TextFld12,
TextFld13, TextFld14, DateFld7, DateFld11 )
SELECT Now() AS FirstInCollections, ImportData.Acct,
ImportData.PrimaryID, ImportData.HostDatabaseID, ImportData.AcctGrp,
ImportData.AcctType, ImportData.ProdType, ImportData.LastName,
ImportData.FirstName, ImportData.PersonalID, ImportData.BirthDate,
replace(nz(ImportData.Address),'|',Chr$(13) & Chr$(10)) AS
DelqDataAddress, ImportData.City, ImportData.State,
ImportData.PostalCode, ImportData.Country, ImportData.Phone1,
ImportData.Phone2, ImportData.DelqAmnt, ImportData.DelqDate,
ImportData.DaysDelq, ImportData.ChargeOffAmnt,
ImportData.ChargeOffDate, ImportData.DaysSinceChargeOff,
ImportData.CurrBalance, ImportData.PayoffAmnt,
ImportData.PayoffGoodUntil, ImportData.NextPayoffAmnt,
ImportData.DelqStatus, ImportData.LastPaymDate,
ImportData.LastPaymAmnt, ImportData.PaymTotal, ImportData.RecoveryFlag,
ImportData.OpenDate, ImportData.OpenAmnt, ImportData.ClosedDate,
ImportData.CreditLimit, ImportData.PointScore, ImportData.Branch,
ImportData.Officer, ImportData.SpecialDesc, ImportData.Times15DaysDelq,
ImportData.Times30DaysDelq, ImportData.Times60DaysDelq,
ImportData.Times90DaysDelq, ImportData.Times120DaysDelq,
ImportData.Times150DaysDelq, ImportData.Times180DaysDelq,
ImportData.CredBurFlag, ImportData.Dealer, ImportData.InterestRate,
ImportData.PerDiem, ImportData.GLAcct, ImportData.RegPmtAmnt,
ImportData.OrigNumPmts, ImportData.NSFCount, ImportData.LastNSFDate,
ImportData.LastNSFCheck, ImportData.LastNSFAmnt,
ImportData.LastPaymNSF, ImportData.InterestDate,
ImportData.InterestDue, ImportData.MaturityDate,
ImportData.OverdraftProtection, ImportData.ATMFlag,
ImportData.TextFld1, ImportData.TextFld2, ImportData.TextFld3,
ImportData.TextFld4, ImportData.TextFld7, ImportData.TextFld8,
ImportData.TextFld9, ImportData.TextFld10, ImportData.TextFld12,
ImportData.TextFld13, ImportData.TextFld14, ImportData.DateFld7,
ImportData.DateFld11
FROM ImportData LEFT JOIN DelinquencyData ON ImportData.Acct =
DelinquencyData.Acct
WHERE (((DelinquencyData.Acct) Is Null));
BTW, instead of the outer join, I originally had the where clause say
"where importdata.acct not in (select acct from delinquencydata)". I
got no errors on that query but it performed horribly.
Any ideas? I need to be able to generate this query in code without
having to go back and edit the query manually.
Thanks.
Kathy