Duplicate Records in DataSheet View

[caykamanj1966]

I hope someone can help me with this.

I have embedded datasheet on a Form.

I have to access an external database everyday to export the records as an excel file and paste them into my datasheet. These 2 databases can't talk to each other, because that has not been approved and probably never will.

I have to review these records to make sure they are not going to impact our program in anyway. If I do find records that will impact, my team and I have to assess how much of an impact it will be. They usually have a planned start and stop date.

Those records in the external database follow a workflow and they go through several stages/statuses, so they start out as Draft, then Notify, then Submit and finally Approved. Well of course these status changes don't happen all in the same day. With being said, I usually download the first round of records when they are in the Notify state, say on a Monday. Then when I come in on Tuesday I have download files again, well some of those files I downloaded on Monday, I will have to download again, because now they have changeds their status to Notify or Submit. So I don't want to add these records to my Access database, unless the planned start and stop dates are different.

What I want to be able to do is have the database actually show me the duplicate records maybe in a datasheet format and then I want the database to look and see if the duplicate records have differnt planned start and stop dates and if they don't, it deletes the older record automatically. But if that can't be done, I will just look at the dates and if I see it being the same, I can delete what I need to delete manually. But it would be nice if all of this can be done automatically.

I do not know how to code something like this or if this can even be done.

Can someone please help me with this? If I need to give more information, please let me know.

Thanks in advance!

 

[Andrzejek]

Do those records that you download have any unique field, like an ID or any other Primary Key field?
If so, you can Insert records with the IDs that you do not have, and Update the records that you do have.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[caykamanj1966]

No they don't.

It is exported into an excel format and then I copy those records into my Access Database and the fields match what I have in my Access Database.

 

[Andrzejek]

So, how do you know which rows (of data from Excel) have to match which records in your table?

I will just look at the dates and if I see it being the same, I can delete what I need to delete manually

How do you determine: this Excel XYZ data matches (or does not match) my XYZ record?
How do you define 'Duplicate Records'?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[caykamanj1966]

So here are the fields in my database:

impacts
ma_id
maint_category
create_date
submit_date
assignment_group
status
title
planned_start_date
planned_end_date
requestor

I have changed one of the attributes for the "ma_id" field for indexed to have yes (no duplicates).

The ma_id field has numbers like this: CHG1112345

So if I copy the records from the external database into my database, and if there is a record with that number it will alert me of course and put the duplicate record in a separate database.

Once it finds all the duplicate records, I accept the rest of the records and do what I need to do.

Did that explanation help?

 

[Andrzejek]

If [blue]ma_id[/blue] field is your unique value (PK), then my answer from 20 Mar 24 12:12 still stands:

you can Insert records with the [blue]ma_id[/blue] that you do not have, and Update the records that you do have.

"So if I copy the records from the external database into my database, and if there is a record with that [ma_id] number [you should Update that record in your table with the data from the external database] it will alert me of course and put the duplicate record in a separate database."

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson