UtterAccess.com
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    
 
   
scewing
post Dec 14 2017, 09:47 AM
Post#1



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
 
RuralGuy
post Dec 14 2017, 09:52 AM
Post#2


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
Post#3


UtterAccess VIP
Posts: 10,140
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
 
datAdrenaline
post Dec 14 2017, 02:38 PM
Post#4


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


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



Posts: 1,392
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
 
scewing
post Dec 18 2017, 01:58 PM
Post#6



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    17th October 2018 - 02:01 PM