relationships2

[a]

(Access 2007)

First question?

How can I change (edit) the relationships from one to many to
Because access by default choose this choice for me (one to many)

Is there something wrong I do let access 2007 choose one to many be default
or what?

What I do

I'm just drag field from table list to another table list

-------------------------

Second Question?

I see in some books in relationships window:

On the line that join 2 table (2 circle) and (1) on the line!!! is this
disappear in access 2007?

if not how to display them ?

and is this 2 circle related to (one to many relationship)?

------------------------------------

Third question:

Is this true or false (for one to many relationship)

In (Edit relation) dialog box

Table /Query (mean) >>>>>>>>>>>>>>>>>> (ONE)

Related table/Query (mean) >>>>>>>>>>>>>>>>>> (Many)

I hope you answer!!!

 

[Peter Yang[MSFT]]

Hello,

First question? How can I change (edit) the relationships from one to many to

By default, the relationship between should be one to many. You could refer
to relation database theory for more related information. Many to many
relation should be implemented via an addition intermediate table and 2 one
to many relations.

Usually we don't use one to one relation because 2 tables use the same
primary key and this may bring more join and performance cost during
querying. However, if you do need to use one to one relations, when you
edit relatioins in Access, you could select primary keys on both table and
you shall see "one to one" relation.

Second Question?

In relation windw, 1-oo on the relation line means "one to many" relation.
This is shown by default. 1 is on the table that its primary key is defined
in a relation.

Third question:

Yes. Usually it is correct if you select primary key in the "Table /Query"
and foreign key in "Related table/Query"



Hope this is helpful. Please let me know if you have any comments or
questions.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Arvin Meyer [MVP]]

(Access 2007)

First question?

How can I change (edit) the relationships from one to many to

Because access by default choose this choice for me (one to many)

Is there something wrong I do let access 2007 choose one to many be
default or what?

What I do

I'm just drag field from table list to another table list

To clarify Peter's remarks on question one:

You can force a one-to-one relationship by adding a few dummy records to
each table with the same Primary Key values. When you create the join Access
will the create the one-to-one relationship, make sure that you Cascade both
Updates and Deletes on one-to-one relationships or they will become unusable
should you try to delete a record.

 

[John W. Vinson]

(Access 2007)

First question?

How can I change (edit) the relationships from one to many to

Because access by default choose this choice for me (one to many)

Is there something wrong I do let access 2007 choose one to many be default
or what?

No. Access 2007 is *right*.

