CODEWITHSUNDEEP
sqlms-access
Oscar
Oscar

Reputation: 33

using criteria in an update query involving a join

I'm using MS Access

The SQL below updates the CurrNumTees field in the Parent tblContact records with the number of tblTorTee records that have an end date (which is not the ultimate effect I am aiming for, but I provide it as a starting point.

UPDATE tblContact 
  INNER JOIN tblTorTee ON tblContact.ContactId = tblTorTee.TorId 
SET tblContact!CurNumTees = DCount("[tblTorTee.EndDate]",
  "tbltortee","Torid = " & [ContactId]);

I need to update the CurrNumTees field with the number of records in tblTorTee that do not have an EndDate, in other words, that field is blank. I’ve tried using WHERE and HAVING and IS NULL in various combinations and locations, but without success. Could you help point me in the right direction?

Upvotes: 3

Views: 138

Answers (2)

June7
June7

Reputation: 21370

Saving calculated data (data dependent on other data) is usually a bad design, especially aggregate data. Should just calculate when needed.

Did you try the IS NULL criteria within the DCount()?

UPDATE tblContact Set CurNumTees = DCount("*", "tblTorTee", "EndDate Is Null AND TorId = " & [ContactId]);

Upvotes: 1

Jonathan Willcock
Jonathan Willcock

Reputation: 5245

The MS Access COUNT function does not count nulls, so I think you have to do this in two stages.

Firstly create a query like this:

SELECT TorId, IIF(ISNULL(EndDate),1,0) AS isN
FROM tblTorTee
WHERE EndDate IS NULL;

And save it as QryEndDateNull

Now you can run an Update Query like this:

UPDATE tblContact
SET tblContact.CurNumTees = DSUM("IsN","QryEndDateNull","TorId = " & [ContactID]);

Upvotes: 1

Related Questions