X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Custom Primary Key, Access 2010    
post Dec 14 2017, 09:47 AM

Posts: 34
Joined: 2-June 17

I'm designing a table to contain deficiency reports. Our deficiency reports our cataloged as yyyy-001 (i.e. 2017-001). Each time a new report is entered, I'd like the number to increment automatically (autonumber). I'd like this to be the primary key, but it's not a requirement. I can combine the year part of a DateEntered field with an autonumber field to make a ReportSerial field, the problem is getting the autonumber field to reset with a new year. Anyone have an idea on how I can do that (or maybe a totally different way of going about this altogether)?
Go to the top of the page
post Dec 14 2017, 09:52 AM

UtterAccess VIP
Posts: 2,823
Joined: 25-June 05
From: @ 8300' in the Colorado Rocky Mountains

Use a standard AutoNumber for your PrimaryKey and then this link for your other field: Custom AutoNumbers

(RG for short) aka Allan Bunch Previous MS Access MVP acXP, ac07, ac10, ac13 - WinXP Pro, Win7 Pro, Win10 Pro
Please reply to the forum so all may benefit.
Go to the top of the page
Jeff B.
post Dec 14 2017, 11:07 AM

UtterAccess VIP
Posts: 9,958
Joined: 30-April 10
From: Pacific NorthWet

If having an incremental number is a requirement, Access Autonumber is NOT what you want to use. As suggested, check out Custom Autonumber...


Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
post Dec 14 2017, 02:38 PM

UtterAccess Editor
Posts: 17,952
Joined: 4-December 03
From: Northern Virginia, USA

What happens when you get more than 999 reports?

Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
post Dec 14 2017, 03:27 PM

Posts: 1,356
Joined: 7-April 10
From: Detroit, MI

Is the number increasing every day so you would end up with 2017-365, then want to go to 2018-001? If so, just increase the ID (primary) number by finding max + 1. If right(yourField) = 365, reset to year + 1 - 001.

Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
post Dec 18 2017, 01:58 PM

Posts: 34
Joined: 2-June 17

These are counting deficiency reports. We average about 20 per year for the last 5 years. I'd say that if we were to get above 100 in one year, I wouldn't have to worry about this database anymore. I'd be more concerned with finding new employment quickly. lol
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    19th April 2018 - 07:50 PM