OK, here is the database that does what I think you want, it uses a table and form to identify the Folder where your data files are located. Once you have updated the location in the form you can click on the import Button called Import New File Data.
There are 2 tables, one called Customers to hold the CustomerID, the file name, the Customer and the file date.
The other table holds the sales data from the files in the folder.
The VBA finds a file in the Target Folder and re-arranges the date into a format that is acceptable to an Access date Field, it then stores that date in a single record in the Customer table.
it then looks up the CustomerID that has been allocated to that record.
The code then opens the target file for reading data (the old fashioned method) and reads in one line at a time, it ignores the first line and the last line. It saves the data to the Test table adding the CustomerId that it looked up in the last Field. This is the correct way to store data in a relational database, ie whereever possible only store the data once and then refer to it via it's Key ID field.
I have added a query that opens after the import that combines the 2 tables for data output.
Reply With Quote