Normalize Table re Multiple Dates

[Carrie]

Hi, Awhile back in error, I created a db with multiple date fields (19 in
all). All are due dates or reminder dates (which are calculated off of the
due date) for various actions. Of course I have been unable to report the
way we need to. After reading many posts, I now know that I should normalize
my db and only use one date field but for some reason I can't get my head
around the way this should work. I need to store and report on all the
dates. Any advice is appreciated!

 

[Duane Hookom]

I have set up an application for a loan processing office. A single loan
application could go through many different steps. The steps could vary
based on the loan.

The basic structure:

tblLoans
===============
loaLoaID autonumber primary key
loaFileNumber
.......

tblSteps
===============
stpStpID autonumber primary key
stpTitle text value like "create file" or "request first born"
stpDaysFromDue numeric field that could serve as a default
stp....

tblLoanSteps
================
losLoSID autonumber pirmary key
losLoaID link to tblLoans.loaLoaID
losStpID lint to tblSteps.stpStpID
losDateComp date completed
losEmpID link to tblEmployees.empEmpID
losComments
.....

 

[mnature]

tbl_DateTypes
DateTypeID (PK)
DateTypeName

tbl_VariousDates
VariousDatesID (PK)
DateTypeID [Links over to tbl_DateTypes for whichever type of date this is]
TrackedDate [Here is where you put your actual date]
MainTableID [Since you don't specify what this ties in to, this will be the
primary key in your main table, which could be customerID, clientID, eventID]

tbl_VariousDates could be your main table, with VariousDatesID actually
being the customerID or whatever. You don't give a lot of information, so
hopefully this gives you an idea of what to do.