How to make synchronism between three forms?

[ghost]

I have a database for overtime. The database has three forms, employee form,
supervisor form and manager form. What I want to do is a workflow, for
example, if the employee inters the data for his overtime, the supervisor
form shows that data and release them. After that, the manager form shows the
data to release it. Finally, the data goes to the employee form and showing
with the releasing and ready for printing. Can the idea be done? If yes,
please clarify. Thx.

 

[Jeff Boyce]

It sounds like you are looking for someone to design the system and workflow
for you. What have you already tried?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ghost]

Hi,
I created three tables. One for the employee, one for supervisor, and one
for the manager, but I still confused, what fields are suitable and how can I
join between them and I would like to use a check box for releasing
If you have an idea, please advice?
Your help is highly appreciated.
Thx.

 

[Pat Hartman \(MVP\)]

To begin with, you need a different set of tables. You need a table to hold
employee data and you need a table to hold time reporting data. The
employee table is a self referencing table and so can link to other records
in the same table. This is the preferred structure since supervisors and
managers are also employees and you can't arbitrarialy limit the hierarchial
structure to three levels since there could easily be more.

tblEmployee:
EmployeeID (autonumber primary key)
FirstName
LastName
ReportsTo (foreign key to EmployeeID in tblEmployee)

The ReportsTo field allows each employee to report to a single individual in
the management chain. If this is not your structure you will need to use
two tables to create this relationship.

tblTimeReporting:
TimeReportingID (autonumber primary key)
EmployeeID (foreign key to EmployeeID in tblEmployee)
ReportDate
HoursWorked

This table also needs a unique index on EmployeeID and ReportDate to ensure
that there is only a single report per employee per day.

From just these two tables, you can derive all the management reporting you
need based on the limited description you have given. If it becomes
necessary to track what the time was spent on, you will need to add an
additional field to tblTimeReporting and also add that field to the unique
index because now you will need to support multiple time entries per
employee per day.

 

[ghost]

Hi,
Thanks a lot
What about the forms, how many forms should I have and which control tool is
suitable for releasing the overtime. What about if I use the check box?
Please advice?

 

[Jamie Collins]

You need a table to hold
employee data and you need a table to hold time reporting data. The
employee table is a self referencing table and so can link to other records
in the same table. This is the preferred structure since supervisors and
managers are also employees and you can't arbitrarialy limit the hierarchial
structure to three levels since there could easily be more.

tblEmployee:
EmployeeID (autonumber primary key)
FirstName
LastName
ReportsTo (foreign key to EmployeeID in tblEmployee)

The ReportsTo field allows each employee to report to a single individual in
the management chain.

Preferred by whom? "Committing spreadsheet" (thanks JohnV) is also very
common, therefore must be a *preferred*by a significant number of people
(arguably newbies with the wrong mental model) but that doesn't make it 'best
practice'. The author of the book 'Trees and Hierarchies in SQL for
Smarties', Joe Celko, prefers the nest sets model (see
http://www.dbazine.com/ofinterest/oi-articles/celko24).

This structure as posted is denormalized e.g. redundancy in your 'reports
to' column. Remember that a table either models entities (e.g. Employees) or
a relationship (e.g. OrgChart) but not both. The org chart exists independent
of the people who currently hold the positions. Is 'parking space number' an
attribute of an automobile? Of course not and the same applies to the org
chart.

If this is not your structure you will need to use
two tables to create this relationship.

OK you can normalize your earlier structure by using two tables instead of
one but you'd need to add constraints to remove INSERT/DELETE anomalies i.e.
changes that cause cycles and orphaned trees. Then there are the problems of
querying of the adjacency list model e.g. Jet SQL isn't good a recursion so
you'd need as many self joins as there are potential levels in the
hierarchy...

Jamie.

--

 

[Jamie Collins]

What I want to do is a workflow, for
example, if the employee inters the data for his overtime, the supervisor
form shows that data and release them. After that, the manager form shows the
data to release it. Finally, the data goes to the employee form and showing
with the releasing and ready for printing.

I was looking at workflows a couple of weeks ago and kept coming back to
this article:

Transition Constraints
by Joe Celko
http://www.dbazine.com/ofinterest/oi-articles/celko35

"A transition constraint says that an entity can be updated only in certain
ways. These constraints are often modeled as a state transition diagram.
There is an initial state, flow lines that show what are the next legal
states, and one or more termination states... A declarative way to enforce
Transition Constraints is put the state transitions into a table and then
reference the legal transitions. This requires that the target table have
both the previous, and the current, state in two columns..."

The example used is a bit simplified (e.g. a single transition_time
timestamp rather than periods for each state) but hopefully it should give
you some ideas.

Jamie.

--