DB Design Question - Nearly identical records

C

Clif McIrvin

I've noted with interest various threads dealing with the question of
incrementing values, and have noticed that there seems to be a common
reaction along the lines of, "That's a calculated value, and shouldn't
be stored."

In my application under development one of the requirements is to handle
scheduled piece-work (or custom) production. It is quite common for a
job to have a series of pieces identified by the JobID and an
incrementing suffix.

When entering new jobs, it is quite convenient to enter the common
information for each piece once and simply duplicate that record while
incrementing the suffix for each successive piece.

From when I began getting acquainted with Access (and RDB concepts)
until now I have learned a lot, and have seen how to restructure my
production data design from 'wide' to 'tall' using child tables for a
variety of data that may (or may not!) become related to a piece during
it's production cycle -- but I have not seen any other way to handle the
production records themselves - that is many individual records nearly
identical except for the ID suffix, production date and various related
child records.

The (simplified) structure I am contemplating looks something like this:

Jobs table
JobKey, autonumber, PK
JobID, Human assigned, Text, Indexed, Duplicates Allowed (I don't like
that part, but without major changes to the way internal company systems
work that isn't going to change any time soon.)
JobName
(etc)

Production table
PrdKey, autonumber, PK
JobID, FK
Piece, Text (incrementing suffix described above)
JobID + Piece, Index, Duplicates Allowed
Date Produced
Date Promised
(etc)

PrdRemarks table
RemID, autonumber, PK
PrdKey, FK
Remark, Text
RemDate, Date

QCInfo table
QCID, autonumber, PK
PrdKey, FK
QCDate, Date of inspection
QCStatus
(etc)

Anyone have comments or suggestions for me?
 
K

Ken Sheridan

Firstly, the bar on storing computed values only applies to those which can
be time-independently derived from other values, not on computed values per
se.

In your production table don't repeat the JobID as part of the Piece column;
simply store the suffix in the Piece column. You can increment it in a
form's BeforeInsert event procedure with:

Me.Piece = Nz(DMax("Piece", "Production", "JobID = " & Me.JobID),0) + 1

For presentation you can concatenate the JobID and Piece column values at
any time, e.g. JobID & "-" & Piece.

I've assumed JobID is a number data type. If its text:

Me.Piece = Nz(DMax("Piece", "Production", "JobID = """ & Me.JobID & """"),0)
+ 1

Ken Sheridan
Stafford, England
 
C

Clif McIrvin

Ken Sheridan said:
Firstly, the bar on storing computed values only applies to those
which can
be time-independently derived from other values, not on computed
values per
se.

In your production table don't repeat the JobID as part of the Piece
column;
simply store the suffix in the Piece column. You can increment it in
a
form's BeforeInsert event procedure with:

Me.Piece = Nz(DMax("Piece", "Production", "JobID = " & Me.JobID),0) +
1

For presentation you can concatenate the JobID and Piece column values
at
any time, e.g. JobID & "-" & Piece.

I've assumed JobID is a number data type. If its text:

Me.Piece = Nz(DMax("Piece", "Production", "JobID = """ & Me.JobID &
""""),0)
+ 1


Thanks, Ken. Sounds like I'm heading in the proper direction this time;
that's reassuring to hear!

Since both fields are text, and the Piece number is pre-assigned, and
..... I've written a VBA function to handle the incrementing. It seems to
work fairly well; at least I've not seen serious enough issues to break
off from other tasks to tweak it any further. I've seen that DMax
concept presented here before; someday I may even get an opportunity to
use it!

I'm incrementing in the Form's current event if in a new record with
some other test logic, and using the control's DefaultValue property to
propagate the values from the previous record -- so if the piece number
sequence breaks it's a simple matter for the user to supply the correct
value and proceed to the next record. I also learned how to cause access
to add a new record when I down-arrow into the add row in datasheet
view -- works pretty slick when all that's changing is the incrementing
number.

Side note: I've managed to experience some of the difficulties resulting
from 'committing spreadsheet' that you folks warn us newbies about --
hard experience can be a pretty good teacher! That's why I'm re-working
my table structure.

Thanks again; and thanks also for all I've learned from you during these
months of (mostly) lurking! You crank out some pretty detailed
explanations.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top