process tracking

[Guest]

I have a 10 step process. Items entering into this process may or may not go
through all 10 steps. I want to set up a way to track each item going
through this process and display the current step this item is in ant any
given point. I also want to be able to display which steps are being bypassed
and which are not yet completed: In other words I want to end up with a
report or form that resembles

item1 - last step completed = step 6
step 1 - 7/1/06
step 2 - 7/2/06
step 3 - NA
step 4 - 7/4/06
step 5 - NA
step 6 - 7/7/06
step 7 -
step 8 - NA
step 9 -
step 10 -

would I be able to get these results from the following table structure?

tblProcess
-----------
ID (int)
item# (int)
step# (int)
dateStepComplete (date)
stepNA (bool)

The second part would be how would I structure the query to display these
results for a specific item#?

Thanks

Brian

 

[Guest]

Brian,

In your tblProcess, does it contain all 10 processes for each item or only
the completed or N/A processes? In other words, do you have a tbl which
includes all the steps for each item and then a table with the specific data
if a step has been complete or is it assumed all items go through the same
steps and tblProcess only contains the data either completed or n/a?

What I am looking for is a listing of all steps for each item so that you
can do a query with an outer join in order to get the results you are looking
for (including blank steps).

In order to get the last step completed, do a query with criteria on the
date completed field either >#01/01/2000# or based on a data entry field on a
form. Then, if you choose the Totals line on the query, have the Item number
a Group By field and choose Max for the step and it will give you the last
step with a date on it.

Let us know on the table stucture so that we can explain the queries for the
form/report.

Jackie

 

[Guest]

The table currently in use has each step contained within one item's record
(10 fields/steps per record) plus a y/n option for each step, leaving those
fields that are NA as null and checking the y/n field; then I resolve the NA
on the report through an iif expression. From previous discussions on this
group I gleened that this was not the most efficient or normalized way to
structure this table, so I was debating whether to change the table structure
to what I described in my post - one step per record - up to 10 records per
process.

example:

old table
-----------
ID
item#
step1 date
step1NA (bool)
step2 date
step2NA (bool)
step3 date
step3NA (bool)
........ step10 date
step10NA (bool)

so data would look like:

record# 1
ID 1
item# 1
step1 7/1/06
step1NA false
step2 7/2/06
step2NA false
step3
step3NA true
step4 7/4/06
step4NA false
step5
step5NA true
step6 7/7/06
step6NA
step7
step7NA
step8
step8NA true
step9
step9NA
step10
step10NA

While this has worked for us so far, I am confused about whether this is the
optimum table structure and we just haven't run into any potential problems
that may exist.

Would it be better to stick with the current structure or move to the new
model that would result in data like:

record# item# step# datecomplete stepNA
-----------------------------------------------------
1 1 1 7/1/06 false
2 1 2 7/2/06 false
3 1 3 true
4 1 4 7/4/06 false
5 1 5 true
6 1 6 7/6/06 false
7 1 8 true

If I choose to go with the new format, I will need to restructure several
queries etc.

I am relatively new to using Access to this degree of complexity and am
learning as I go. In order to prevent any growth issues, I want to make this
database as compliant with current modelling as possible

 

[Guest]

Brian,

There are many reasons for normalizing. It is best to design a program that
can change/have features added as easy as possible. One of these is to not
restrict data entry to a limited number of records. If your process decided
to add another step (11), that would require reprogramming in your table
structure and only adding an additional record in a normalized database.

Also, have you had to write reports based off your data? Instead of adding
one field for total time, you will have to add 10. Also, I am trying to
think of a way to get the information you need off your current data
structure and it will be difficult.

I think you will have to create a query with nested if/then statements to
determine the last step completed or create queries to append the data into a
temporary table so that the data for that particular query is normalized.

If it is at all possible to change the data now, I would recommend it. When
you try to "get around" the problem, the problem occurs again.

Don't know if anyone else out there has an opinion on this. Hope this helps.

Jackie

 

[Guest]

Thank you for your help. I have learned a lot about Access by developing (and
making mistakes with this particular database and have had a tremendous
amount of help from this group.

I have in the past toyed with the new table structure and ended up having to
export the data into Excel, manipulate it that way, then import it into the
new table - luckily the DB is still small enough to handle without having to
consume too much coffee.
You have given me a good way to think about my table structures and i am
beginning to make some sense of this normalization process.

thanks
Brian