Please Help with Normalization! (1 Viewer)

I have been tasked with converting an old Electronic Trouble report (ETR) flatfile database to access 97. The old flatfile had 2 basic tables in it, ETR and LOG.

The ETR table consists of the following fields:

ETR - Text
Equip - Text
Station - Text
SSCODE - text
Date_Open - Date/Time
Time_Open - Date/Time
Opened_By - Text
Status - Text
PCN - Text
Date_CLS - Date/Time
Closed_By - Text
Problem - Memo
Fix_Action - Text
Manhours - text
Parts_Used - Text
Action - Memo

The LOG table is layed out as follows:

Subject - text
Station - Text
Date_Open - Date/Time
Time_Open - Date/Time
Initials - Text
ETR_Ref - Text
Status - text (status of ETR)
PCN_SN - Text (serial num of Equip)
Manhours - text
Problem - text
Entry - Text (Log entry)


The problem I'm having is that when user opens an ETR on a piece of equipment, not only does he have to fill out all items on form that go into ETR table but at the same time generate a Log entry that can be viewed or printed out vice having to go in and search each ETR.

On the other side, users will need to be able to make a log entry without having to open a trouble report (ETR), such as replacing expendibles, this would not require a trouble report being opened.

So they both will have some of the same information but at the same time have to be independent.
Here lies my problem on how to normalize?

Any help greatly appreciated.

Jim

Remove status and Problem from the log table (duplicate entries with ETR). Then have a log form that pulls basic data from the ETR but allows them to enter log information only. You end up with multi log records to one ETR (one to many) I would convert the seperate date and time values to a single date/time field in Access. I would also create lookups for things like status, opended/closed by, that kind of stuff.

OK, I don't have a problem with most of that.
I'm still at odds what to do with the action field in ETR table and the entry field in the LOG table, these two fields are esentially the same, ie. the when making an ETR entry this action field will be the same info that goes into the entry field in the log. This would normally not be a problem except that when making a LOG entry (which may or may not have to have a corresonding ETR entry), Log entries can be made about consumables replaced on a system that don't require an ETR entry (I hope you follow this).
All ETR entries must have an entry in the LOG though.

So, I don't know what to do with these fields?


Jim

Well I would think of a log as an entry to a trouble ticket. If you have something outside of that, why not make it's own table? You could track consumables and trouble tickets independently. Better would be something like a hardware table or person table that you could associate either a trouble ticket, or a consumable entry to. But if you must go down the lines your are (some times we are stuck with what we have) then I would suggest, either leave the duplicates fields as they are, and handle the updates in your applications however you need to, or keep them at the lowest level (LOG) only, but referencing them will be more difficult as to find the current one you need the latest LOG entry for a trouble ticket. Not a big issue, but still adds complexity.