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,826
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
Go to the top of the page
Jeff B.
post Dec 14 2017, 11:07 AM

UtterAccess VIP
Posts: 10,181
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...
Go to the top of the page
post Dec 14 2017, 02:38 PM

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

What happens when you get more than 999 reports?
Go to the top of the page
post Dec 14 2017, 03:27 PM

Posts: 1,495
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.
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    16th January 2019 - 08:01 PM