1 to 1 relationship

[Guest]

Hi,

In Access 2003, suppose I have 2 tables joined by a 1-1 relationship, I have
a form where users can add a new record into one of these tables, is there a
way of cascading any new records into the other related table. Both tables
share the same primary key.

Thanks

 

[Jeff Boyce]

Ian

It is somewhat unusual to need a 1-1 relationship. Could you describe a bit
more about what kinds of data you have stored in two (related) tables?

By the way, if the "same primary key" in both tables is one you
generate/enter, skip the next portion ... otherwise, if you've used an
Access Autonumber for both these primary keys, the two table are definitely
NOT related. Access creates Autonumbers independently in each table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hi Jeff,

I am trying to put together a client database, I have created several tables
all linked to a master client table, the master contains the unique client
code and the client name. Each of the other tables are linked to the client
master list with 1 to 1 relationships. I decided to go with several tables
due to the amount of fields I would be requiring, it was an attempt to keep
things simple.
I have a form that creates a new record within the client master, I would
like this record to appear within each of the other tables so that users can
select any new records from their own individual forms to enter their
departmental data relating to that new client.

I hope this helps explain what I am trying to do.

Thanks
Ian.

 

[Joseph Meehan]

Hi Jeff,

I am trying to put together a client database, I have created several
tables all linked to a master client table, the master contains the
unique client code and the client name. Each of the other tables are
linked to the client master list with 1 to 1 relationships. I decided
to go with several tables due to the amount of fields I would be
requiring, it was an attempt to keep things simple.

That sounds like a normalization problem. Why do you need a lot of
fields? BTW Access allows over 200 fields in a table.

I have a form that creates a new record within the client master, I
would like this record to appear within each of the other tables so
that users can select any new records from their own individual forms
to enter their departmental data relating to that new client.

This sounds more like it should be a one to many relationship. The
parent table with child tables for the additional information. In include a
filed in the child table to identify departments or users as you need.

 

[Jeff Boyce]

Ian

I'll reiterate what Joseph mentioned...

If you are using "several tables all linked to a master client table ... due
to the amount of fields [required]", there is a very good chance your table
structure is not well-normalized -- i.e., is more like a spreadsheet than a
relational database.

If you are willing to pursue this further, please describe (some of) the
fields you've decided to put in these other tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[strive4peace]

Hi Ian,

and I will reiterate again what Jeff and Joseph said ...

think of it this way -- for every noun you can visualize, you will have
a table. Fields are like adjectives that describe that noun.

I suspect that you created other tables by copying your master table and
changing it... that means you copied the UNIQUE index too -- and it does
not apply to any other table (unless it truly is a 1:1 relationship,
which I doubt)

when you are in Table Design view, always turn on the Indexes window
(lightening bolt icon or View, Indexes from the menu) and keep it
showing -- move it to the right of the screen, but keep it on and make
sure the Indexes that Access creates are actually ones you want before
you save the table.

Once the Indexes window is showing, click on an Index in the Indexes
Window and note the properties in the lower pane ... change a Yes answer
to No by double-clicking on it (for instance, in the Unique property --
this means you can only have ONE of each value)

you have to keep tabs on the Indexes... Access creates indexes
automatially if "ID" or "Code" or "Num", etc is in the fieldname unless
you change the defaults -- and there is a limit to the number of indexes
you can have in a database -- so delete the ones you don't need.

Jeff asked that you devulge your table structure... may I second that?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Ian

I'll reiterate what Joseph mentioned...

If you are using "several tables all linked to a master client table ... due
to the amount of fields [required]", there is a very good chance your table
structure is not well-normalized -- i.e., is more like a spreadsheet than a
relational database.

If you are willing to pursue this further, please describe (some of) the
fields you've decided to put in these other tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,

I am trying to put together a client database, I have created several
tables
all linked to a master client table, the master contains the unique
client
code and the client name. Each of the other tables are linked to the
client
master list with 1 to 1 relationships. I decided to go with several tables
due to the amount of fields I would be requiring, it was an attempt to
keep
things simple.
I have a form that creates a new record within the client master, I would
like this record to appear within each of the other tables so that users
can
select any new records from their own individual forms to enter their
departmental data relating to that new client.

