Design Question

[Two Gun]

I have a process at work with a beginning and ending date. Within those dates, several people will be assigned responsibility at a particular point in time (date). When they're done with their part of the process, they "hand off" to another person, again on a particular date. When the last person completes the process, they update the Ending Date and we're done.



What I want to do is capture the entire "chain of custody" from the beginning date to ending date, and identify the names and assigned dates of all those who had custody for any period of time. During the process, I want to calculate in a form and report a running total of the number of days the process is taking and when the process is complete, record the number of total days from beginning to end, as well as the number of days each person took to "do their part".

Seems simple enough ... but I'm lost.

Six fields (I suspect):

Beginning Date - Date the process starts
Assigned To - Name of person the process is initially assigned to
Date Assigned - Date this person was assigned the process
Transferred To - Name of person the process was handed off to
Date Transferred - Date the process was transferred to this new person
Ending Date - Date process complete

The calculation piece (number of days in the process) is not a problem. But I'm not sure how I can use the Transferred To field and Date Transferred field to efficiently capture multiple hand offs, or even if I'm on the right track. Appreciate the help and direction.

[June7]

Normalized table to allow any number of 'hand-offs':

ID
ProjectID
Stage (initial, transferred, etc.)
StaffID
DateReceived
DateCompleted

[Two Gun]

June7 - Sorry, but if you don't mind walking me through this ... my Access knowledge and experience are marginal. Does your example represent joined tables in a query or one normalized table? Are ProjectID and StaffID fields autonumbers? Thanks for your patience.

[June7]

I am suggesting a table with those fields. ProjectID and StaffID are foreign key fields relating to primary keys of other tables. Only guessing, but I invisioned you have some sort of table of unique 'projects' or 'processes' that you are tracking progress for and a table of staff drawn on to accomplish each stage of the project.

[Two Gun]

Actually, I was working with only one table and one data entry form. I think I mentioned my experience was marginal.

In my table I have a field called ActionNumber that provides an automatic date/sequence number, effectively assigning a unique number to each process. Would this substitute for your foreign key "ProjectID" field or must this information be in a separate table?

Would the StaffID table, in addition to the primary key, contain only the names of staff members who could be assigned a process?

Assuming I can construct the table(s) correctly, would I be able to update this information from one data entry form?

I know this is turning into a "teaching moment", but I really appreciate your assistance.

[June7]

The autonumber field itself could not be a 'foreign key'. Autonumber field generates unique value and can be designated as a primary key. A number field in another table would store values from the autonumber field as a foreign key. The two tables then have a relationship and can be joined in queries so that all related data is available.

Staff table would have names (each name part in its own field) and any other data about staff you want to document (phone number, whatever).

Projects table would have info about the project (title, client, department, whatever).

If you don't care about supplemental data on staff and projects, I suppose this could be done with one table. In the table I suggested, instead of StaffID would be StaffName entered like: Jones, Mary; instead of ProjectID would be ProjectName. Data consistency is always a concern and there are techniques to minimize errors but nothing can be foolproof.

All the progress records could be entered through one form. Adding new staff or projects would use other forms or some combination of form/subform could facilitate entry into multiple tables. However, would not record aggregate data such total days for project or per person. This data would be calculated as needed on reports.

This is basic Access functionality and I suggest you could benefit from time with an instructional text, such as Access 2010 in 10 Minutes or Access for Dummies or other introductory references.

Review
http://forums.aspfree.com/microsoft-...es-208217.html
http://office.microsoft.com/en-us/ac...010098674.aspx

[Two Gun]

Thank you for the direction and references ... time to spin up.