CODEWITHSUNDEEP
ms-access
Jason Bayldon
Jason Bayldon

Reputation: 1296

Appending into another table and avoiding duplicates

I am attempting to query a table and append the results into another table. The query that I am attempting to create, requires two fields in a table:

[TblCaseListing]
CaseNumber (text field describing an internal case number)
NextRenewalDate (date/time field describing the next due date for renewing the case)

Essentially this table is a listing of all the possible cases, and the dates that the case number can last be renewed by. The process that I am creating looks like:

  1. Case is Queried and Appended 90 days prior to the NextRenewalDate (dest. [tblCaseNumberQueue])
  2. Case is handled by users
  3. The TblCaseListing.NextRenewalDate is updated to the next year if item is completed

Now my query looks like:

Field: CaseNumber
Criteria: Not In (SELECT DISTINCT CaseNumber FROM tblCaseNumberQueue) 'problem is here

Field: NextRenewalDate
Criteria: <=DateAdd("d",90,Date()) 'detect and append from 90 days

However, this is not correct. Since the NextRenewalDate will change year after year by Users working a form and updating the data. I want to append within 90 days as long as the renewal date and case number combined dont already exist in the destination table together. This query to me says "Append if today's date is within 90 days and the case number is not in the destination table". It should say "Append if the case number combined with next renewal date are not in the destination table, and the NextRenewalDate is within 90 days. What do I need to modify in order to append based on the two fields? Thanks!

Upvotes: 2

Views: 8195

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123849

It seems to me that you can handle this in one of two ways. The set of cases in which you are interested is easily defined

SELECT CaseNumber, NextRenewalDate
FROM TblCaseListing
WHERE NextRenewalDate <= DateAdd("d", 90, Date())

Your first option would be to use NOT EXISTS to check the destination table for cases that have already been inserted

INSERT INTO tblCaseNumberQueue (CaseNumber, NextRenewalDate)
SELECT CaseNumber, NextRenewalDate
FROM TblCaseListing
WHERE NextRenewalDate <= DateAdd("d", 90, Date())
    AND NOT EXISTS
        (
            SELECT * FROM tblCaseNumberQueue
            WHERE tblCaseNumberQueue.CaseNumber = TblCaseListing.CaseNumber
                AND tblCaseNumberQueue.NextRenewalDate = TblCaseListing.NextRenewalDate
        )

However, some people frown upon using NOT EXISTS because it is not very efficient.

The other option would be to create a unique index in [tblCaseNumberQueue]

CREATE UNIQUE INDEX UX_CaseDate ON tblCaseNumberQueue (CaseNumber, NextRenewalDate)

and then just do the INSERT without the NOT EXISTS check and let the unique index disallow the rows that are already there.

INSERT INTO tblCaseNumberQueue (CaseNumber, NextRenewalDate)
SELECT CaseNumber, NextRenewalDate
FROM TblCaseListing
WHERE NextRenewalDate <= DateAdd("d", 90, Date())

However, some people frown on that approach, too. They claim that we should always avoid errors and not rely on such mechanisms for otherwise "legitimate" operations.

Personally, I would probably go with the first option because it is more explicit, and only go with the second option if I thought that NOT EXISTS might be significantly impacting performance. (However, I would recommend creating the UNIQUE INDEX in either case since you really do want to avoid duplicates, whichever way you choose to avoid them.)

Upvotes: 3

Related Questions