Suggestions for handling pseudo-workflow

[Larry Kahm]

I have been asked to add a kind of workflow to an existing applicaiton. At
first I thought it would be simple, but now my brain has frozen and I'm
stumped - so I am looking for some help.

The group has a table that contains listings of in-house press
announcements. The new workflow element that they want is to add trask the
tasks associated with these announcements. There are six tasks (scan the
article, obtain images, validate participants, etc.), each of which has a
date on which it is completed.

I'm blocked as to the table structure(s) - although I realize that I have to
link the main ArticleID to a smaller table that contains ArticleTasks - but
I can't see building a table with six date fields = that's not normalized.
I am also totally frustrated as to how this form should look.

Any help is greately appreciated.

Thanks!

Larry

 

[Ken Sheridan]

What you have is a many-to-many relationship type between Articles and Tasks,
so you are quite right in concluding that you need an ArticleTasks table to
model the relationship type. However, you don't have 6 separate date/time
columns (that's encoding data as column headings and is verboten). What you
have is from 0 to 6 rows per article, entered as the tasks are completed, so
the table has columns ArticleID, TaskID and DateCompleted, the first two
being the composite primary key of the table.

For data entry you'd have an Articles form in single form view with an
ArticleTasks subform embedded in it, the latter based on a query:

SELECT *
FROM ArticleTasks
ORDER BY DateCompleted;

so that the rows in the subform are ordered by date. The subform, in
continuous form view, will be linked to the parent form by setting the
LinkMasterFields and LinkChildFields properties to ArticleID, and will
contain a combo box bound to the TaskID column set up as follows:

RowSource: SELECT TaskID, Task FROM Tasks ORDER BY Task;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

It will also contain a text box bound to the DateCompleted column.

As each task for the parent form's current article is completed a new row is
inserted in the subform, selecting a task in the combo box and entering a
date.

Ken Sheridan
Stafford, England

 

[Larry Kahm]

Thank you both for your suggestions. They did help to unfreeze my brain.

Larry