Import unique ID

[vaughan198]

Hi there I am using a simple code to append three excel worksheets (in the same workbook) to three tables in my database. There are multiple records per table/per import.
Is there any way to apply a unique ID to all the records in each import. I.e all the records I import today have an Import ID of 1 and all the records I import tomorrow have an import ID of 2. This is so that I can do statistical analysis between different report imports.
Thanks,
Will

 

[Pat Hartman]

Yes. You can add a calculated field to the append queries. You just need to figure out how to calculate an appropriate value.

If you will only be importing the files once per day, you could add a DateAdded column to the table with a default of Date(). Then you don't need to do anything. As the records are appended, Access will automatically populate the field with the default value. Just do your analysis based on groups of rows with the same date.

If you want to assign a sequential number, you can create a table. On the form where you run the code add a text box to hold the NextNum. The current event of the form can populate the text box with a dMax() but you will have to add code to update the value after you run the append queries.