Custom ID number

[Kill_Switch]

Good evening.

I'm creating a database in which when a new process verification is generated it is given a new record number. (2010-001, 2010-002, etc).

My question is, I would like this number to be automatically generated once the user wishes to create a new "process verification". How would I go about doing this? This number will be the PRI KEY as there will be no duplicate records of these audits/verification.

yyyy-\[next seq number] ?

Any help would be great.

 

[John Big Booty]

Personally I would use an auto number as your PK. This reference number you can construct by using the DMax() function +1 and then concatenate it for display purposes with the year.

 

[Kill_Switch]

So would it be something like this? And just set the field to "Duplicates Allowed" to "no"?

DMax("yyyy") function +1

 

[The_Doc_Man]

You would probably do better to do this differently than you described.

When you have a complex primary key (PK), trying to do it in one field is almost guaranteed to thin out your hair. (From all the ripping out ...). Have the CURYEAR field separate from the YEARSEQ field. (My names avoid reserved words, word to the wise...).

Then a new YEARSEQ is simply a 1 + DMAX("[YEARSEQ]", "yourtable", "[CURYEAR] = " & Format( Now(), "yyyy" ) ) ... or 1 + DCOUNT(... same criteria... )

...or something like that.

Once you have a year and a unique sequence number, the rest is a matter of how you choose to display it. The two fields don't actually need to be linked together any time EXCEPT when you display them in forms or reports if you use a synthetic key for the REAL PK. For efficiency's sake, I suggest that you have a separate autonumber key to use for relationship management.

A common mistake made by less experienced designers is to think they must use their company's internal numbering system as a PK no matter how messy that number happens to be. In truth, when building a multi-table system with real relationships among member tables, you want the key to be as short as possible. An autonumber takes 4 bytes. The key you described would take not less than nine bytes (yyyy-nnnn). Remember that PKs are copied literally to tables where they are used as foreign keys (FK), so you just made you other tables 5 bytes longer than they really needed to be. Size will eventually bite you on the butt one way or another.

Thing about PK/FK is that if the PK is synthetic / autonumbered, it can stay invisible all of the time anyway and just stay behind the scenes helping you along with the real relationships. Then queries that join the tables can exploit the REAL PK to access the two-part company key when it is needed.

 

[Kill_Switch]

Great, thanks guys. I'll definately be plugging away at this within the upcoming days. And be posting back. I got about 5-6 pages of a plan of attack written down.

The quality office, is very excited that I'm attempting this endeavor to say the least. I told them it is possible and will decrease your work load by 50%. Cause they go between 2 excel sheet, each with 3-4 workbooks. So entering this data into 1-2 forms will drastically help, along with the relationships.

Thanks once again.

-Anderson



*gathering thoughts*

 

[John Big Booty]

Here's a small sample DB that uses the Dmax() function to increment a counter.

 

[Kill_Switch]

Okay, looked at the relation and the expression, and for my sanity do I have this logic correct?

me.counter = Nz(DMax("MyCounter", "CounterTable")("YearVariable", "YearTable" = " & Format( Now(), "yyyy" ))) + 1

So going by what you said above, "MyCounter" and "YearVariable" would be in two different tables as field names, however the ID for those would have the relationship.

And I see that every time I opened the form, the number went up, perfect. Will it auto adjust for the current year if yyyy is used, I would think so, just would like to confirm.

And does that above code look about right? Sorry, don't know vba, learning slowly.

 

[John Big Booty]

Personally I would store the counter and the date separately, and then only concatenate them for display purposes, on your forms or reports. ie. in unbound text box that uses something like;

=Counter & "-" & Format("YYYY", YourDateFieid)

Also avoid using the Now() function unless you absolutely need a time component to your date, use instead Date() which will simply return the current date.

 

[John Big Booty]

Here's a quick sample.

 

[Galaxiom]

In truth, when building a multi-table system with real relationships among member tables, you want the key to be as short as possible. An autonumber takes 4 bytes. The key you described would take not less than nine bytes (yyyy-nnnn).

Wise advice.

The key described is a text datatype and text data as a key can be potentially even worse than this particular design. Being case insensitive, Access compares character by character against both upper and lower cases.

Always avoid text based primary keys, joins and any comparisons on text fields whereever possible.