One Table with Two Types of Data

[wmugrad28]

I'm working on a service database that tracks jobs on WO (work orders) done by technicians. There are two types of WO jobs. The first is a repair and the second is a PM (preventative maintenance). The only real difference between the two is that a PM consists of a group of repairs where each item in the PM is selected each time and has a special PM number (or letter) associated with that group. Repairs don't have a number.

IMPORTANT REQUESTS AND NOTES

NOTE: A piece of equipment can be considered both a PM and a repair on the same WO.

REQUEST (mandatory): The repairs will be printed on one form and each item from each PM group will be printed on a separate form. Example if a WO has two PMs on it (A and , the first with three equipment pieces (A) and the second with two (:, will be printed on two separate forms.

REQUEST (mandatory): View all the repairs and PMs for a certain customer or piece of equipment on one screen and be printable in a report.

REQUEST: Not have the piece of equipment listed twice (if in fact it is a PM and a repair on the same WO)

There are three ways I've considered dealing with these issues:

1. Create a table for repairs and a table for PMs. Then relate WO to a repairs T.O. (a one to many) and then take the same WO T.O. and relate it to a PM T.O (a one to many).

THOUGHTS

+This keeps the records separate and on one screen (in two portals)

-There is no common history between the two (in one place).

-It leaves lots of wasted space with the blanks for two portals.

-There may be the same piece of equipment listed twice on the screen both as a repair and a PM

2. Relate WO to a WO_Jobs table (one to many) and stick both PMs and repairs in WO_Jobs with a PM_Number field (if it is needed because it is a part of a PM). I would also have a Job_Type field that would be a check box with value list values of "repair" and "PM".

THOUGHTS

+There is a common history between the two (in one place).

+It eliminates wasted space by only needing one portal.

+The piece of equipment will only be listed once on the screen.

-This violates first normal form. It will be difficult (but not impossible) to separate the repairs from the PMs for each form we need to print.

3. Relate WO to a WO_Jobs table (one to many) and stick both PMs and repairs in WO_Jobs with a PM_Number field (if it is needed because it is a part of a PM). Each PM and repair would be entered separately so they would not be part of the same record.

THOUGHTS

+There is a common history between the two (in one place) and the PMs and repairs are separate.

+It eliminates wasted space by only needing one portal.

-The piece of equipment may be listed twice on the screen.

-The repair and PM forms we need to print may be a little tricky to setup.

Thank you for any help and suggestions.

Greg