I am more or less an advanced level user in Excel, but I am relatively new to Access. I am hoping someone may be able to help me here. I work in a business that I have sometimes hundreds of transactions per day (I'm in the financial services industry). I have different tables for different types of transaction types. I don't want to mess with the Autonumber for each table, but I need to create a unique transaction number across all types of transactions. I need to create a "Trans # Field" and I need it in the following format... last digit of the year (i.e. - "6" for 2016)+ the day number of the year (i.e. - today 12/28/2016 would be 363 accounting for the leap year) + a number starting at 1000. For example, today's transactions would be 63631000, 63631001, 63631002, etc. Tomorrow, 63641000, 63641001, 63641002, etc.

Would it be possible to use an Expression in the Property Sheet or perhaps this would only be able to get done in VBA?

Thanks and happy holidays!

---

Reply from Alex Hedley:

How about a Composite Key?