I hope this helps explain what I am trying to do.

Thanks
Ian.

 

[Jeff Boyce]

Ian

I'm with Tom ... if you don't let us know what kind of data you are
storing in the fields, we can't offer meaningful advice about which fields
belong in which tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP

NOTE: a table with 100 fields is quite uncommon in a well-normalized
relational database. ... and so are 1-to-1 relationships. Unless you
provide more specific descriptions of the data you are storing, it will be
tough to offer more specific suggestions...

Hi Jeff/Joseph/Cyrstal,

I have resolved my issue for now by merging all 1 to 1 tables into 1 huge
table, I am already using over a hundred fields, for this reason I am
concerned that my resolution may only be temporary.

I was using a table structure where I had one 'master table' listing all
of
my company's clients by Code (unique ref) and client name, I then had a
separate table for each of these clients finanials for years 2001 to 2006,
each of these tables used the same client code as a primary key and had
approx 20 numeric fields.

Because this is a new database, I was using exactly the same list of
clients
for each of these tables, hence the 1 to 1 relationships, my problem arose
when I tried to use a form to generate a new client into my master list,
referential integrity prohibited me from generating one as it didn't
appear
in the other tables, I thought there may be a way of cascading a new
client
across all tables.

So, currently this is no longer a problem, but in a couple of years time
when my huge table runs out of fields I will need to store data into
separate
tables for the same list of clients, what relationship will these new
tables
have to my current one?

Sorry for the essay...

Kind Regards

Ian.

Hi Ian,

and I will reiterate again what Jeff and Joseph said ...

think of it this way -- for every noun you can visualize, you will have
a table. Fields are like adjectives that describe that noun.

I suspect that you created other tables by copying your master table and
changing it... that means you copied the UNIQUE index too -- and it does
not apply to any other table (unless it truly is a 1:1 relationship,
which I doubt)

when you are in Table Design view, always turn on the Indexes window
(lightening bolt icon or View, Indexes from the menu) and keep it
showing -- move it to the right of the screen, but keep it on and make
sure the Indexes that Access creates are actually ones you want before
you save the table.

Once the Indexes window is showing, click on an Index in the Indexes
Window and note the properties in the lower pane ... change a Yes answer
to No by double-clicking on it (for instance, in the Unique property --
this means you can only have ONE of each value)

you have to keep tabs on the Indexes... Access creates indexes
automatially if "ID" or "Code" or "Num", etc is in the fieldname unless
you change the defaults -- and there is a limit to the number of indexes
you can have in a database -- so delete the ones you don't need.

Jeff asked that you devulge your table structure... may I second that?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Ian

I'll reiterate what Joseph mentioned...

If you are using "several tables all linked to a master client table
... due
to the amount of fields [required]", there is a very good chance your
table
structure is not well-normalized -- i.e., is more like a spreadsheet
than a
relational database.

If you are willing to pursue this further, please describe (some of)
the
fields you've decided to put in these other tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,

I am trying to put together a client database, I have created several
tables
all linked to a master client table, the master contains the unique
client
code and the client name. Each of the other tables are linked to the
client
master list with 1 to 1 relationships. I decided to go with several
tables
due to the amount of fields I would be requiring, it was an attempt to
keep
things simple.
I have a form that creates a new record within the client master, I
would
like this record to appear within each of the other tables so that
users
can
select any new records from their own individual forms to enter their
departmental data relating to that new client.

I hope this helps explain what I am trying to do.

Thanks
Ian.

:

Ian

It is somewhat unusual to need a 1-1 relationship. Could you
describe a
bit
more about what kinds of data you have stored in two (related)
tables?

By the way, if the "same primary key" in both tables is one you
generate/enter, skip the next portion ... otherwise, if you've used
an
Access Autonumber for both these primary keys, the two table are
definitely
NOT related. Access creates Autonumbers independently in each table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

In Access 2003, suppose I have 2 tables joined by a 1-1
relationship, I
have
a form where users can add a new record into one of these tables, is
there
a
way of cascading any new records into the other related table. Both
tables
share the same primary key.

Thanks