Different results between select and update

[rob@trainease.com]

I have a teaser – it’s been bugging me for long enough.

I have a letter produced from the database asking people to renew their support (Born Free Foundation). Amongst all the required data links and other conditions, the key is that Rem1Sent should be null.

After the letters have been printed, it needs to update Rem1Sent with the cut off date used in the fist selection.

The problem is – even using exactly the same tables, links, and selection criteria – it produces 1166 letters but wants to update 1328 dates

The SELECT - which returns 1166 records

SELECT Membership.Rem1Sent
FROM ((Status INNER JOIN (Member INNER JOIN Membership ON Member.MemberRecordNumber = Membership.MemberRecordNumber) ON Status.Status = Membership.Status) INNER JOIN Reminder1Letter ON Membership.Status = Reminder1Letter.Status) LEFT JOIN LiveArrangementSce ON (Membership.Status = LiveArrangementSce.MemberStatus) AND (Membership.MemberRecordNumber = LiveArrangementSce.MemberNumber)
WHERE (((Membership.Rem1Sent) Is Null) AND ((Membership.RenewalDate)<=#10/31/01#) AND ((Status.StatusGroup)='Member') AND ((LiveArrangementSce.ArrangementNumber) Is Null) AND ((Membership.LiveRecord)=True) AND ((Member.AddressStatus) Is Null) AND ((Membership.Gift)=False));

The UPDATE - which finds 1328 records

UPDATE ((Status INNER JOIN (Member INNER JOIN Membership ON Member.MemberRecordNumber = Membership.MemberRecordNumber) ON Status.Status = Membership.Status) INNER JOIN Reminder1Letter ON Membership.Status = Reminder1Letter.Status) LEFT JOIN LiveArrangementSce ON (Membership.MemberRecordNumber = LiveArrangementSce.MemberNumber) AND (Membership.Status = LiveArrangementSce.MemberStatus) SET Membership.Rem1Sent = #10/31/01#
WHERE (((Membership.Rem1Sent) Is Null) AND ((Membership.RenewalDate)<=#10/31/01#) AND ((Status.StatusGroup)='Member') AND ((LiveArrangementSce.ArrangementNumber) Is Null) AND ((Membership.LiveRecord)=True) AND ((Member.AddressStatus) Is Null) AND ((Membership.Gift)=False));

Any ideas how it can do this? Surely the selection in both is identical, so it should return the same number. There is no grouping going on in either.

Any ideas appreciated

 

[KKilfoil]

Rather than trying to play 'spot the differences', I'm going to make a suggestion:

Use your select query as the datasource of your update query.

You are guaranteed to have the same recordset, and you'll only have to maintain one query if your criteria change.

 

[rob@trainease.com]

I'm not actually using queries, the code is behind the click event and actually uses variables from the form.

I have sorted it now by using a local table to collect the correct records, and then use that to update the main table.

Thanks for the suggestion anyway