Date Table

[Bryan Hughes]

Hello,

I am trying to redesign a Client Information db.

One part of the db they currently have is a Client Activity Table.
Their Clients have 3 years of enrollment from the time they enroll. They
can be Active or Inactive.

How they currently have the Table Design is

Client Activity Table
Client ID
M00 ...M49

M00 is the Enrolling Month With Active as the Field Value. There is a Field
for each Month (M01, M02, M03, M04, etc.) ending at Field M49. Each Field
Value can be Either Active or Inactive.

I think the Table Design should be two tables.

Client Active Table
CAID
Client ID
Month
The Month field would have date entered for each month client is active, so
it can be queried out, or it could be the month number since enrollment.

Client Inactive Table
CIAID
Client ID
Month
The Month field would have date entered for each month client is inactive,
so it can be queried out, or it could be the month number since enrollment.

Then on client management form have enrollment date, closing date and a
control for client's status for the current month, entering in the
information I want to store.

Is this a better way to do this? Is the current design the best way to do
this? Is there a better way to design this table that I have not thought
of?

Please Help
-Bryan

 

[Bryan]

Thanks John,

ClientActivity
CAID
ClientID
ActivityDate Date/Time (e.g. 7/1/2003 for July 03)
Active Yes/No

I think this is a better design. Simple.

If you can reliably calculate the activity status from a start date
and an end date, then the ClientActivity table need not exist at all
you can just run a Query determining if the specified date is within
that date range. If the client can go back and forth from inactive to
active at will, then the table would be useful.

Yes, they can change back and forth during that time period.

Thank you again.

-Bryan