Baffled: Gosh! I hope someone will understand and be able to help me!

[Needhlp]

Action:
1) Update the blank fields in a table + add newly imported records but...make sure that there are no duplicates.
I need to Append the records from a table called: XLS_Imp_11_27_07 to my main Table " Invoice Tracking for A/P 10_30".
The fields found in the XLS _ table: Release Dt, Entry Dt, Liquidation Dt may have been populated by a live report found on Internet. Since it's a live report, when Appended, to the other table, it will create numerous duplicates.
The fields found after the 7 first fields in the Invoice Tracking Table may have been updated by users

Is there a way to avoid duplicates and get the data from one table to the other without wiping out whatever is already filled-in?

 

[Needhlp]

I have set a primary key in the parent table. This is tricky though because if you have a look at both tables you will see that the XLS_Import gets updated practically on a daily basis so when you Append , you still wish to keep the data that was also updated by the users in the main table, namely Invoice tracking for...
I am not too sure how the update query works when you want to update a table with another one. I have tried something like [tablename].[tablefield] but it does not seem to work! I reached the point that I will append the records, then run a duplicate query and remove the records manually but when the main table will be out of proportions, it will become a nightmare!! Thanks. It needs to be investigated further but I still don't know how!

 

[Needhlp]

I have tried zillions of things. Now the last: I have put a primary key on the child table instead of the parent. I need the Append Query to capture all the fields that were updated in Excel (child table). If I put the primary key in the parent table, it will wipe out all the duplicates, which would be fine. But, a bunch of fields will be updated by a report on the Internet every time it will be rolled and we will bring them in Access to update the parent table. Meanwhile users will update other fields in the main table.

Instead of creating an Append Query, can we create a Simple Query including the child table (with the primary key) and the fields from the parent table that need to be associated with the ones in the child table. How can we make sure that I will get the correct results?

 

[Needhlp]

Here is what happens: - If I do the update query and I leave a primary key, it wipes out some of my data. If I run an Append Query without putting a primary key, I get duplicate rows but both contains data to be added to one another: Ex.: The first column in rec 1 and 2 will be the same but, the next 6 columns may be updated by a live Internet report in the first of 2 duplicates while some of the 36 other columns may have been updated in the second of 2 duplicates.


Which expression or criteria shall I use in the Update Query in order to update the information from the duplicated fields to merge?

 

[Needhlp]

One last tentative

Hi,
I meant "Set a primary key". Sorry my vocabulary is not always appropriate!

There MUST be a way! In the main table, my unique identifier is the Entry Nbr while initially I didn't set a primary key in the source table. If I ran an update query, it looked at the duplicates and picked randomly.
Now I have inserted a field "RecordingID". Same thing happens.

If you look at the Main Table that I attached when I first opened this thread (I cannot compress enough anymore, file is too big) :Invoice Tracking 1129 and, and the source table called XLS 1127, you run an Append query, than run a Find duplicate Query on the Entry Nbr, you will be able to see exactly what I mean. I have duplicates but in the 1st record (7 first columns) I have (sometimes) new data added by the Internet live report and in the 2nd record, I have new data in the next columns (23 columns)

I believe that a macro would probably do the job but I am new at this and I cannot expect any help.

Thanks for your tremendous patience!