Hi Alex. Happy New Year! Thank you for the recommendation. I think the composite key will be very useful for me for some of the applications in the DB I am working on, but for the specific purpose of what I am trying to do, it will not work or at least I need to do some kind of hybrid perhaps between a composite and what I need.

For example, I am in the financial services industry. I have traders that work in derivatives, others in equities, others in fixed income, etc. I am setting up separate tables for each products area, i.e. - EquitiesTradesT, FixIncTradesT, DerivativesT, etc. For a variety of reasons (regulatory, compliance, record keeping, trade report production,daily P&L, trader commissions, etc.), I need to have a common format of a Trade ID across all product trades.

I am keeping a regular autonumber ID for each table for the purposes of relationships, however, I need the "general" TradeID to have the format I mentioned in my original post. Therefore, in each table, I will have the exact same field, for the purposes of this forum, let's just call it, "TradeID". Let's say the first trade of the day is a fixed income trade with today's date, 01/04/2017. In the FixIncTradeT, the TradeID field, it should generate the number 70041000, i.e. - "7" for 201"7", "004" for the 4th day of the year, and "1000" for the first trade. The next trade is equities. The TradeID field in the table EquitiesTradesT will read 70041001, and so on.

Tomorrow will start at 70051000, i.e. - "7" for 201"7", "005" for the 5th day of the year, and "1000" for the first trade, etc. On December 31st, 2017, the first trade would be 73651000.

I know it seems a little confusing, and quite a bit of detail, and truthfully, it seems a little complicated, but it is necessary.

Cheers

---

Reply from Alex Hedley:

So you can use the Day(), Month(), Year() functions passing in Date() then you could use something like Right(Year(Date()), 1) to get the 7 from 2017.

For the TransactionId you could have a Transaction table with one Field, TransactionId which is a number and every time you create a record anywhere in your db you increment this number. Now just get this number and add 1 each new record.
There could be race conditions and if multiple people are using the db at the same time it could get messy but it's an idea.