ONE-ONE TABLE HEADACHE

  • Thread starter Thread starter sam1967
  • Start date Start date
S

sam1967

For security reasons i have to split a table into two tables joined by
a one-one relationship. the primary key is member_id in both tables.

i have a form which creates the member_id in the first table. how do
i set it up so that the form automatically creates the member_id in
the second table as well ?
 
You can't. The usual reason for a one-to-one relationship is because some of
the information doesn't exist for all of the records (for example, if you've
got a table of employees, you might have a separate table with a one-to-one
relationship containing additional information about the managers) You
populate the 2nd table when you have information to put there: you don't
just add a row as a place holder.
 
You can't. The usual reason for a one-to-one relationship is because some of
the information doesn't exist for all of the records (for example, if you've
got a table of employees, you might have a separate table with a one-to-one
relationship containing additional information about the managers) You
populate the 2nd table when you have information to put there: you don't
just add a row as a place holder.
Click to expand...

any suggestions on how i proceed ? the information is sensitive and
has to be put in a separate table so only a few can read it.
 
For security reasons i have to split a table into two tables joined by
a one-one relationship. the primary key is member_id in both tables.

i have a form which creates the member_id in the first table. how do
i set it up so that the form automatically creates the member_id in
the second table as well ?
Click to expand...

You can run an append query in code at some point, probably when the record
is saved.
If the person adding the new record is going to be entering data into the
sensitive side then you could add a subform with that information on it and
the key would be created.
 
An alternative is to provide an Owner Permission
query that all users may use, only returning the
fields that all users may see. Then provide full
table permission only to the group of selected
full permission users.

(david)
 
You can't. The usual reason for a one-to-one relationship is because some of
the information doesn't exist for all of the records (for example, if you've
got a table of employees, you might have a separate table with a one-to-one
relationship containing additional information about the managers) You
populate the 2nd table when you have information to put there: you don't
just add a row as a place holder.
Click to expand...

OK that is exactly what I need . Do I still create a default
relationship between the two tables ?
 
OK that is exactly what I need . Do I still create a default
relationship between the two tables ?
Click to expand...

The relationship doesn't really make much difference, unless you're going to
use it for Referential Integrity. You may as well include it, but you'll be
able to store data in either table without it.
 
The relationship doesn't really make much difference, unless you're going
to

When you create a relationship, you create an index.
When you create a field called id...... you create an index
(unless you have that feature turned off)
able to store data in either table without it.
Click to expand...

The relationship doesn't matter here, but the indexes do!
To have an updatable query (a query joining both tables,
showing all fields, allowing you to enter data), the tables
must be joined on an indexed (field).


Sam: one way or another, you probably have the indexes
you need. More likely, you should be reviewing if there
are any indexes you wish to get rid of....


(david)
 
Don't create a visible relationship. You can create an implicit
relationship whereby you'd have to link two tables togethers
using a query, rather than using the relationship. Then you can
use a query to access, modify, append delete information
without affecting the other table. Chances are that one table
is just a list of items to be referenced by the other table, and
as such you may or may not need to always join the tables.

Access handles unjoined tables very well. I have never created
an explicit relationship in my life within Access... except to
test some things out. All my relationships are created in my head
and performed/executed via an Access query.

Hope that helps.
 
Back
Top