Edit or Add Data in a Table Each Month

[samjh]

Hi,

I need some help and advise please.

I have a commitment table in my database, and normally people will input commitments through a form.

However each month I will have a flat file upload from excel directly into the Commitment Table, these will be up to 1500 lines. (they are downloads from other systems, that we want to have in our database and i can't link directly to these systems).

I created a macro to do this, and attached it to a button and tested it and it works fine.

My issue is this, once I've uploaded it once, next month what I would like to happen is when I upload it again that it changes the original commitment should any of the details be different or it inserts a new commitment if it didn't already exsist. (After I have uploaded the original data, I can download it to get the commitment ID to attach to the excel files if nessesary.)

I'm not sure what the best way to do this is.

thanks.

 

[plog]

I would bring the monthly data into a temporary table and create 2 queries with it and the Main data table. The first query would find all matches between the 2 tables and update the Main table. The second query would be a LEFT JOIN from the temporary table into the Main table to find all records in the temporary table that are not in the Main table and this query would append those records to the Main table.

 

[samjh]

Hi,

thanks for your reply, that sounds like something I woudl want, however i have no idea how to go about actualy doing that, and when we come to doing this for real, I will have a front end and back databse, and I woudl ahve to attach all the processes to buttons or something as the user will just panels and buttons.

I'm very quick on the uptake, so would just need some pointers on how I go about doing this.

many thanks for your help.

 

[plog]

That's 2 generic questions in a row. Take the advice in my first post and start working through it. When you need specific help, post back specific questions and I will be glad to help.

 

[Solo712]

(After I have uploaded the original data, I can download it to get the commitment ID to attach to the excel files if nessesary.)

Out of curiosity: how do you go about getting the ID to "attach" to the Excel file ? Do you manually export the table with an extra column ?

Best,
Jiri

 

[gemma-the-husky]

note that the key to being able to do this, is that your spreadsheet MUST have a unique ID column that can be used to find the row in the access database.

 

[samjh]

Hi,

Yes, I would download the data with the key from Access and do a look a lookup from the excel file, to add the key. Then we would then just keep uploading the excel file with the key where it already existed and append the details of the commitment.

 

[Solo712]

Hi,

Yes, I would download the data with the key from Access and do a look a lookup from the excel file, to add the key. Then we would then just keep uploading the excel file with the key where it already existed and append the details of the commitment.

I guess what I meant to ask was, do you supply the key with some kind of VBA code ? I use this kind of setup (i.e. updating Access tables from large spreadsheets and maintaining Access keys in Excel) regularly. The code reads the spreadsheet and if there is a key present in the key column it updates each existing record. If there isn't, it adds the row to Access table and places the key in the spreadsheet column. This kind of setup allows easy edits in Excel on bulky data.

If you are able to update the spreadsheet via code you should have no trouble implementing this kind of process.

Best,
Jiri