Relationship 'One to One'

  • Thread starter Thread starter Marco Simone
  • Start date Start date
M

Marco Simone

Hi,

I have to establish relationship 'One to One' between tables, but I don't
know what fields to connect. Do I just implement link between primary key
fields?
I understood how to implement relationship many to many.
Thanks for your help,
Marco
 
Yes but I would think carefully before using 1-to-1 relationship. In
real-life applications, I can't think of things that are related by 1-to-1
relationship (except personal relationships, sometimes???).

In database modelling, 1-to-1 relationships are ocassionally used but mainly
for storage efficiency / convenience rather than real-life representation.

HTH
Van T. Dinh
MVP (Access)
 
Hi,

I have to establish relationship 'One to One' between tables, but I don't
know what fields to connect. Do I just implement link between primary key
fields?
Click to expand...

That will work - actually you can create a one to one by linking any
fields which have unique Indexes in both tables.
I understood how to implement relationship many to many.
Click to expand...

With a third table, right?

Note that one to one relationships are QUITE uncommon. If you're doing
"Subclassing", or certain types of field-level security, you need one
to one; but otherwise, it's usually easier just to put all the fields
in one table.
 
Thanks for reply,

I can think other way to do this except 'One to One' relationship. I am
making database based on a printed form, and I would like that I can enter
data as it is on a printed form.
Printed form has YEAR field, and has 4 coloumns with row titles where I
enter data. Every coloumn can consist data based on YEAR field.
So, I thought that there would be
tblYEAR
tblCOLOUMN1
tblCOLOUMN2
tblCOLOUMN3
tblCOLOUMN4
Note that COLOUMN tables contains data that are not connected to other
coloumn.
If I don't have tblYEAR, I would have to enter year in every table again.
This way I can enter year (or use option group buttons or combo box) in Form
Year and then load another form for entering data in COLOUMNS.

Am I doing right?

Thanks for your help, Marco
 
Thanks for reply,

I can think other way to do this except 'One to One' relationship. I am
making database based on a printed form, and I would like that I can enter
data as it is on a printed form.
Click to expand...

Basing your data entry form on a printed form is just being helpful to
your users.

Basing your Table structure on a printed form will GUARANTEE a bad
table design!

Data presentation and data storage are *two different issues* and must
be handled separately.
Printed form has YEAR field, and has 4 coloumns with row titles where I
enter data. Every coloumn can consist data based on YEAR field.
So, I thought that there would be
tblYEAR
tblCOLOUMN1
tblCOLOUMN2
tblCOLOUMN3
tblCOLOUMN4
Note that COLOUMN tables contains data that are not connected to other
coloumn.
Click to expand...

In that case the data in those tables is useless, since there is no
way to relate it to any other data in your database.
If I don't have tblYEAR, I would have to enter year in every table again.
This way I can enter year (or use option group buttons or combo box) in Form
Year and then load another form for entering data in COLOUMNS.

Am I doing right?
Click to expand...

I'm afraid you're not.

Design your table structure FIRST, based on the logical relationships
of your data. *Then* build a Form to make it easy for the user to
enter the data into that table. This might (at the most extreme case)
require using a non-normalized temporary table and some VBA code to
move the data into your properly structured tables... but it will be
much better in the long run.
 
Thanks John,

You are right. I could enter data in one table form coloumns on report, but
table fields maximum is 255. My four tables exceeds table limits, so I have
to put it separately.

Marco
 
Thanks John,

You are right. I could enter data in one table form coloumns on report, but
table fields maximum is 255. My four tables exceeds table limits, so I have
to put it separately.
Click to expand...

I've needed as many as sixty fields, twice in the past twenty years.

It is IMPOSSIBLE, in my mind, for a realistic normalized table to
require anywhere near 255 fields.

If you're still bound to the paper form... you'll have to *get over
it*. Your Entity, whatever it is, *does not have 500 distinct,
non-related, atomic attributes*.
 
Thanks,

