Trouble with an Append query WHEREE clause 1

[southbean]

Hello All,

I’m having trouble with an Append query. Specifically in the WHERE clause.
Every week I need to append records to Table01 from Table02. Table02 is a linked table from another DB that contains records of employee donations. Table01 tracks the donations in my Access DB.
To eliminate duplications (the source table can have duplicate records) I have the update query below which makes sure the new record being appended has a donation date greater than the previous maximum date of the employee’s record.

INSERT INTO Table01 ( EmpID, DonateAmount, DonateDate )
SELECT qry_ MatchingRecs.EmpID, qry_UpDate_MatchingRecs. Amount, qry_MatchingRecs.CheckDate
FROM qry_MatchingRecs
WHERE qry_MatchingRecs.CheckDate >DMax("DonateDate"," Table01 ",[EmpID]);

This works fine. However, It will not append new records - since the WHERE clause is searching for a date.

I’ve tried using things like: WHERE IsNull(EmpID) and CheckDate >DMax("DonateDate ) etc. But nothing works.

Any/All suggestions would be GREATLY appreciated!

- lm

 

[boblarson]

Well, I think you probably need a subquery for this. But just so you know, your DMax criteria is wrong. You use the field and an equals sign and then concatenate in the current field:

DMax("DonateDate"," Table01 ","[EmpID]=" & [EmpID]);

And if EmpID is text it would be:

DMax("DonateDate"," Table01 ","[EmpID]=" & Chr(34) & [EmpID] & Chr(34));

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[Golom]

Since it won't append new records, your assertion that it "... works fine ..." is a bit puzzling ... however ...

I'm a bit uncertain about the use of DMAX in this context. Specifically, it needs to run for every record retrieved in the SELECT and, since DMAX doesn't contain any fields from the main query, I suspect that it will run only once because the SQL optimizer "helps you out" by optimizing static parts of the query.

You do (as boblarson says) need a subquery ... and more specifically a coordinated subquery for this.

INSERT INTO Table01 ( EmpID, DonateAmount, DonateDate )

SELECT EmpID, Amount, CheckDate

FROM qry_MatchingRecs As M 

WHERE CheckDate > (Select MAX(DonateDate) From Table01 As T
                   Where T.EmpID = M.EmpID)

There are alternate constructions involving joins that don't need a sub-query but the sub-query route is (for me at least) easier to visualize.

I do note that you have [blue]qry_UpDate_MatchingRecs.[/blue] as a query reference but that is not mentioned in the FROM clause. I assume that you meant [blue]qry_MatchingRecs.[/blue]

 

[southbean]

boblarson & Golom,

Thank you for your replies!

I've tried both and have not had success.

There are two records that meet the criteria:
Record #1: A new record.
Record #2: A record that has a greater DonateDate than the Max date of the matching (EmpID) record in the table.

However, using the subquery method, only Record #2 is selected to be appended.

Any idea why this would be happening?

Thank you again for your replies!!!

- lm

 

[PHV]

WHERE CheckDate > [!]Nz([/!](Select MAX(DonateDate) From Table01 As T
Where T.EmpID = M.EmpID)[!],0)[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Golom]

Well ... yes. It's happening because you didn't include that detail in your specification. The SQL I provided checks only for the CheckDate > MaxDate condition. It does not check for EmpID values that do not exist in Table01. To do that you would need

INSERT INTO Table01 ( EmpID, DonateAmount, DonateDate )

SELECT EmpID, Amount, CheckDate

FROM qry_MatchingRecs As M 

WHERE CheckDate > (Select MAX(DonateDate) From Table01 As T
                   Where T.EmpID = M.EmpID)

      OR NOT EXISTS (Select EmpID From Table01 As T
                     Where T.EmpID = M.EmpID)

ORDER BY EmpID, CheckDate

ORDER BY is usually irrelevant in an append query because it (usually) doesn't matter in what order records are inserted. In this case however, you do need it because you are testing based on a MAX date in Table01 and, if you happen to encounter a record in the query with a large MAX date then later records with earlier dates won't be inserted because they will fail the MAX date test.

 

[southbean]

Golom,

Sir, I am in your debt. That worked perfectly!

Thank you very much!!!

- lm