0

I have this access SQL append query where I have a Primary key set to now allow duplicates in the destination table. I'm confused if I am accomplishing the right thing here with the WHERE condition.

I am trying to only screen the newest records from the source table 'tbl_IMEI_MASTER' and only append(add) the records that do not have a match on the key in the destination table (same identifier as the source table). I think it's working but I did get a message that 72 rows (which is the total that were new and unique for the addition to the source table based on most recent date) were being updated in the destination table when only 14 of them should have been updated/added. Only 14 should have been identified as not having the same unique key.

INSERT INTO leads_historical (Customer, LeadNumber, ImportDate)
SELECT DISTINCT tbl_IMEI_MASTER.Customer, tbl_IMEI_MASTER.LeadNumber, tbl_IMEI_MASTER.ImportDate
FROM tbl_IMEI_MASTER
WHERE tbl_IMEI_MASTER.ImportDate = (SELECT MAX(tbl_IMEI_MASTER.ImportDate) FROM tbl_IMEI_MASTER);
Dre Day
  • 338
  • 2
  • 8
  • Hans - it shows date and time as 00:00:00 for all. I import all the data in one shot. Right now, I'm connecting all of that imported data of source table and trying to say hey, take what's out of this source data (considering what was added today to source) and add any new data to the historic table that doesn't currently exist in historic table from source table. – Dre Day May 20 '22 at 00:11
  • OK. Make a new query using just the `SELECT` piece of your existing query. When you open that query, does it show you 72 rows instead of the 14 you expect? If so, what patterns can you identify to explain which actually get added and which don't? – HansUp May 20 '22 at 00:58
  • Hey - yes actually your post inspired me to do that. Realized this could be a good way for checking chunks to see what's happening! Anyway - I think I see what is happening and I'm not sure if it's a bad thing or not. What's happening is that it's taking matching records on Customer, and if it matches - it pulls those in and updates the date from existing records, while also adding and recording anything new. So if a record matched and had an older date - it pulls that it and updates the date. I think I'd rather it just pull in what's new only though. Not sure how though. – Dre Day May 20 '22 at 01:11
  • So the append edits existing matches on the historic list by updating the date and retaining the rest of the data, and also adds new data... – Dre Day May 20 '22 at 01:13

1 Answers1

1

I got it -

Using just select to break it down further I drilled down to the desired result.

SELECT DISTINCT tbl_IMEI_MASTER.Customer, tbl_IMEI_MASTER.LeadNumber, tbl_IMEI_MASTER.ImportDate
FROM tbl_IMEI_MASTER
WHERE tbl_IMEI_MASTER.ImportDate=(SELECT MAX(tbl_IMEI_MASTER.ImportDate) FROM tbl_IMEI_MASTER) AND NOT EXISTS (SELECT leads_historical.Customer FROM leads_historical WHERE leads_historical.Customer = tbl_IMEI_MASTER.Customer);
Dre Day
  • 338
  • 2
  • 8