Job Numbers and Sub Job Numbers - How to store

[richard]

Hi

I have a database with various tables of which I have a question about the
following two.

tbltable1
tblnapswork

table1 stores the address fields of sites we do work at with a unique site
reference number for each site.

tblnapswork stores the detail of the work to be carried out using the site
reference number to create the link to the site address. There is a jobnumber
field which is increased as each job is created using DMax+1.

Some sites are large and currently one job is created for all the same work
on a site, however there is a need to track work progress and we are looking
at breaking the job down into smaller parts by creating sub jobs.
Currently the job number is just 1294 and we are proposing 1294/001 with the
001 bit incrementing with each sub job upto 999. We are comfortable with 999
as a max due to the size of buildings we are dealing with or are likely to
deal with.

Currently the site address is entered into my database and then into another
database completely seperately, however this second database is being
re-written in SQL and the thought process is that the operator enters the
jobnumber in the rewritten database and this will pull through all the
relevant fields needed, ie address, work type etc from mine, hopefully
eliminating some human error!!!!!!!!!!!!!!

So the question is how to store the job number and sub job numbers

1 I could create a field withing tblnapswork called subjobnumber and store
the two parts of the number seperately (looking something like: field 1 -
1294 field 2 - 001) or

2 I store the jobnumber and the subjobnumber in the same field within
tblnapswork (looking something like 1294/001)

I am looking for any comments on how practical the two options are and also
if there may be any other options we have missed.

I am confident I can achieve option 1 but am unsure how option 2 would work
as I presume it would be a text field and I would want to increment both
parts of the number at different times.

Thanks in advance

Richard

 

[Distore]

Let's go shopping!
My shop is http:376512972.paipai.com

 

[Ken Sheridan]

Richard:

I'd suggest that you decompose the tblnapswork table into two tables, one to
represent each job as a whole, and one to represent the sub-jobs. The
columns of the former would represent the attributes of the job as a whole,
while the columns of the latter would represent the attributes specific to
the sub-job. That way any redundancy, and possible inconsistencies arising
from this, is avoided.

The key of the jobs table can remain as existing, i.e. the jobnumber. The
sub-jobs table would also have a jobnumber columns as a foreign key
referencing the primary key of the jobs table. It would also contain a
subcode column (or whatever you want to call it), numbered from 1 upwards for
each job. Together these two columns would form the primary key of the
sub-jobs table.

Both the jobnumber and subcode columns can be integer number data types.
They can easily be combined in a query, form or report as:

Format(jobnumber,"0000") & "/" & Format(subcode,"000")

For data entry a jobs form with a sub-jobs subform, linked on jobnumber,
would be appropriate. You can automatically generate the subcodes in the
same way as the jobnumbers with:

DMax("subcode", "subjobs", "jobnumber = " & [jobnumber]) + 1

A further argument for decomposing the table is that while the subcode could
still be automatically generated easily if a single table were used, the
jobnumber could not be. With two tables on the other hand the jobnumber is
determined when a new row is inserted into the jobs table, this is
automatically assigned to each subjob by the link between the form and
subform, and the subcode can then be automatically generated as above.

Bear in mind (and this applies to your current scenario too) that in a
multi-user environment on a network there is the possibility of the same
number being returned by the DMax function for separate users if they are
adding new records simultaneously. It important, therefore that the columns
are correctly indexed so as to prevent duplicate values being saved to the
table.

Ken Sheridan
Stafford, England