Query joins without a predefined relationship

[ccoindre]

After wiping out data in tables through joins in select queries and using Access 2000 help, I figured out it was a cascade delete. The help info said to not check the "Delete cascaded records" checkbox for the relationship, but, opening the join properties in the query design view showed no such checkbox, only the left/right column data and 3 radio box options for the type of join: Left to Right, Right to Left, or Both. Testing showed that the only way to get to an edit screen which sets up referential integrity and allows checking/unchecking of cascade deletes is through the relationships view. Apparently, the relationship must be setup first. Then when a query is designed and tables are added, the join lines are automatically inserted. Apparently, not creating a relationship before designing a query forces an automatic cascade delete situation?? I have set up a significantly sized database by defining 'ad hoc' queries as required, without predefining table relationships. Can someone shed some light on the requirement to setup relationships first and what are the problems that may occur if you don't? Usually, I delete records using a specific query which so far has nor caused any problems (probably because there is no table relationship defined??).
Also, I have noticed that most examples show a "1" or "infinity" symbol at the ends of the join lines, mine have none, just dots and arrows, I don't know why.

Thanks in advance
ccoindre

 

[scriverb]

You are correct that you have to first set up the relationships for them to be passed to the query design window. And, Yes, only in the Relationships windows can you setup the relationship to be a one to one or one-to-many. That's why the 1 and infinity sign. You are telling access the correct relationship between two tables. ACCESS will then hold you to that situation.

Now, you also said: Apparently, not creating a relationship before designing a query forces an automatic cascade delete situation?? This is not true. If you do not create any relationships then you are relying upon your programming skills and knowledge of the table and record relationships to keep everything straight. Nothing will happen automatically. You must write queries and code to design the relationships and happenings of the tables and their records.

I personally, like to not use the relationships in the relationship window. There are relationships but they are in the design of the database which I am controlling.

In the query design window the links between tables can only be inner, left, and right joins. There are no 1's or infinity signs here when you do not by design in the relationship window create those relationships in the beginning.

I hope this sheds a little light upon your confusion. It will get better as you work with your tables and design your queries. You will then start to become comfortable with the relationship process.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[ccoindre]

Thanks for clarifying how the relationships work. The query in question is straightforward with a main table with several indices and a join to another table to get the 'real' data. In a subform, with a child index that restricts the records displayed, I deleted a record, via the query, in the main table and it also deleted the corresponding record in the joined table. That is why I assumed the cascading delete was in effect. I will do some more testing and resolve my problem.

Again, thanks a lot. Those questions have been nagging me for some time.

ccoindre