Sequential Number and date (1 Viewer)

[Jonny45wakey]

Hi

I have been scratching my head on this one and still no solution so hoping someone with more access intelligence than me can help?

I have a table called tblEnquiry which has an autonumber PK field called EnquiryID

What i would like is when a record is generated the PK field "EnquiryID" looks as follows:-

Todays date in this format (dd-mmm-yyyy) followed by a sequential number 001

if a record added on same day then the PK entry "EnquiryID" is same date but sequential number 002 and so on

Hope this makes sense?

Appreciate any help

Thanks

Jonny

 

[Cronk]

What you are wanting is too complicated. Just make a normal autonumber ID. (Autonumber is a long integer and what you are wanting is a primary key which would be text.) Have a date field. It's easy in code to generate the inquiry order on the same day from the autonumber. But then again why is it so important to be able to show that a particular inquiry is the fourth one for the day. (It's also simple to count the number of inquiries on a particular day with a query or dcount function.)

 

[CJ_London]

and if you store date/time (using now() rather than date), easy to see the order in which enquiries were made. And if you really need to see 001, 002, use what is called a running count.

Your way means your PK field is text - and text takes up much more space than a number. An autonumber (long) takes up 4 bytes, your method requires 15 chars so takes up 32 bytes (2 bytes per char + 2 bytes)

Double it for the index - 8 bytes and 64 bytes. And if there are child records that will be another 8 or 64 bytes for each child. So very simplistically your app will be 8 time slower and grow much more quickly with your text solution.

Another issue is that with a text value of dd-mmm-yyyy your sorting will be all over the place. You will get all the first of months sorted on monthname (so April will appear before January), followed by the second of the month, and so on

 

[The_Doc_Man]

When you are dealing with autonumbers, they are guaranteed to be unique (in the absence of a corrupted database). If you pick the ascending rather than random autonumber, the sequences are ascending but might have gaps due to deletions or cancellations of new entries. The value created for each autonumber is not usable for many purposes because it is first and foremost a unique record identifier. As such they are suitable for relationships and as prime keys for an independent table. The only other thing I ever used them for was a "confirmation number" if we issued a help desk ticket for someone.

When you add all sorts of "complications" to that PK, you are making extra trouble for yourself - because that complicated PK will have to be repeated in every table that has a foreign key linking back to the PK. Which means you just bloated your DB. (E.g. the factors that CJ noted.) There are many ways to identify a record for display purposes that don't complicate the internal record references. You can compute any kind of info you want on a form that has only an indirect relation to the storage information.

Store an autonumber PK for the record, then remember the date (and time). There are ways to develop a simple counter to record the ordinal number of the day for this record that don't have to be involved in linkages.

 

[LarryE]

Hi

I have been scratching my head on this one and still no solution so hoping someone with more access intelligence than me can help?

I have a table called tblEnquiry which has an autonumber PK field called EnquiryID

What i would like is when a record is generated the PK field "EnquiryID" looks as follows:-

Todays date in this format (dd-mmm-yyyy) followed by a sequential number 001

if a record added on same day then the PK entry "EnquiryID" is same date but sequential number 002 and so on

Hope this makes sense?

Appreciate any help

Thanks

Jonny

So each day the sequential numbers start over with 001? Is that correct?

 

[Pat Hartman]

The primary key of a table should be an autonumber rather than an "intelligent" user-friendly string. Which isn't to say that you can't have an "intelligent" string if your users really want one. The important part is to keep the pieces of the "intelligent" Unique Index (not PK) in separate fields so the sequence number can be easily generated without a lot of code to split it out.

Here's an example that shows how to generate an "intelligent" identifier. Not to your specs though so you'll have to look at the code and understand how it works so you can do what you want.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk