Help with setting up unique ID on import

[Angel69]

Hi,

I have a user that uploads a spreadsheet weekly. I want to assign a unique id to each record that is the current date plus the start and end date for the data they are loading which would be the week prior plus a counter.

For example: Data from the week of 5/4-5/10 is loaded on 5/12.

Record one would be 050414-051014-1
Record two would be 050414-051014-2
Record three would be 050414-051014-3

and so on. How do I accomplish this in my table design? They will be deleting the prior week data and pasting the new data so the table design will not change.

Thanks!

 

[Cronk]

Given your format of 050414-051014-x, you need two fields, the date of import (05/04/2014) and a sequential number (x).

Rather than storing the dashes and the end of week, just include these in the format of any output in reports, queries, forms.

I must say though I can't see any reason for storing a sequential number.

 

[gemma-the-husky]

If you need a cross ref back to the original excel sheet, than add a sequential number column to the spreadsheet

there is absolutely no guarantee (and indeed it is most unlikely) that the access table will be represented in the same order as the original spreadsheet.