G
GMC
Hello all, using Access 2007 w/ service pack 2 on Window XP service pack 3.
I created an append query that is supposed to select fields from two tables
in a database and append the data into another table in a different
database. I have all tables linked in one database that I am trying to run
the query in.
INSERT INTO ELIGIBILITY2 ( transferstate, Casenum, Eligible, Dopen, Adults,
Children, CFname, CLname, CMI, Ext, Income1N, Income1A, Income2N, Income2A,
Income3N, Income3A, Income4N, Income4A, Income5N, Income5A, IncomeT,
Asset1N, Asset1A, Asset2N, Asset2A, Asset3N, Asset3A, Asset4N, Asset4A,
Asset5N, Asset5A, AssetT, FNADV, LNADV, CitizenCheck, ConflictCheck,
RREJECT, LSCElig, OFFICENUM, Notes )
SELECT CLIENTSW.TransferState, ELIGIBILITY.Casenum, ELIGIBILITY.Eligible,
Date() AS Expr2, ELIGIBILITY.Adults, ELIGIBILITY.Children,
ELIGIBILITY.CFname, ELIGIBILITY.CLname, ELIGIBILITY.CMI, ELIGIBILITY.Ext,
ELIGIBILITY.Income1N, ELIGIBILITY.Income1A, ELIGIBILITY.Income2N,
ELIGIBILITY.Income2A, ELIGIBILITY.Income3N, ELIGIBILITY.Income3A,
ELIGIBILITY.Income4N, ELIGIBILITY.Income4A, ELIGIBILITY.Income5N,
ELIGIBILITY.Income5A, ELIGIBILITY.IncomeT, ELIGIBILITY.Asset1N,
ELIGIBILITY.Asset1A, ELIGIBILITY.Asset2N, ELIGIBILITY.Asset2A,
ELIGIBILITY.Asset3N, ELIGIBILITY.Asset3A, ELIGIBILITY.Asset4N,
ELIGIBILITY.Asset4A, ELIGIBILITY.Asset5N, ELIGIBILITY.Asset5A,
ELIGIBILITY.AssetT, ELIGIBILITY.FNADV, ELIGIBILITY.LNADV,
ELIGIBILITY.CitizenCheck, ELIGIBILITY.ConflictCheck, ELIGIBILITY.RREJECT,
ELIGIBILITY.LSCElig, 9 AS Expr1, EligibilityNote.Notes
FROM (CLIENTSW INNER JOIN ELIGIBILITY ON CLIENTSW.CASENUM =
ELIGIBILITY.Casenum) LEFT JOIN EligibilityNote ON CLIENTSW.CASENUM =
EligibilityNote.Casenum
WHERE (((CLIENTSW.TransferState)=True) AND
((CLIENTSW.DCLOSED)=#5/12/2009#));
This worked for about two months and one day it wouldn't work any longer. I
received error 3167 Record Deleted on this query when there wasn't a value
in the EligibilityNote table for each casenum in the Eligibility table. I
looked back to days before this error occured. There were many occurences
where there was not a matching eligibilitynote value in the table.
What could the problem be?
Thanks.
GMC
I created an append query that is supposed to select fields from two tables
in a database and append the data into another table in a different
database. I have all tables linked in one database that I am trying to run
the query in.
INSERT INTO ELIGIBILITY2 ( transferstate, Casenum, Eligible, Dopen, Adults,
Children, CFname, CLname, CMI, Ext, Income1N, Income1A, Income2N, Income2A,
Income3N, Income3A, Income4N, Income4A, Income5N, Income5A, IncomeT,
Asset1N, Asset1A, Asset2N, Asset2A, Asset3N, Asset3A, Asset4N, Asset4A,
Asset5N, Asset5A, AssetT, FNADV, LNADV, CitizenCheck, ConflictCheck,
RREJECT, LSCElig, OFFICENUM, Notes )
SELECT CLIENTSW.TransferState, ELIGIBILITY.Casenum, ELIGIBILITY.Eligible,
Date() AS Expr2, ELIGIBILITY.Adults, ELIGIBILITY.Children,
ELIGIBILITY.CFname, ELIGIBILITY.CLname, ELIGIBILITY.CMI, ELIGIBILITY.Ext,
ELIGIBILITY.Income1N, ELIGIBILITY.Income1A, ELIGIBILITY.Income2N,
ELIGIBILITY.Income2A, ELIGIBILITY.Income3N, ELIGIBILITY.Income3A,
ELIGIBILITY.Income4N, ELIGIBILITY.Income4A, ELIGIBILITY.Income5N,
ELIGIBILITY.Income5A, ELIGIBILITY.IncomeT, ELIGIBILITY.Asset1N,
ELIGIBILITY.Asset1A, ELIGIBILITY.Asset2N, ELIGIBILITY.Asset2A,
ELIGIBILITY.Asset3N, ELIGIBILITY.Asset3A, ELIGIBILITY.Asset4N,
ELIGIBILITY.Asset4A, ELIGIBILITY.Asset5N, ELIGIBILITY.Asset5A,
ELIGIBILITY.AssetT, ELIGIBILITY.FNADV, ELIGIBILITY.LNADV,
ELIGIBILITY.CitizenCheck, ELIGIBILITY.ConflictCheck, ELIGIBILITY.RREJECT,
ELIGIBILITY.LSCElig, 9 AS Expr1, EligibilityNote.Notes
FROM (CLIENTSW INNER JOIN ELIGIBILITY ON CLIENTSW.CASENUM =
ELIGIBILITY.Casenum) LEFT JOIN EligibilityNote ON CLIENTSW.CASENUM =
EligibilityNote.Casenum
WHERE (((CLIENTSW.TransferState)=True) AND
((CLIENTSW.DCLOSED)=#5/12/2009#));
This worked for about two months and one day it wouldn't work any longer. I
received error 3167 Record Deleted on this query when there wasn't a value
in the EligibilityNote table for each casenum in the Eligibility table. I
looked back to days before this error occured. There were many occurences
where there was not a matching eligibilitynote value in the table.
What could the problem be?
Thanks.
GMC