Hidding duplicates

[imx]

Hi I have 2 tables, 1 for temp (records imported from excel) and 1 for permenant records appended from the temp table. I'd like to:

1. compare IDs from both tables and if I find a match then i compare 3 fields in these tables. If tmp tables field are different, then append record from tmp to perm table.

2 set the new record's Boolean value to true in perm table and set the old record that was matched against the new rec to False.

Hope I have made this clear

 

[llkhoutx]

Duplicate rows are the result of improperly filtered queries.

 

[imx]

True, but in this case I dont have control over the source of the data (Excel docs). We're basically importing the same xls every day so its up to the database to exclude genuine duplicates (all fields are the same) and import records that have changed (ID is the same but 1 or more of 6 fields has been edited). Since management are interested in historical data we must keep records that have changed slightly. And also use boolean value to indicate the latest version of the same record

 

[Sako]

Hi,

I've done something similar to this before. Can you post a sample of your database (fill in with random values if the data is confidential).

 

[imx]

Hi

1. The records in tblCurrent represent the data that was last imported on a previous occasion.

2. The records in tblTemp are records to be imported at the end of the day.

3. records 1001 (richard) and 2001 (peter) should NOT be imported because the changes occurred in fields that are not important to management

4. records 3001 and 4001 should be appended to tblCurrent because a change has been made to important fields (strDetails1 and/or strDetails2 and/or strDetails3). Also their fDetails_changed field will set to True.

5. original records 3001 and 4001 within the tblCurrent should have their fCurrent value set to false.

6. record 5001 (harry) does not exist in tblCurrent and should be append from tblTemp to tblCurrent

7. tblCurrent_after_successful_import represents how records in tblCurrent should look after a successful import


View attachment DBImport.mdb

 

[llkhoutx]

You can articulate it, give it a try.

 

[Sako]

Can you make it more clear?

As for Peter, (ID 2001), there was a change in strDetails4. Which StrDetails are important? which ones are not?

 

[Sako]

Hi,

Step 1. Modify your indexes as I did in my DB. This way, you will be sure that no duplicate records with str1, str2, str3 will be the same.

Just follow the steps in my DB. There is most likely a shorter way to do it, but this is my solution. You can play around to make it better but it seems to me that it creates the same final table as yours.

 

[imx]

Cheers, your a star... thats a nice solution.
I just realised that when the next import occurs, i need to set records 3001 and 4001's (in the temp table about) fDetails_changed to FALSE if their details (strDetails1 and/or strDetails2 and/or strDetails3) are identical to to previous (by previous i mean the latest version of 3001,4001 where fDetails_changed=TRUE AND fCurrent =TRUE) records.

Any help would be grately appreciated