Date Increment

[James Tebb]

I am building database to log the journeys a driver has to make in connection with a job.


My table has the fields "Job Title", "Job Start Date", "Job End Date"
It also has fields for "Date Day 1", "Destination Day 1", "Date Day 2", "Destination Day 2" etc

When I fill in the Job Start and end date I want "Date Day 1" to equal Job start date.
I then want "Date Day 2" to automatically be the next day, Day three the day after etc until the "Job End Date" is reached

How can I do this?

[jzwp11]

Welcome to the forum!

Having sequentially numbered fields: Date Day 1, Date Day 2 etc. and Destination 1, Destination 2 etc. are examples of repeating groups which indicates that your database is not normalized. For an overview of normalization, please check out this site. The problem with repeating groups is what would happen to your table if you need to add another day field? You would have to restructure the table and ALL related forms, queries and reports. I would not want to do that. Additionally, you are limited to 255 fields in Access. I would be better to have the day/destinations in a separate table that relates back to the job table. Also, what happens if the job does not start or end on the expected start/end dates?

In terms of the correct table structure it would look more like this

tblJobs
-pkJobID primary key, autonumber
-JobNumber
-dteExpectStart (date/time field)
-dteExpectEnd (date/time field)

tblJobProgress
-pkJobProgID primary key, autonumber
-fkJobID foreign key that relates back to tblJobs; this field must be a long integer datatype field
-dteProgress the date (equivalent to the date for the day)
-fkLocationID foreign key to a table that holds the possible locations assuming that you go to the same locations on a regular basis


tblLocations (just holds a list of all possible locations)
-pkLocationID primary key, autonumber
-txtLocationName

Having the above also saves you the time of retyping the same location names over and over again in the tblJobProgress table.