I know that I could split table so that would be many tables related to
their subjects (entities), but I don't know how to connect same data (same
rows), since data are stored in tables based on date (like 15.01.2003). So
if there are more entities, there would be more tables, I suppose tables
would be in relationship 'One to one'.
Every row in tables should contain numeric data for one period (date).
Another row in tables is numeric data
for another date. Than when I enter data in tables, I want to do
calculations for example:
1. (value of field1 in tblA)/(value of field55 in tblA) on same date (row)
2. (value of field10 in tblA)-(value of field20 in tblB) on same date
(row)
3. OR different date (value of field 15 in tblA) on 15.01.2003 / (value of
field 15 in tblA) on 15.01.2002

I am at the begining, and I posted similar question above "Form based on
Query" and MacDermott answered on my question too.
I dont know how to put data together (of same date) after entered in tables
if they are splited in many tables.

Thanks for your help, Marco
 
Thanks,

I know that I could split table so that would be many tables related to
their subjects (entities), but I don't know how to connect same data (same
rows), since data are stored in tables based on date (like 15.01.2003). So
if there are more entities, there would be more tables, I suppose tables
would be in relationship 'One to one'.
Every row in tables should contain numeric data for one period (date).
Another row in tables is numeric data
for another date. Than when I enter data in tables, I want to do
calculations for example:
1. (value of field1 in tblA)/(value of field55 in tblA) on same date (row)
2. (value of field10 in tblA)-(value of field20 in tblB) on same date
(row)
3. OR different date (value of field 15 in tblA) on 15.01.2003 / (value of
field 15 in tblA) on 15.01.2002

I am at the begining, and I posted similar question above "Form based on
Query" and MacDermott answered on my question too.
I dont know how to put data together (of same date) after entered in tables
if they are splited in many tables.
Click to expand...

Neither MacDermott nor I would suggest "many tables".

How about ONE table, with three fields?

Datefield; FieldNumber; Value.

Three fields. An unlimited number of field numbers (1, 10, 55, 20,
whatever you like). A SelfJoin query joining this table to itself on
datefield would let you answer all of your same-date questions; a
self-join Cartesian query with criteria on the dates would answer your
third question:

SELECT A.Datefield, A.Value, B.Value, A.Value/B.Value AS Quotient
FROM Yourtable AS A Inner Join Yourtable AS B
ON A.Datefield = B.Datefield
WHERE A.Fieldnumber = 1 AND B.Fieldnumber = 55;

for your first example.
 
Thanks John,
How about ONE table, with three fields?
Datefield; FieldNumber; Value.
Click to expand...

There are about 400 field numbers. So when I enter data on form, I enter
first record and then I should cycle to new record after entering new
FieldNumber. Then I would have to enter same date again. I don't know if I
understood well.
for example:
Datefield: 15.01.2003
FieldNumber: 001
Value: 155

Then I should move to next record and enter same date in Datefield again.
Also FieldNumber is fix data and not needed to be entered. This is not input
I would like to have.
I would like to enter date once and then enter data in table or tables (if
more for normalisation).

Thanks for your help, Marco
 
Thanks John,


There are about 400 field numbers. So when I enter data on form, I enter
first record and then I should cycle to new record after entering new
FieldNumber. Then I would have to enter same date again. I don't know if I
understood well.
for example:
Datefield: 15.01.2003
FieldNumber: 001
Value: 155

Then I should move to next record and enter same date in Datefield again.
Also FieldNumber is fix data and not needed to be entered. This is not input
I would like to have.
I would like to enter date once and then enter data in table or tables (if
more for normalisation).
Click to expand...

Ok... so have a table of Dates, one record per date (and possibly
other fields if there is any information other than the dates and the
400 field numbers).

Use a Subform based on this three-field table to enter the values; use
the date field as the Master/Child link and it will fill in
automatically.

In the Subform's BeforeInsert event use VBA code such as:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtFieldNumber = NZ(DMax("[FieldNumber]", "[tablename]", _
"[Datefield] = #" & Format(Me![datefield], "mm/dd/yy"))) + 1
End Sub

where txtFieldNumber is the name of the textbox containing the field
number. Set that control's Tab Stop property to False; it will fill in
automatically, incrementing by one for each new record, but you can
mouse into the field to change it if (frex) you have a dataset with
missing data for some field numbers or you get ahead of yourself with
typing.
 
Back
Top