To add to what Peter and Arvin have said, you should be aware that one to one
relationships are *very* rarely used. If you're not using Subclassing or Field
Level Security (or if those terms aren't familiar), you probably don't need or
want one to one relationships! If you have two tables in a one to one
relationship, you can usually simply include all the fields in one table and
get the same result.

What I do

I'm just drag field from table list to another table list

If you DO want a one to one relationship (for a learning experience, or
because you're using subclassing, or...) then create a unique Index on the
joining field in both tables. A Primary Key is one way to create a unique
index.

-------------------------

Second Question?

I see in some books in relationships window:

On the line that join 2 table (2 circle) and (1) on the line!!! is this
disappear in access 2007?

I believe so, but I don't have 2007 installed yet.

if not how to display them ?

and is this 2 circle related to (one to many relationship)?

------------------------------------

Third question:

Is this true or false (for one to many relationship)

In (Edit relation) dialog box

Table /Query (mean) >>>>>>>>>>>>>>>>>> (ONE)

Related table/Query (mean) >>>>>>>>>>>>>>>>>> (Many)

Yes.

John W. Vinson [MVP]

 

[David W. Fenton]

To clarify Peter's remarks on question one:

You can force a one-to-one relationship by adding a few dummy
records to each table with the same Primary Key values. When you
create the join Access will the create the one-to-one
relationship, make sure that you Cascade both Updates and Deletes
on one-to-one relationships or they will become unusable should
you try to delete a record.

Huh? Is it not the case that 1:1 requires a UNIQUE index on both
sides? I don't know if it needs to be the PK, but it definitely has
to be UNIQUE. I can't see how adding data to the tables will make
any difference, as the relationship is determined by field metadata
for the joined fields, not by an examination of the data in those
fields.

 

[Arvin Meyer [MVP]]

Huh? Is it not the case that 1:1 requires a UNIQUE index on both
sides? I don't know if it needs to be the PK, but it definitely has
to be UNIQUE. I can't see how adding data to the tables will make
any difference, as the relationship is determined by field metadata
for the joined fields, not by an examination of the data in those
fields.

Maybe I didn't make myself clear enough. Primary Keys automatically maintain
uniqueness. If one field is a primary key and the other table just indexed
by the relationship, Access is smart enough to figure out that it's
one-to-one if the key data is the same as the second table's field. If both
indexes are Primary, you don't need any data, and you don't need any data if
both are created as unique indexes. (Yes/No Duplicates)

BTW, the only time I every use a unique index in a table that is not a
Primary Key, is when it's a compound index. Only sloppy data entry requires
using a unique index when it isn't the Primary Key or isn't a compound
index.

 

[Arvin Meyer [MVP]]

Indeed, one-to-one relationships are rare. The only 2 times I remember ever
seeing them are in 1 highly unnormalized database that required about 300
fields in 1 table (Access only allows 255), and in a Contacts table, where
lots of different auxiliary data was maintained on different types of
contacts. Vendors and Customers were in the same table, but each and
attributes unique to their contact type that were maintained in 2 separate
one-to-one tables.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[John W. Vinson]

Indeed, one-to-one relationships are rare. The only 2 times I remember ever
seeing them are in 1 highly unnormalized database that required about 300
fields in 1 table (Access only allows 255), and in a Contacts table, where
lots of different auxiliary data was maintained on different types of
contacts. Vendors and Customers were in the same table, but each and
attributes unique to their contact type that were maintained in 2 separate
one-to-one tables.

I have actually several one to ones in my animal shelter app: there's a table
of People which is related one to one to several tables of specific sets of
people (which can be overlapping), e.g. Reporting Party, Witness, Officer,
Suspect, Animal Owner, etc. etc. It seemed a perfectly reasonable case for
subclassing.

John W. Vinson [MVP]

 

[David W. Fenton]

Maybe I didn't make myself clear enough. Primary Keys
automatically maintain uniqueness. If one field is a primary key
and the other table just indexed by the relationship, Access is
smart enough to figure out that it's one-to-one if the key data is
the same as the second table's field. If both indexes are Primary,
you don't need any data, and you don't need any data if both are
created as unique indexes. (Yes/No Duplicates)

Without a unique index on the field, I can't see how it can do
anything but make it 1:N.

BTW, the only time I every use a unique index in a table that is
not a Primary Key, is when it's a compound index. Only sloppy data
entry requires using a unique index when it isn't the Primary Key
or isn't a compound index.

In a 1:1 relationship, is not the foreign key the PK of the
subsidiary table?

 

[David W. Fenton]

Indeed, one-to-one relationships are rare. The only 2 times I
remember ever seeing them are in 1 highly unnormalized database
that required about 300 fields in 1 table (Access only allows
255), and in a Contacts table, where lots of different auxiliary
data was maintained on different types of contacts. Vendors and
Customers were in the same table, but each and attributes unique
to their contact type that were maintained in 2 separate
one-to-one tables.

I've used them in these circumstances:

1. an app where the source data was from the UNOS national
transplant registry, and the entry into the table related to the
table into which the UNOS data was imported was the indication of
enrollment in the medical study the database was recording data for.

2. an app where a main table was for the case record for the
patient, and the related 1:1 table recorded demographics that were
collected only when the case record was closed.

3. an app where two 1:1 tables were used for two different subtypes
of records that recorded contacts with customers.

The latter didn't work very well, though in theory it was great. The
problem was that it required lots of outer joins for displaying data
about all three subtypes (main type and two subtypes) at the same
time.

I don't hesitate to use them when necessary.

I also use them a lot temporarily in apps where I need to add
features but can't get a lock on the database to change a table
structure. Temporarily, then, I add a 1:1 table with the new field
and add it to the base query that represents the table so that I can
build the new feature. This works best when you don't use your
tables directly in your SQL but use "views" that are easily edited
to incorporate whatever you want without needing to change the
objects based on it.

 

[David W. Fenton]

I have actually several one to ones in my animal shelter app:
there's a table of People which is related one to one to several
tables of specific sets of people (which can be overlapping), e.g.
Reporting Party, Witness, Officer, Suspect, Animal Owner, etc.
etc. It seemed a perfectly reasonable case for subclassing.

Do you ever need to present an omnibus list of all subtypes with
data from the side tables? If so, you end up with horrid outer
joins, which just completely kill performance.

 

[John W. Vinson]

In a 1:1 relationship, is not the foreign key the PK of the
subsidiary table?

Usually, not necessarily! Just because a field has a unique index does not
mean that it's the *only* unique index. The field is probably a candidate key,
but need not be the primary key. It might even be a nullable field (though
that would be really odd).

John W. Vinson [MVP]

 

[John W. Vinson]

Do you ever need to present an omnibus list of all subtypes with
data from the side tables? If so, you end up with horrid outer
joins, which just completely kill performance.

That's never come up but you're right, if it ever did I'd be in a quandry!

John W. Vinson [MVP]

 

[Arvin Meyer [MVP]]

I just thought of another instance where I used tables related by a 1 to 1,
but the relationships was not declared. I had a table where the costs for
each item in a homebuilder's database were entered and used to create PO's,
etc. After writing the initial database I got a request to be able to update
a cost for the future, like being able to update a cost today, that wouldn't
be effective until next year. Any PO's issued before then would have today's
cost.

So I created a related table to which records were added only if the
effective date in the newly updated record was in the future. The record
reverted to its OldValue, if that event happened. Every day the database
opened, it checked for records with the same or older date, and if it found
any, sent the user a message that there were records needing updating, and
offered to do it. To make a long story short, the table only allowed 1
update per costcode per model and subdivision, based on the PK of the
original record which was a 1 to 1 with the future record.

 

[David W. Fenton]

That's never come up but you're right, if it ever did I'd be in a
quandry!

I found that in certain select cases, a UNION ALL worked faster than
the outer joins. But for some reason, it didn't *always* work, and I
could never figure out what the difference was.