Re: One to One relationship? (Access 2000)

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: John Vinson (jvinson_at_STOP_SPAM.WysardOfInfo.com)
Date: 11/16/04 

Date: Tue, 16 Nov 2004 11:32:32 -0700

On Tue, 16 Nov 2004 01:54:02 -0800, "Alastair"
<Alastair@discussions.microsoft.com> wrote:

>Hi guys, thanks for responding,
>
>I work for an engineering contractor and we design and construct oil & gas
>refinaries, chemical plants e.t.c. This obviously involves specifying various
>pieces of equipment; this is done using 'datasheets'. These are currently
>generated in Excel, but we’re thinking about using Access instead. I am
>looking at centrifugal pump datasheets for this trial.

Just bear in mind that Access IS NOT A SPREAD***. Excel is a
spread*** program, a good one. Access is a relational database.
*They are different*; applying spread*** design and logic to a
database will pretty much guarantee a *bad design*.

>The conditions that the pump will operate at (temp, pressure, flow etc) and
>the requirements of the pump are specified by a process engineer (me) in a
>‘process data***’, this is then handed to a mechanical engineer who designs
>a pump to meet these constraints and produces a ‘mechanical data***’ to
>give to the procurement dept so the can find one for us.

Again... don't focus too much at first on the final output. Think
first about the Entities - real-life things, people or events -
relevant to your application; and their Attributes (unitary types of
information about each Entity).

>For each pump there is one process datasheet and four mechanical datasheets,
>hence the five forms in my database, as each set of five datasheets comprises
>one record I would have liked the data to be stored in one table, but that’s
>not possible. In a given project there will be many pumps each with an
>associated set of unique datasheets.

So a Pump is an entity; a Data*** is (it appears) an entity. Without
knowing more about the details it's hard to suggest a viable table
structure.

>With regard to the number of fields, this really is unavoidable as there is
>so much information to specify, for example if the design changes (as it
>invariably does) it needs to be revised and down the side of each of the
>forms on each line there is a box to indicate in which revision the data on
>that line was specifed, as there are 70 lines on each form this means 70
>fields (per form) before you even start with the actual engineering. These
>need to be separate fields as they each refer to different lines even though
>most of the revision boxes will display the same number.

Wrong. You're approching this from the final product, not from the
logical structure of the data!

If you have many revisions, you DO NOT NEED MANY FIELDS for each
revision. You need *two tables* - a table of Pumps related one-to-many
to a table of Revisions. The final report will not be generated from
one table, or probably even from two - it will be based on a Query
pulling together data from several tables. The 70 lines will not come
from 70 fields - they will come from *one* field in 70 records in this
related table.

>I agree that autonumber is the wrong data type for the I.D field; I’ll look
>into changing that today, I liked the sound of the subforms, although I think
>I recall reading the you could only use two layers of subforms, is there a
>limit to the number of subforms on one form?

Seven deep, in A2000 and later.

>It may be the case that Access is simply the wrong tool for what I’m trying
>to do,

Oh, I think it's an *excellent* tool for what you're trying to
accomplish - much better than Excel in fact - but you'll need to climb
up the rather steep learning curve, and work WITH the program instead
of treating it as a big spread***!

                  John W. Vinson[MVP]
             Join the online Access Chats
        Tuesday 11am EDT - Thursday 3:30pm EDT
      http://community.compuserve.com/msdevapps

Quantcast