Making a Custom Primary Key based on Date and Activity in a day

[Krash878]

Hello

Made a database to keep track of how many calls we are getting in a day. We are also trying to use this database as a helpdesk log that let us keep track of what issues are closed and which issues are not.

Anyway

We want to use a primary key with this structure. YYYYMMDD####

The #'s are the number of occurrences that day.

For instance today we would have
200403040001
200403040002
200403040003
200403040004

And tomorrow would be
200403050001
200403050002
200403050003
200403050004

What would be the best way to make this happen?

Thanks

Krash

 

[Darrylles]

Hi,

Haven't tested this, but should be ok.....

You'll already have two fields, or variables. Let's call 'em myDate as a date and myActCount as an integer.

To combine these into a primary key, the pk must be a string, so you need to convert the date and integer to string:

pk = str(myDate) & str(myActCount)

this would however give you:

200403041
200403042
200403043
200403044

because myActCount would be 1,2,3,4 etc not 0001,0002,0003.

To get around this, you need 'Format':

pk = str(myDate) & str(format(myActCount,"####"))

which gives:

200403040001
200403040002
200403040003
200403040004
-------------------------------------------------------
A way to maintain the myActCount value (and ensure that it resets each day) may be:

Simple table with 2 fields: myActCount and myCountDate.
Table will only ever contain one record.

Every time you access this record (to get the last myActCount value in order to increment it) - you check the myCountDate value. If it's not today's date, then this is the first 'transaction' of the day, so myActCount should be set to 1 and myCountDate set to today's date.
-----------------------------------------------------

Hope this helps,

ATB

Darrylle



"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@hotmail.com