Storing data in table form vs linear record form

[Guest]

I am trying to set up a housing schedule. I would like to set up a table
with the following structure for each house.

House#1

Task Start Date End Date Duration
Task 1 1/1/05 1/2/05 1
Task 2 1/3/05 1/5/05 2
Task 3 .... ...... ..
Task 4
....
Task 60 ..... ...... ..

This table would have approximately 40 - 60 tasks so it is rather large.
For each task I have to store a task name, start date, end date, and
duration. Is there a simple way to set this up? Could you point me to an
example? Every example I have seen has a simple table design with 1-10
columns for each record. This table represents 4 * 60 = 240 items for each
record. I could break down each task to a record with 4 columns and add a
column for the house identifier, but this makes data entry and forms
problematic. Any suggestions?

 

[John Vinson]

I am trying to set up a housing schedule. I would like to set up a table
with the following structure for each house.

House#1

Task Start Date End Date Duration
Task 1 1/1/05 1/2/05 1
Task 2 1/3/05 1/5/05 2
Task 3 .... ...... ..
Task 4
...
Task 60 ..... ...... ..

This table would have approximately 40 - 60 tasks so it is rather large.
For each task I have to store a task name, start date, end date, and
duration. Is there a simple way to set this up? Could you point me to an
example? Every example I have seen has a simple table design with 1-10
columns for each record. This table represents 4 * 60 = 240 items for each
record. I could break down each task to a record with 4 columns and add a
column for the house identifier, but this makes data entry and forms
problematic. Any suggestions?

I'd suggest a couple of things. The Duration field should not exist in
your table, as it can apparently be calculated on the fly from the
Start Date and End Date.

More importantly, what it seems that you have is a Many to Many
relationship from Houses to Tasks. This requires a third table:

Houses
HouseNumber
<address, owner name, any other info about the house itself>

Tasks
TaskNo
TaskName
<perhaps other fields such as typical duration>

TaskSchedule
HouseNumber <<< link to Houses
TaskNo <<< link to Tasks
StartDate
EndDate

The data entry for this can be made very straightforward; you would
perhaps have a Form based on the Houses table, with a continuous
Subform based on TaskSchedule. On this subform you would have a combo
box to select the Task; a textbox for startdate and enddate; and a
Duration textbox with a control source

=DateDiff("d", [StartDate], [EndDate])

I'm not sure how the dates and durations interact; if a task is
supposed to always take n days, then you could even use the
AfterUpdate event of the StartDate textbox to automatically populate
the scheduled end date.

John W. Vinson[MVP]