I am thinking I should start over, and establish tables for all of the data I want. My problem is that I am not sure how to store data from a source like Employee, Process, date and Hours worked. This may occur one time or a hundred times on a particular job and I need to see totals of that information, plus I need to see it listed out.
The first thing to do is turn off your computer, go into another room,
and get a pencil and a pad of paper.
Identify the "Entities" - real-life things, people, or events - of
importance to your application. An Employee is a type of entity; a
Process is another one; a Job is an entity; a workshift is yet
another.
For each type of Entity, identify its "Attributes" - atomic, distinct
chunks of information that you need to record. For an Employee you'ld
have some EmployeeID as a unique identifier (names aren't suitable as
they are neither unique nor stable); LastName, FirstName, whatever
other personal information you need to know about the employee. Do the
same for the other entities.
Then determine how the entities are related to one another. For
example, I'm guessing (not knowing your business, I can only guess)
that a Workshift would have a one-to-many relationship to Employees
and also to Jobs; so it would have fields for the EmployeeID (but NO
other employee information), who did the work; the JobID, what were
they working on; the starting time (storing both the date and the
time, e.g. #13-Sep-2005 09:15#) and the ending time, maybe some other
fields.
Hours worked would NOT be stored in any table; instead you'ld
calculate it on the fly from the start and end times. You can total up
the hours worked by an employee, or on a job, or whatever, using
Totals queries - again, it's neither necessary nor prudent to store
such derived data in tables.
Once you have the Tables (entities), with their Fields (attributes)
and Relationships set up, you can start building Forms to enter data
into the tables. Typically you would NOT have One Grand Master Form
which does everything; instead, you would have a modular approach,
with Forms and Subforms to enter data into the "one" and "many" sides
of relationships. For example, you might have a Job form with a
Workshifts subform, so that you could enter the details of a job on
the main form, and multiple rows of data about work done on that job
in the subform.
Finally, to print information out, you would create Reports based on
queries joining the relevant tables.
It's a big job, and a rather steep learning curve, but there are a lot
of us here with ropes, pitons, and advice!
John W. Vinson[MVP]
