Need help with append query, please
- From: "EManning" <emanning_spambegone@xxxxxxxx>
- Date: Mon, 27 Jun 2005 15:28:33 -0500
Using A2K. I've got a subform that displays a list of agencies by date for
each client. For example, currently there are 10 agencies. Client #59 has
30 agency records: one set for 6/22/2005, one set for 7/1/2005, and one set
for 7/15/2005. If the user adds an agency for #59 on the 6/22/2005 set, an
append query is created in the AfterUpdate of the agency name to append this
new agency to all other clients. Here's the 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#));
This WHERE clause ignores all records for #59. However, there should be a
new record appended for 7/1/2005 and a new record for 7/15/2005. If I don't
use the clause I get duplicate 6/22/2005 records for #59.
Can anyone tell me what I'm doing wrong? Below is a sample of
tblAgencyAssistance if that helps:
ID AgencyAssistance AssistanceDate AssistanceAmount
59 Dental Assistance 6/22/2005 $6.00
59 Dental Assistance 7/1/2005 $10.00
59 Dental Assistance 7/15/2005 $0.00
60 Dental Assistance 6/1/2005 $14.00
60 Dental Assistance 7/1/2005 $15.00
61 Dental Assistance 7/1/2005 $0.00
Thanks for any help or advice.
.
- Prev by Date: RE: Get top customers and rest group as one Name?
- Next by Date: Re: extract part of a field
- Previous by thread: Sum Values in COlumn?
- Next by thread: Re: extract part of a field
- Index(es):
Relevant Pages
|
