Please HELP! Can I perform update query from excell file to table?

[eforce]

MY problem is that I have a table in which my primary keys (month, year & their units UIC) are "no-duplicates allowed" of course. My clients would send me their report data on a month to month basis but in the case that they wanted to update a previous months data, how would I import this data into my table.

I realize I'm going to get the "can't append data to fields.......ect...no duplicates allowed message" which is not something I want to change but I was wondering if an update query could be done by using an excell file (the file the company sends me after exportng it from their table).

I receive all of their data via email in excell format.

Please HELP!

 

[Fornatian]

Firstly, import the excel file back into Access or create a linked table, then we can solve the problem in the one arena.

Side issue...The advantage of a linked excel table is if the format of the data never changes you can overwrite the source file and the access file will automatically pick up the new data when the db is opened.

Back to the answer...Pull both tables into a QBE grid and draw lines between the primary key fields of both tables - this will ensure the unique records are matched. Test this first before creating an update query by pulling field into the query from both tables to ensure they are the same.

Now transform the query into an update query and enter the fields to be updated as the fields from the excel table.

If necessary add criteria to ensure only those records you need to update are updated.

 

[eforce]

Thanks Ian

Thanks!

 

[eforce]

How would I accomplish this

Correct me if I'm wrong because I keep having problems:

I have "tblMaster" has 3 primary keys (Month, Year, & UIC) which are set as "Yes, no duplicates".

Each subordinate company is identified by a "UIC" and they send me via email (excel format) updated reports each month. The report is simply a table that is exported from their database so, the format will never change.

Lets say that Company A wants to correct their report info for Oct 2002. How can I update my table with this information.

Problem 1: My table is setup for no duplicate info and my primary keys are "Month" "Year" & "UIC" (which is the company number).

In the upper correspondence Ian told me about linking the tables and everytime the excel file is updated, my table we automatically be updated.

Problem 2: I can't get this link table thing to work right.


Please HELP!

Eforce

"Delta's last resort"

 

[Fornatian]

If you wish send me a copy of your db(delete sensitive info please, but enough to work with) and a copy of a spreadsheet you want to link through to:

formatian@aol.com

be patient my mind and email work at the same speed.