Help with Append query, please

[EManning]

Using A2K. tblAgencyAssistance looks like this:

ID Agency Date Amount
59 AAA 6/22/05 $50
59 AAA 7/1/05 $0
59 AAA 7/15/05 $40.55
60 AAA 7/2/05 $23
60 AAA 7/15/05 $0
61....
61....
63.....
....and so on...

I create an append query in code to insert a new record for every ID in this
table. But I want to exclude ID#59, date 6/22/05 from being inserted.
Here's my query:

INSERT INTO tblAgencyAssistance ( ID, AgencyAssistance, AssistanceDate,
AssistanceAmount )
SELECT DISTINCT tblAgencyAssistance.ID, "AAA" AS Expr1,
tblAgencyAssistance.AssistanceDate, 0 AS Expr2
FROM tblAgencyAssistance
WHERE (((tblAgencyAssistance.ID)<>59) AND
((tblAgencyAssistance.AssistanceDate)<>#6/22/2005#));

The WHERE clause excludes all ID #59....I want it to exclude only the record
with ID #59 AND date 6/22/05. It inserts records for the other ID's just
fine.

What am I doing wrong here?

 

[Guest]

You might want to try a query like the following:

select * from tblAgencyAssistance
where not (ID = 59 and AssistanceDate = #6/22/05#)

I'm not certain, but I think you're getting the equivalent of an OR because
of the double NOT EQUAL clauses, so you're getting a set containing all rows
not having ID = 59 OR not having date = 6/22/05.

 

[EManning]

Thanks. That did it.

You might want to try a query like the following:

select * from tblAgencyAssistance
where not (ID = 59 and AssistanceDate = #6/22/05#)

I'm not certain, but I think you're getting the equivalent of an OR because
of the double NOT EQUAL clauses, so you're getting a set containing all rows
not having ID = 59 OR not having date = 6/22/05.