One to One relationship? (Access 2000)

[Alastair]

Hi,

I am creating a database for an engineering application to store equipment
datasheets in a database, it currently consists of 7 tables all linked by
one-to-one relationships (all with the same autonumber primary key: 'ID').

Ideally i would have liked one big table but there were too many fields for
one table so I thought the one-to-one relationships would simulate this.

The fields from these tables are displayed on 5 different forms each
containing the 'ID' field, now when I enter data into one of the forms (and
therefore one of the tables) for a given field this creates a record in the
table containing that field and generates a value for 'ID' for that record,
but I would like Access to generate a new record in every table (and
therefore all the forms) for this 'ID' value.

Is this possible? I'm sure there must be a more effective way of doing this
but I am relatively new to access and I'm still getting to grips with it.

Thanks a lot guys,

Alastair

p.s. I previously posted this message in the forms DG - I know thats bad
'netiquette' but I didn't get any responses and i'm still stuck

 

[John Vinson]

Hi,

I am creating a database for an engineering application to store equipment
datasheets in a database, it currently consists of 7 tables all linked by
one-to-one relationships (all with the same autonumber primary key: 'ID').

This is a VERY BAD idea. First off, one to one relationships are VERY
rare. If you're familiar with the terms "Subclassing" or "table-based
field level security" then you may be using one to one relationships
correctly, but I doubt that you are.

More importantly - you should never, EVER link *to* an Autonumber.
Autonumbers are *not* controllable; they will have gaps. You have
absolutely no way to ensure that the ID in the "child" table has any
relationship whatsoever to the ID in the main table.

Ideally i would have liked one big table but there were too many fields for
one table so I thought the one-to-one relationships would simulate this.

I have needed as many as 60 fields in a table... twice in the past
twenty years.

I VERY strongly suspect that you have "committed spreadsheet",
embedding a one to many relationship in each record. Could you post
some examples of fieldnames? I expect that *two* tables, in a one to
many relationship, will be a much better design.

The fields from these tables are displayed on 5 different forms each
containing the 'ID' field, now when I enter data into one of the forms (and
therefore one of the tables) for a given field this creates a record in the
table containing that field and generates a value for 'ID' for that record,
but I would like Access to generate a new record in every table (and
therefore all the forms) for this 'ID' value.

If you MUST use this design - and again I'm sure it's not ideal! -
then you should use a Long Integer ID in each Child table (not an
Autonumber) and use a Form for the main table, with a Subform for each
child table.

Please post a description of the data you're storing. I'm certain that
a normalized solution which will not require humungously wide tables
or one-to-one relationships is possible.

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

 

[Brett Collings [429338]]

Comments in-line with your questions

Hi,

I am creating a database for an engineering application to store equipment
datasheets in a database, it currently consists of 7 tables all linked by
one-to-one relationships (all with the same autonumber primary key: 'ID').

Ideally i would have liked one big table but there were too many fields for
one table so I thought the one-to-one relationships would simulate this.

Alastair, the logic there sounds ok but then I have to query the large
number of fields. This leads me to a supposition that maybe you have
relational data in your datasheets that could be in related tables.

The fields from these tables are displayed on 5 different forms each
containing the 'ID' field, now when I enter data into one of the forms (and
therefore one of the tables) for a given field this creates a record in the
table containing that field and generates a value for 'ID' for that record,
but I would like Access to generate a new record in every table (and
therefore all the forms) for this 'ID' value.

And this is the problem. By duplicating ID numbers you are making
referrential integrity very difficult to maintain and you are not
conforming to Third Normal Form data structure

Is this possible? I'm sure there must be a more effective way of doing this
but I am relatively new to access and I'm still getting to grips with it.

I'd like to get a better insight into your structure before we can
help you unravel this. Can you describe your data for us and
particularly what the many fields are or do


Cheers,
Brett

 

[Alastair]

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.

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 datasheet’, this is then handed to a mechanical engineer who designs
a pump to meet these constraints and produces a ‘mechanical datasheet’ to
give to the procurement dept so the can find one for us.

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.

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.

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?

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

Thanks again for the help.

Alastair (London - U.K)

 

[John Vinson]

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 SPREADSHEET. Excel is a
spreadsheet program, a good one. Access is a relational database.
*They are different*; applying spreadsheet 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 datasheet’, this is then handed to a mechanical engineer who designs
a pump to meet these constraints and produces a ‘mechanical datasheet’ 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 Datasheet 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 spreadsheet!

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