One Table Or Two?

[Bluezman]

This question may not only apply to the Tables forum, but I'm hoping to get a start here. I'm having trouble deciding whether or not I need one table or two (or perhaps more?) for a certain kind of data.

In my member database, I've got a table called tblHistory with the following fields:

hisID - (one-to-many with memID in tblMembers)
hisDate - date field, mm/dd/yyyy
hisChapterNum - number, integer
hisType - combo box with 14 possible items.

Using the contents of the hisType field, I want to calculate the number of days between events to find a total time in service, however not every hisType will be applicable to the calculation.

For example:

1234,12/01/1985,400,INTITATED
1234,12/31/2000,400,SUSPENDED
1234,03/31/2000,400,RESTORED
1234,06/01/2001,324,DUAL WITH
1234,09/15/2002,324,DUAL WITHDRAWN
1234,12/31/2002,400,DECEASED

The DUAL WITH and DUAL WITHDRAWN records are necessary and need to be in a table, but not really used to calculate any time in service.

Should I have a seperate table for IN events and one for OUT events and perhaps a third for OTHER events?

Thanks for any help that might be given

Bluez

 

[cogent1]

I would say you only need two tables.

Your events should be in a separate Events lookup table, which is joined to your member table in a one-to many relationship using an EventID field.

You can then use whichever dates you want to calculate based on the eventID.