Relationships Window

[Dean]

I have been developing in Access since Access97. I have
never been able to get the 1 and the infinity symbol to
appear when I build relationships between tables. Is
there a way to turn this feature on/off?

 

[Glen SIdelnikov]

It is automatic and based on the key and indexing policies you are
selecting.

 

[Albert D. Kallal]

The infinity symbol only appears when you enforce referential integrity.

I mean, if you have a table with how the person paid (visa, master card
etc). then that is really only a look up.

Take a look at the following screen shot

http://www.attcanada.net/~kallal.msn/Articles/PickSql/Appendex2.html

You can see at the top there is a table called tblPayments

The "how paid" field is not really much of a relation. I mean, I can add new
types of payments, or I can even delete records from tblPayments, and
NOTHING happens to the how paid table. So, no RI is enforced, and thus
looking at that diagram, you see a simple arrow. It is just a simple lookup,
and not a enforced relaton.

Now, take a close look at the table called tblBroup (booking group). Note
the line that is drawn to the tblPayments. It is has a 1 to many, and BOTH
THE infinity sign AND THE RIGHT arrow appear. This is tells me that you DO
NOT have to have records in tblPyamens. (they are optional, but RI is
enforced). After all, if you book a group of people, they may not have yet
paid you any money. Thus, this is a left join. if you look close, you will
notice that about 90% of the relations are left joins. this makes sense,
since I want to easily be able to print out people who are booked, but if my
relation says that they also MUST have made payments, then they would NOT
appear. (actually, this also means that your queries in the query builder
will also default to the correct type of join that the designer intended).

Now, take a close look a the join line from tblBooking to tblGroup
tblooking id --> to tblBroup Booking_id

Note how there is a one to many and the infinity sign. BUT NOTE HOW THERE is
no right "arrow head". this is VERY important, because this means as the
designer I did NOT use a left join, and therefore when you add a booking,
you MUST also add a tblGroup record (JET does not enforce this, but you can
see that the desinger of applction DID intented this). The fact of the arrow
head existing, or not tells you a great deal about the assumptions the
orignal developres made in the system.

So, the infinity sign only appears when you enforce RI. And if you are using
a linked table, you do all this stuff in the back end table

And, if no right arrow head appears, then you really need to "add" the child
record. Often, designers pay no attention to this small detail, and thus
they miss out a huge ability of the ER diagram to become a fabulous working
design document.

 

[Dean]

Your response does not resolve this problem. Each of my
tables have keys and indexes set. In the relationship
dialog window, when creating relationships between
tables, the relationship type says one to many but the 1
and the infinity symbol do not appear in the gui
schematic.
When establishing queries, the symbols are not present
between tables either. There must be a way to turn this
feature on or off.
- - - Dean

 

[Glen SIdelnikov]

Dean,
my response is not intended to solve the problem. The only person who can do
it is you. I am answering your question.
In my response I am assuming the certain level of experience and try to be
as short as possible.
Sorry for misunderstanding.
If the option of the referential integrity is not appearing in your
relationship window - your indexes are not set for it.
If you can not enforce the referential integrity, you will not see the signs
you are asking for.

 

[Dean]

Albert,
You are so correct. I discovered this just prior to
reading your response. I have never used the
Relationships design window before and have never set
referential integretity. It is amazing that I have
developed over two dozen robust working Access database
systems used by multiple users at multiple locations and
never used this feature. I have always enforced
referential integety in code. I guess this old dog needs
to learn a new trick. It just goes to show that we all
can learn from each other.
Thanks for you timely response.
- - -Dean

 

[Dean]

I was not critisizing your response, only inferring that
what you were recommending was not a solution to the
problem. The problems was that I was NOT enforcing
referential integrety. I have over seven years of
programming in Microsoft Access for business
applications. These systems are used by hundreds of
people at multiple locations. I have never used the
relationship window when designing a system and have
never enforced referential integrity using Access wizards.
I enforce data referential integrity through code.
This may be the hard way but I, as the developer, have
always had full control on record inserts, updates, and
deletions. I am not saying that the Access relationships
and referential integrity wizard is a bad thing, I just
have not used that feature before. There are alot of
tools in the Access database but not everyone uses all
the tools. Thanks for your response.
- - -Dean

 

[Tony Toews]

You are so correct. I discovered this just prior to
reading your response. I have never used the
Relationships design window before and have never set
referential integretity. It is amazing that I have
developed over two dozen robust working Access database
systems used by multiple users at multiple locations and
never used this feature. I have always enforced
referential integety in code. I guess this old dog needs
to learn a new trick. It just goes to show that we all
can learn from each other.

I found this was particularly useful when I first started out with
Access and using subforms and such. Ensure that I didn't screw things
up.

Now, occasionally, it helps when I do something stupid with action
queries in VBA code. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm