Best way to import and append daily?? 1

[wvmbark]

Each day I receive a text file that I want to import and append to tblHistory. tbl History has additional fields that will be updated using data from the import. I.e, my date comes in as yyyymmdd as text. I have a field in tblHistory to capture the date after it's been updated.

I need to know the best way to do set up this process - Do I have an import table that gets updated then appended and then deleted? Can I perform the updates during the append? Any help would be greatly appreciated.

Thanks!!

Mike

 

[BNPMike]

I would import the data into a temporary table, which you delete each time. You can then run two queries - an update and an append - based on this table re-formatting/changing/adding etc fields in the query.

The reason is that doing things in steps makes it easier to capture and correct error situations.

 

[wvmbark]

Thanks! It makes good sense, I just wanted to make sure I wasn't missing some standard or typical way of approaching this.

 

[wvmbark]

I'm still trying to learn about joining tables, so I need a little help, please...

If some of the records (with PK [CtrlNmbr]) in the import table "tblImport" are updates to the records in the permanent table "tblHistory", how do I set up the Update query so that only records where [CtrlNmbr] match in both tables get updated?

Thanks!

 

[BNPMike]

Join the temporary table to the permanent table. Then turn the query into an update one and set all the permanent data fields to equal the matching fields in the temporary table. You can do all of this in the QBE Query Screen.