Better ID Numbers

[peterkennett]

I'm sure this has been posted many times, but I never had a need until now.

- I have a DB that tracks engineering jobs.
- Each JOB can have many TASKS.
- I need to track each TASK by a unique ID number in this format:

YYYY - JOB# - TASK#

Each Job number is sequential starting at 1 on January 1st.
Each Task number is sequential starting at 1 with each new JOB.

For example:

2008-37-02 is the 2nd task of the 37th job in the year 2008.

Every new JOB moves starts the task counter back to 1.
Every new YEAR starts the JOB numbers back to 1.

What is the best way to do this?

Thanks!

Peter

[janders468]

I think the best way is to have two tables, a job table and a task table, the job number would be the primary key of the job table and the task number would be the primary key of the task table, the task table should contain a foreign key of the job table ID. You can then add as many related tasks as you need for the associated job table.

[ADezii]

I'm sure this has been posted many times, but I never had a need until now.

- I have a DB that tracks engineering jobs.
- Each JOB can have many TASKS.
- I need to track each TASK by a unique ID number in this format:

YYYY - JOB# - TASK#

Each Job number is sequential starting at 1 on January 1st.
Each Task number is sequential starting at 1 with each new JOB.

For example:

2008-37-02 is the 2nd task of the 37th job in the year 2008.

Every new JOB moves starts the task counter back to 1.
Every new YEAR starts the JOB numbers back to 1.

What is the best way to do this?

Thanks!

Peter

How do you know when an old job has finished and a new one begun, this is the critical question.

[ADezii]

I'm sure this has been posted many times, but I never had a need until now.

- I have a DB that tracks engineering jobs.
- Each JOB can have many TASKS.
- I need to track each TASK by a unique ID number in this format:

YYYY - JOB# - TASK#

Each Job number is sequential starting at 1 on January 1st.
Each Task number is sequential starting at 1 with each new JOB.

For example:

2008-37-02 is the 2nd task of the 37th job in the year 2008.

Every new JOB moves starts the task counter back to 1.
Every new YEAR starts the JOB numbers back to 1.

What is the best way to do this?

Thanks!

Peter

The basic logic to generate the next, sequential, TaskID Number, minus the missing critical piece of information listed previously in Post #3 would be something similar to:
[CODE=vb]
Public Function fGenerateNextTa skID() As String
'Task ID Format: YYYY-JJ-TT (Year - Job# - Task#)
Dim strLastTaskID As String
Dim intLastTaskIDYe ar As Integer
Dim intLastTaskIDJo bNum As Integer
Dim intLastTaskIDTa skNum As Integer

Dim intNextTaskIDYe ar As Integer
Dim intNextTaskIDJo bNum As Integer
Dim intNextTaskIDTa skNum As Integer

strLastTaskID = DLast("[TaskID]", "tblTasks")

intLastTaskIDYe ar = Val(Left$(strLa stTaskID, 4))
intLastTaskIDJo bNum = Val(Mid$(strLas tTaskID, 6, 2))
intLastTaskIDTa skNum = Val(Right$(strL astTaskID, 2))

If Val(intLastTask IDYear) = Year(Date) Then 'Not a New Year
intNextTaskIDYe ar = intLastTaskIDYe ar
Else
intNextTaskIDYe ar = intLastTaskIDYe ar + 1 'New Year, increment by 1
End If

'Missing information concerning Job Number
'If Job Number is the same
intNextTaskIDJo bNum = intLastTaskIDJo bNum 'maintain Current Job#
intNextTaskIDTa skNum = intLastTaskIDTa skNum + 1 'increment Task#
'Else
intNextTaskIDJo bNum = intLastTaskIDJo bNum + 1 'increment Current Job#
intNextTaskIDTa skNum = 1 'Reset Task# to 1
'End If

fGenerateNextTa skID = CStr(intNextTas kIDYear) + "-" & Format$(intNext TaskIDJobNum, "00") & _
"-" & Format$(intNext TaskIDTaskNum, "00")
End Function[/CODE]
NOTE: This code requires a strick adherence to the TaskID Format of: YYYY-JJ-TT and a seeding of the 1st ID, meaning that you need only a single, manually entered ID, for the code to work.