Compare 2-tables, update #1 w/new records from #2 & field 5 on all. 4

[Sherman6789]

Hello,

We have an Access table that holds basic information used by most of the people in the office. I'll call this the "#1 - Primary" table.

Information for updating this table is sent to us about every two weeks in the form of an Excel spreadsheet. This is a new Excel file sent each time with a different name. An Access copy of this table is created. I'll call this table the "#2 - Temporary" table.

Last June, I received instructions on how to automatically have a "temporary" Access table created from the Excel Spreadsheet. Field #10 from the temp table is then deleted. The two tables are merged and any new records found on the temporary table are copied into the primary table. All duplicate records are ignored/rejected. Finally, the temporary Access table is deleted. All of this is done at a push of a button.

This has worked well for the past few months until I recently received new instructions. One of the fields on the temporary table needs to be pushed into the primary table each time regardless of whether the record is new or old. This one field is updated constantly and we need the lastest results for this one field to be reflected at all times.

I think that after the new records are merged into the primary table that another command should be added which will match the key field of both table and replace this field in the primary table with that of the temporary table. At that time the temporary table will automatically be deleted.


**************************************************
Here are the basic commands attached to the update button:

Private Sub cmdGetExcelFile_Click()

'Transfer spreadsheet on a: to a new temporary table

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTempUpdateExcel", "a:\ExcelImport.xls", True

CurrentDb.Execute "ALTER TABLE tblTempUpdateExcel DROP COLUMN F10;"

'Append old Table with data from new table

CurrentDb.Execute "INSERT INTO tblNIDDKGrantApps SELECT tblTempUpdateExcel.* FROM tblTempUpdateExcel;"


' Delete "NewTableName" table after you are satisfied that it is ok.

DoCmd.DeleteObject acTable, "tblTempUpdateExcel"

DoCmd.Close

End Sub
**************************************************

NOTE: Even though the initial Excel spreadsheet has a different name each time, it is copied onto a floppy disk and given a preset name: "ExcelImport.xls". This name and floppy drive are imbedded into the sql statements.


Any suggestions?

 

[PHV]

Something like this ?
CurrentDb.Execute "UPDATE tblNIDDKGrantApps AS A INNER JOIN tblTempUpdateExcel AS E ON A.ID=E.F1" _
& " SET A.FieldPushed=E.F5;"
The above assumes that:
- ID is the primary key of #1 and the corresponding field in #2 is F1
- #1.FieldPushed is the field forcibly updated by the value of #2.F5

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Sherman6789]

Thanks PHV,

I have just gotten my Access books out to study inner joins. Please explain the "A." and the "E." above?

I'm not sure if I made it clear that as soon as a copy of the Excel spreadsheet is made into a "temp" table, the Excel file is not used by the program.

Summary
Primary Table:
Key field = Grants
pushed field = SupervisorName (this is subject to change)

Temporary Table:
contains all major fields from the Excel file which are in the correct order for the Primary file.

 

[PHV]

(i]Please explain the "A." and the "E." above[/i]
They are aliases for tblNIDDKGrantApps. and tblTempUpdateExcel. respectively.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

Here's a great link for 'Understanding SQL Joins' that you will find helpful!

Leslie

 

[willir]

Great link Leslie

 

[lespaul]

thanks richard!