Normalizing data question

[NXMold]

I have a "Jobs" table with fields "Promised Date" and "Ship Date". As a job evolves, these dates are frequently revised. Then, after its done, a job might come back for rework and the dates are revised some more.

I want to track on-time shipping, but under this model I cannot automate it. There is a change history kept in a memo field by a VB function, but this requires a lot of manual labor to extract and report the information (using a separate excel sheet).

But as the job evolves, there is always one current promised date.

I am thinking I have to make a table for this date, the question is do I:

1) keep the existing table as-is, and copy this date to the new table programmatically in the after-update event

2) remove the existing field, and somehow query it from the new table to show only the most current record

Looking for some pro's/con's.

 

[dhookom]

If you need to track a history of dates, I would consider keeping a table of job dates with all of the dates in it. I would expect the structure to be something like:
[tt][blue]
tblJobDates
=============
jbdJbDID
jbdJobID links to job table
jbdDateType (Promised, Shipped, ...)
jbdDate the date
jbdUpdated date the record was updated
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[NXMold]

That's what I had in mind, but how do you display/modify this field as part of the main record?

Display on a form/report is easy, a query could be made, or dlookup. But in order to edit, a new record must be created and the form requeried. I guess I could code that with VB and SQL (I've recently learned such tricks) but it seems the long way around. Of course edits could be made using a popup form.

 

[dhookom]

You would edit all of the date records in a subform on the job main form.

Duane
Hook'D on Access
MS Access MVP