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:

Public Function fGenerateNextTaskID() As String
'Task ID Format: YYYY-JJ-TT (Year - Job# - Task#)
Dim strLastTaskID As String
Dim intLastTaskIDYear As Integer
Dim intLastTaskIDJobNum As Integer
Dim intLastTaskIDTaskNum As Integer
 
Dim intNextTaskIDYear As Integer
Dim intNextTaskIDJobNum As Integer
Dim intNextTaskIDTaskNum As Integer
 
strLastTaskID = DLast("[TaskID]", "tblTasks")
 
intLastTaskIDYear = Val(Left$(strLastTaskID, 4))
intLastTaskIDJobNum = Val(Mid$(strLastTaskID, 6, 2))
intLastTaskIDTaskNum = Val(Right$(strLastTaskID, 2))
 
If Val(intLastTaskIDYear) = Year(Date) Then             'Not a New Year
  intNextTaskIDYear = intLastTaskIDYear
Else
  intNextTaskIDYear = intLastTaskIDYear + 1             'New Year, increment by 1
End If
 
'Missing information concerning Job Number
'If Job Number is the same
  intNextTaskIDJobNum = intLastTaskIDJobNum             'maintain Current Job#
  intNextTaskIDTaskNum = intLastTaskIDTaskNum + 1       'increment Task#
'Else
  intNextTaskIDJobNum = intLastTaskIDJobNum + 1         'increment Current Job#
  intNextTaskIDTaskNum = 1                              'Reset Task# to 1
'End If
 
fGenerateNextTaskID = CStr(intNextTaskIDYear) + "-" & Format$(intNextTaskIDJobNum, "00") & _
                      "-" & Format$(intNextTaskIDTaskNum, "00")
End Function

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.