Clueless and frustrated

  • Thread starter Thread starter Prismaddict
  • Start date Start date
P

Prismaddict

I have a piece of paper that has 30 names going down the left side and 6
weeks worth of dates going left to right across the top. This makes a grid of
a whole bunch of boxes I'll call "mailboxes". Within each "mailbox" will be a
one of four selection showing that persons work status. What I'm trying to do
is to create a form that will identify a particular person with a particular
date and establish their status for that date. Once all the data is collected
then I will use that data in a report. I have created tables for "Employees"
with their particular info and I have a table for Status" that I can select
the 1/4 status but I can't figure out how to make columns in the dates table
to correspond with the EmployeeID or how to point to that ID from the form to
input the data into that select field. I know little of Access but this
doesn't feel like it should be rocket science. Any help would be appreciated
or if I could be pointed towards a template even better. tia
 
What you have here is a many-to-many relationship between employees and
statuses. A many-to-many relationship table is modelled by a third table
with two foreign key columns referencing the primary keys of the other two
tables, so in this case you'd have a table EmployeeStatuses say with columns
EmployeeID and Status. The date is an attribute of this relationship type,
so the table would also have a column StatusDate say.

Create relationships between Employees and EmployeeStatuses (on EmployeeID),
and between Statuses and EmployeeStatuses (on Status). Enforce referential
integrity in each case, and in the latter relationship also enforce cascade
updates. This ensures that only valid EmployeeID and Status values can
appear in EmployeeStatuses, and that if a Status value is changed in the
Statuses table it will automatically be changed in matching rows in
EmployeeStatuses. It also means that rows cannot be deleted from Employees
or Statuses if there is one or more matching rows in EmployeeStatuses.
Assuming EmployeeID is an autonumber column in the Employees table you don't
need to enforce cascade updates in that relationship as its value cannot be
changed.

For data entry you could have a form, in single form view, based on the
Employees table and within it a subform, in continuous form view, based on
the EmployeeStatuses table. The subform would be liked to the parent form on
the EmployeeID columns. The subform would have two controls, a combo box
bound to the Status column, and a text box bound to the StatusDate column.
The RowSource property of the Status combo box would be along these lines:

SELECT Status FROM Statuses ORDER BY Status;

You don't need a control bound to the EmployeeID column in the subform as
its value is automatically entered via the linking mechanism.

For a report, base it on a query which joins the Employees and
EmployeeStatuses tables on EmployeeID. Group the report first on the
employee's name, e.g. a LastName column and then on the EmployeeID and give
the latter group level a group header. Put the controls bound to the columns
from the Employees table in this header. This separates any employees who
might have the same names. Make the StatusDate the third group level. Put
the controls bound to the Status and Statusdate columns in the detail section.

Ken Sheridan
Stafford, England
 
Back
Top