none
Append query to write only recs that don't exist yet in the table

    Question

  • I've searched on this topic but haven't found another post that addresses this specific issue.

    INSERT INTO tblCellSearchesMonthly ( LockID, InmateID, StaffName, SearchDateTime )
    SELECT tblCellSearches.LockID, tblCellSearches.InmateID, tblCellSearches.StaffName, tblCellSearches.SearchDateTime
    FROM tblCellSearches;

    I want an append query to write records from one table to another as above, but only if a LockID hasn't been entered in the same Month/Year already. IOW, if a rec exists in the second table with the same LockID/Date combination, don't append it. Is there a JOIN and a WHERE clause that would work here? Or is there another/better way?

    R

     

    Friday, February 15, 2013 6:39 PM

Answers

  • Try this --

    INSERT INTO tblCellSearchesMonthly ( LockID, InmateID, StaffName, SearchDateTime )
    SELECT tblCellSearches.LockID, tblCellSearches.InmateID, tblCellSearches.StaffName, tblCellSearches.SearchDateTime
    FROM tblCellSearches LEFT JOIN tblCellSearchesMonthly ON (tblCellSearches.SearchDateTime = tblCellSearchesMonthly.SearchDateTime) AND (tblCellSearches.LockID = tblCellSearchesMonthly.LockID)
    WHERE (((tblCellSearchesMonthly.LockID) Is Null) AND ((tblCellSearchesMonthly.SearchDateTime) Is Null));

    Friday, February 15, 2013 10:00 PM
  • You shouldn't need a tblCellSearchesMonthly table at all.   It should be possible to return the results with a query:

    SELECT LockID, InmateID, StaffName, SearchDateTime
    FROM tblCellSearches AS CS1
    WHERE SearchDateTime =
        SELECT MIN(SearchDateTime)
        FROM tblCellSearches AS CS2
        WHERE CS2.LockID = CS1.LockID
        AND YEAR(CS2.SearchDateTime) = YEAR(CS1.SearchDateTime)
        AND MONTH(CS2.SearchDateTime) = MONTH(CS1.SearchDateTime));

    Ken Sheridan, Stafford, England

    Friday, February 15, 2013 11:30 PM

All replies

  • What sort of date is used for SearchDateTime?  Is the first of each month?  Every Monday?


    Friday, February 15, 2013 6:59 PM
  • By "LockID/Date" do you mean the SearchDateTime column?

    If I understand, you don't want to add a record in the monthly table if one already exists in the monthly table for that LockID at ANY TIME in the same month and year which is in the not-monthly table, right?

    Is SearchDateTime a true date/time field containing a full date, or does it only contain a month/year date?

    It would be a bit more difficult to exclude a given LockID/Date when the date you are reading in the not-monthly table contains day also.  That would mean you need to search all records in the monthly table which are dated any time during the month/year, right?

    This could be mitigated if you have the flexibility of adding columns to the tables.  You could add a year/month column to both tables, but in the not-monthly table, it would be a calculated column based on the SearchDateTime value.

    If the SearchDateTime is only the month/year, this would be quite easy.  Please clarify.


    Ron Mittelman

    Friday, February 15, 2013 7:06 PM
  • Try this --

    INSERT INTO tblCellSearchesMonthly ( LockID, InmateID, StaffName, SearchDateTime )
    SELECT tblCellSearches.LockID, tblCellSearches.InmateID, tblCellSearches.StaffName, tblCellSearches.SearchDateTime
    FROM tblCellSearches LEFT JOIN tblCellSearchesMonthly ON (tblCellSearches.SearchDateTime = tblCellSearchesMonthly.SearchDateTime) AND (tblCellSearches.LockID = tblCellSearchesMonthly.LockID)
    WHERE (((tblCellSearchesMonthly.LockID) Is Null) AND ((tblCellSearchesMonthly.SearchDateTime) Is Null));

    Friday, February 15, 2013 10:00 PM
  • Karl,

    We still haven't established whether the SearchDateTime field is a "real" date-time field. We can't necessarily infer that from the name of the field.

    If it's a "real" date-time field, this won't satisfy his requirements.  He said to not insert the record if one exists for that LockID for the same month and year.  That I why I asked the questions I did before suggesting the query.  Either the tables will need to have year/month fields added and used for the comparison, or the query will need to be quite a bit more complex, using Year() and Month() functions in the linking.


    Ron Mittelman

    Friday, February 15, 2013 10:52 PM
  • You shouldn't need a tblCellSearchesMonthly table at all.   It should be possible to return the results with a query:

    SELECT LockID, InmateID, StaffName, SearchDateTime
    FROM tblCellSearches AS CS1
    WHERE SearchDateTime =
        SELECT MIN(SearchDateTime)
        FROM tblCellSearches AS CS2
        WHERE CS2.LockID = CS1.LockID
        AND YEAR(CS2.SearchDateTime) = YEAR(CS1.SearchDateTime)
        AND MONTH(CS2.SearchDateTime) = MONTH(CS1.SearchDateTime));

    Ken Sheridan, Stafford, England

    Friday, February 15, 2013 11:30 PM
  • The SearchDateTime field is full date. I had planned on using Month and Year functions to extract the needed month and year parameters. The query Karl wrote above seems to work, however the responses here, particularly Ken's, have caused me to question my entire design. I probably don't need two tables. I will re-visit this issue later if necessary. Many thanks to all.

    R

    Saturday, February 16, 2013 6:32 PM