How to Maintain Historical Data on Daily Basis (One Below Other)

[RAJESHKUMAR R]

Dear Experts,



I would need your suggestion on my subjected Query!

I have an Access Table Named as “Demand” which is having multiple column!

Also, I have an Excel worksheet Named as “Demand Input”. Every Day Based on given Input (Manual input from ERP), Excel do all Formation and keep it ready in “Demand Input” worksheets for
transfer to MS Access

The Help which I required here is, With the help of programming, How to Pull those “Demand Input” Worksheet Data to “Demand” Access Table (Note: Column Heading is Exactly Same) to the last blank cell on Daily basis!

Thanks & Regards,
Rajeshkumar R

[ranman256]

import records from a file


1. save the file (excel, or text) to a generic name everytime: File2import.xls (or File2import.txt)
2. attach (link) the file as an external table, like tFile2Import
3. make a query that appends data from tFile2Import table to the target table.
this query would use * to import all fields with the same name, but if the 2 tables dont have the same names, you must add the source fields and target fields to the query.
4. put this query in a macro, run the macro.


Once this is done, the import steps are now:
1. save the new file and overwrite the old one
2. run the macro
done.

[ArviLaanemets]

Even when normally in your Excel file are only new entries, to be on safe side it is wise to make sure the append query to add only new data.

Possible ways:
1. In excel table, you create an unique ID column (reads ID from ERP system, or calculates an ID based on date and on some row identifier(s)). In Access file, you have a field for this ID too. The append query will have the syntax like

INSERT INTO (FieldList)
SELECT ... FROM LinkedExcelTable WHERE ExcelID Not In (SELECT ExcelID FROM AccessTable)

2. You have several fields in Excel table, which together give an unique set of values. In Append query, you use Left Join on LinkedExcelTable and AccessTable, and insert rows where fields in linked AccessTable are Null.

[RAJESHKUMAR R]

Dear Experts,

Thanks for your valuable suggestions!

Thanks & Regards,
Rajeshkumar R