Best Way to Track a "Limitless" Process

[d-mcc56]

Hey guys,



So quick description here,

I have a form that displays information on construction agreements people have with us.
You can search for an agreement and have it display all the information and important dates associated with that agreement.
When the construction is completed for an agreement the contractor has to call us and request an inspection for our approval.
Upon their request we have 30 days to actually go do the inspection and either approve or reject the construction.

So 4 dates need to be recorded: Inspection requested, inspection due by, inspection done on, and then either construction approved or construction rejected.
If it is rejected then the contractor needs to redo this process until it is approved, so it could potentially go on forever.

I am asking you guys if you have any ideas as to what would be the best way to record this information?
I do no want to rewrite over old dates, I want to be able to see all of the dates for every time a contractor goes through this process.

My thoughts were to include a subform on the form in datasheet view that is continuous with the 5 dates as headings.
I'd love to hear your ideas for a different approach to this.

Thanks guys!

[burrina]

Up to you for your taste preferences. That should work or else a Continuous subform.

[rpeare]

if each 'round' of inspections has a inspection requested date, you do not need the inspection due field

If the inspection is requested on 1/1/2014 you can get the 'due by' date by the formula dateadd ("d", 30, [InspectionRequestDate])
so realistically you only need 3 points of data for every round (at least as you have stated it)

a PK for the table that will carry this information
a FK to the contract table that the inspections are related to
NOTE the first two can be handled if you're using bound forms by using a form/subform arrangement, if you're using unbound forms it's a little different but you still need both of these.
a 'inspection requested' date
an 'inspection completed' date
an checkbox that can mean either 'approved' or 'rejected' (it doesn't really matter)

[d-mcc56]

Yeah I am using DateAdd for the due date but I still need the date to be displayed so I included it.
PK? FK?

[rpeare]

PK is the primary key (an autonumber) to provide a single field to uniquely identify records
FK is a foreign key the value that relates the table another table, a table can contain multiple FK's.
you can get the 'due by' date either in a query or to display on a form using the formula I gave you. It should not be stored in your table, nothing calculated should be stored.

[d-mcc56]

Ah okay thanks, never heard of them referenced that way, still an access noobie.

Thanks!