dmax update query problem

[NoFunAtWork]

I am having a problem with a credit reporting information collecting
database. The dmax function is giving unexpected results.

I am using dmax to eliminate multiple payments from a single payee in one
month - retaining just the payment info for the most recent month.



The way the db is set up is to draw payment info from a Quickbooks report
and then converts some of the fields [txt to date, etc] and pastes the "new"
payment information in with the "old" name and address info stored in a
table called "tblBasis"



This takes place in two steps the first draws and converts the data from
linked excel file [the QB report called "tblQBmonthlyExport"] and appends to
a table called "tblInterimMonthly" - - this works fine.



However when I want to update the old data [tblBasis] and eliminate
duplicate payments during one month [keeping the one for the most recent
month due] is where I am having problems.



A query like the following returns the correct number of records 36 [there
are 39 payments this particular month for 36 accounts]



SELECT tblInterimMonthly.DateOccurance, tblInterimMonthly.BalanceDue,
tblInterimMonthly.DateLastPayment, tblInterimMonthly.CustAcctNum

FROM tblInterimMonthly

WHERE
(((tblInterimMonthly.DateOccurance)=DMax("DateOccurance","tblInterimMonthly","[CustAcctNum]
= " & [tblInterimMonthly].[CustAcctNum])));



However when I convert into an update qry it returns 37 records [and I can
see one duplicate]



UPDATE tblInterimMonthly INNER JOIN TblBasis ON
tblInterimMonthly.CustAcctNum = TblBasis.CustAcctNum SET
TblBasis.DateOccurance = tblInterimMonthly.DateOccurance,
TblBasis.BalanceDue = tblInterimMonthly.balacedue, TblBasis.DateLastPayment
= tblInterimMonthly.datelastpayment

WHERE
(((TblBasis.DateOccurance)=DMax("DateOccurance","tblInterimMonthly","[CustAcctNum]
= " & [tblInterimMonthly].[CustAcctNum])));



I tried it in a different way [two steps using the first qry called
'qry2DMAXw_36Records" which produces the correct records] and it returns 35
records



UPDATE qry2DMAXw_36Records INNER JOIN TblBasis ON
qry2DMAXw_36Records.CustAcctNum=TblBasis.CustAcctNum SET
TblBasis.DateOccurance = qry2DMAXw_36Records.dateoccurance,
TblBasis.BalanceDue = qry2DMAXw_36Records.balancedue,
TblBasis.DateLastPayment = qry2DMAXw_36Records.datelastpayment;



Yes the qry names are strange but I have done and redone this several ways
over the last few days and am stuck. Some of the syntax is not the most
readable [ I tend to use the access qry authoring view ]



Any help would be appreciated



I have posted a copy of the db here



http://www.habilee.org/CRinfo.htm there is a link at the bottom of the page
to a zip file



it is about 200k so you can see the data, perhaps there is a data
peculiarity that is causing this that I am not seeing. Unzip it into folder
called c:\AAHACreditReporting [for some of items not necessarily related to
this] for it all to work.



qry2DMAXw_36Records

qryUpdate_nostep_returns37

qryUpdate_step_returns35



FYI this db is to allow the 37 Habitat for Humanity affiliates in Alabama to
work together and report payment history of our home owners. This db will
be at each location and a complierdb at our Association office -so some
items are automated for simplicity but others to allow for different
bookkeeping systems.



Other input appreciated as well.