Dates should be in the table to which they apply. If the overall project has
a Start Date and an End Date, you would have fields for those in the Project
table, as they are attributes of the project. Then you would have date
fields (Start and End, I expect) as needed for the Phase or Step (foundation,
etc.). You may also have inspection dates.
Steve did indeed suggest a table hierarchy you could use, although I wouldn't
have gone so far as to insist it is all you need. It seems you may want a
description of the inspection taking place, for instance. Several types of
jobs such as plumbing or electrical (and others, no doubt) may have at least
a rough-in inspection and a finish inspection.
In any case, if there is ever a chance of more than one phase, inspection, or
anything else, use a separate table. Even if you are "sure" there will never
be more than two inspections (for a phase?project?), use a separate table for
inspections. I have gone to considerable effort to redo some of my early
projects built based on certainties that were anything but.
Which leads to another point: Are inspections for phases only, or are there
inspections related to the overall project too?
Steve did not explain much about his structure (I have a theory about that,
but we'll see). The convention he used, which is a reasonable one, is to
name the primary key field the same as the table, and the linking field the
same as the primary key field of the table to which it is related. So when
you see this:
TblProjectPhase
ProjectPhaseID
ProjectID
ProjectPhaseID is the primary key field of tblProjectPhase. ProjectID is on
the many side of a one-to-many relationship with ProjectID in tblProject. It
is one-to-many because one project may have many phases. One Phase may have
many (more than one) inspection, so a similar structure applies to
tblInspection.
Build the tables, create the relationships using Tools >> Relationships, then
you can build the queries, and the forms and other elements of the interface.
If the design is done correctly, you can pull any data you want. Don't worry
too much about that before the basic structure is built.
It is of a little concern that you spoke of "some check boxes". Be careful
not to store preferences in check boxes. It can be a nuisance later on.
There is a good discussion of this point here:
http://allenbrowne.com/casu-23.html
Thanks guys. We have one project, many dates. We normally only do one
inspection, two at the most. We have 8 inspectors so I could have a little
table for them. As to dates, we have dates for steps of the projects and
completion date, inspection date. I question how to handle dates that must be
stored. The dates are tied to "A" project. IE, project number 1011-1100-323
has, say 8 dates. from drawings through completion. I am thinking I have to
have a one-to-many, one project, many dates. But then I also have to deal
with District, school, project description. I am just a little confused on
the dates, do they need a primary and foreign, or what.
[quoted text clipped - 30 lines]
