Table to store data from subform

[Guest]

I'm still fairly new to creating databases, so I apologize ahead of time if
something like this has been asked before or if I'm completely wrong in my
thinking.

I am creating a database to be used by project managers at remote sites to
keep track of their daily activities. They will be doing data entry into a
form called Daily Report every day. The Daily Report includes information as
date, work order number, location and also includes subforms that shows the
type of equipment used and the types of personnel working that day. Once the
data entry is done for the day, the project manager will print out the
report, sign, date, and file. I need to be able is to pull up a previous
days Daily Report and print should the original print out get lost and I
believe this can be done through a query. My dilemma is how the data should
be stored in tables. For the most part, the Daily Report will contain a
unique field called Today's Date as the Daily Report is only done once a day.
When I get to the subforms, there will most likely more than 1 entry for a
particular day. For example, we may use 2 bulldozers, 3 trucks, and 2 GPS
units one day; 4 cars and 5 GPS units on another day; and, 4 cars, 1 truck,
1 GPS unit and 1 backhoe the next day. The equipment in the case above would
be tied to the particular date of the Daily Report. Is there a way for all
of this information to be included in 1 table? Any help would be greatly
appreciated.

 

[TC]

Full marks for asking for help with the table structures as the first &
most import part of your project.

The way to approach this is to describe your data needs in normal
narrative english form, without referring to forms & reports /at all/.
This is because the tables are meant to reflect the natural occurrence
& relationships of the various data elements, regardles of how those
data elements will be displayed or entered on forms & reports.

"No", there is /no way/ that you would store all, or even most, of that
information in a single table.

Tell me if these are correct:
- A workorder has one and only one location.
- A workorder has zero, one or more bits of equipment.
- A workorder has one or more personnel.
- Do you need to record the /role/ of each person? Eg. Tom is project
manager on workorder #1, but digging holes on workorder #2?)
- Do you pre-allocate specific bits of equipment o a workorder, or do
they just take what they need, when they need it?
- Can a project manager manage several workorders? f yes, can he/she do
that on a single day?

You need to get all these facts sorted before you can start designing
the table structures.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

TC--Thanks for the help. Here are some answers to your questions:

- Each workorder only has one location.
- Each work order can have any number of pieces of equipment.
- Each work order can have any number of personnel (I'm not concerned about
personnel names, just the role they work in; there can be more than 1 person
in a particular role, e.g., there may be 1 manager and 3 laborers; I need to
be able to keep track of the # of roles and # of hours worked each day)
- There is not a specific set of equipment for each workorder and different
workorders may use the same equipment
- A project manager can manage several workorders but not at the same time