By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,442 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Better ID Numbers

P: 12
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
Mar 13 '08 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 112
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.
Mar 15 '08 #2

ADezii
Expert 5K+
P: 8,638
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.
Mar 16 '08 #3

ADezii
Expert 5K+
P: 8,638
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function fGenerateNextTaskID() As String
  2. 'Task ID Format: YYYY-JJ-TT (Year - Job# - Task#)
  3. Dim strLastTaskID As String
  4. Dim intLastTaskIDYear As Integer
  5. Dim intLastTaskIDJobNum As Integer
  6. Dim intLastTaskIDTaskNum As Integer
  7.  
  8. Dim intNextTaskIDYear As Integer
  9. Dim intNextTaskIDJobNum As Integer
  10. Dim intNextTaskIDTaskNum As Integer
  11.  
  12. strLastTaskID = DLast("[TaskID]", "tblTasks")
  13.  
  14. intLastTaskIDYear = Val(Left$(strLastTaskID, 4))
  15. intLastTaskIDJobNum = Val(Mid$(strLastTaskID, 6, 2))
  16. intLastTaskIDTaskNum = Val(Right$(strLastTaskID, 2))
  17.  
  18. If Val(intLastTaskIDYear) = Year(Date) Then             'Not a New Year
  19.   intNextTaskIDYear = intLastTaskIDYear
  20. Else
  21.   intNextTaskIDYear = intLastTaskIDYear + 1             'New Year, increment by 1
  22. End If
  23.  
  24. 'Missing information concerning Job Number
  25. 'If Job Number is the same
  26.   intNextTaskIDJobNum = intLastTaskIDJobNum             'maintain Current Job#
  27.   intNextTaskIDTaskNum = intLastTaskIDTaskNum + 1       'increment Task#
  28. 'Else
  29.   intNextTaskIDJobNum = intLastTaskIDJobNum + 1         'increment Current Job#
  30.   intNextTaskIDTaskNum = 1                              'Reset Task# to 1
  31. 'End If
  32.  
  33. fGenerateNextTaskID = CStr(intNextTaskIDYear) + "-" & Format$(intNextTaskIDJobNum, "00") & _
  34.                       "-" & Format$(intNextTaskIDTaskNum, "00")
  35. 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.
Mar 16 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.