Help! - Relationships between 9 tables are broken

[john_y]

Hi,

I have been asked to update at my organisations access 2003 based database that has been in operation for about 6 years.

I find that there are no relations between more than 10 tables and wondered how reports are still being produced - can anyone help answer this please?

Best wishes,

John

 

[plog]

There's relationships there, they just aren't defined in the relationship area. That area isn't required for the database to work, its mostly a reference section. If you want to see the relationships you are going to have to open up the queries and find out for yourself.

I'm sure the last developer left comments in all the note fields of tables and has some sort of documentation you can reference. Welcome to the world of Access, where the poor reputation Access developers have is well earned.

 

[Pat Hartman]

Relationships are defined in the relationship window and are different from joins which are defined query by query. Relationships are permanent and used to enforce Referential Integrity. Joins can connect any two tables and/or queries on ANY columns of compatible data types. So you could join Employee to Company on Department to City and the query engine would attempt to find matches. It doesn't care. However, you could not define a relationship and enforce RI because that process actually validates the data and it would not find valid matches between the Department and City columns.

Sometimes, the relationships are just hidden. Open the relationship window and press the show all button to see if they reappear.

Also --- Relationships are defined on physical tables so they are ALWAYS in the BE database. If you create them in the FE, you will not be able to enforce RI and they will be informational only.

Relationships are always with the physical tables because the database engine needs to enforce them and the BE may be accessed by ANY FE or even non-Access apps such as web pages and Win forms.

 

[john_y]

Relationships are defined in the relationship window and are different from joins which are defined query by query. Relationships are permanent and used to enforce Referential Integrity. Joins can connect any two tables and/or queries on ANY columns of compatible data types. So you could join Employee to Company on Department to City and the query engine would attempt to find matches. It doesn't care. However, you could not define a relationship and enforce RI because that process actually validates the data and it would not find valid matches between the Department and City columns.

Sometimes, the relationships are just hidden. Open the relationship window and press the show all button to see if they reappear.

Also --- Relationships are defined on physical tables so they are ALWAYS in the BE database. If you create them in the FE, you will not be able to enforce RI and they will be informational only.

Relationships are always with the physical tables because the database engine needs to enforce them and the BE may be accessed by ANY FE or even non-Access apps such as web pages and Win forms.

Hi,

Thanks for your replies,

I have opened 2 or 3 queries and found tables without relations/ joins.
There are more than 40 queries so I will have to look at each one to see if I can find relations/ joins.

I am going to try and simplify the design and add some notes and create a user guide so that in the future it might be more easily understood and would not be such a huge issue.

Just one more question - when I look at the relationships view I see some fields are highlighted in bold which I understand indicates they are key fields. When I look at the table design view there are NO primary or foreign keys?

Best wishes,

John

 

[Pat Hartman]

I have opened 2 or 3 queries and found tables without relations/ joins

You cannot see relations in a join. All you see is how the coder chose to link the two tables/queries.

Are you looking in the BE? You can't do anything with relationships in the FE.

I'm not sure why you are looking at the queries unless it is to divine what the relationships are supposed to be.

If you see a query with multiple tables/queries in the design pane but NO join lines, the query is creating a Cartesian Product and should be fixed unless that is your intent. It is extremely unusual to consciously create a Cartesian Product since they produce enormous recordsets and massively duplicate data. If tbl1 has 100 rows and tbl2 has 200 rows, a Cartesian Product would produce 100 * 200 or 20,000 rows. The Where clause is probably filtering the results down plus the query might include the DISTINCT predicate or group by clause. The Where clause should give you some idea of what the join should be.

 

[john_y]

You cannot see relations in a join. All you see is how the coder chose to link the two tables/queries.

Are you looking in the BE? You can't do anything with relationships in the FE.

I'm not sure why you are looking at the queries unless it is to divine what the relationships are supposed to be.

If you see a query with multiple tables/queries in the design pane but NO join lines, the query is creating a Cartesian Product and should be fixed unless that is your intent. It is extremely unusual to consciously create a Cartesian Product since they produce enormous recordsets and massively duplicate data. If tbl1 has 100 rows and tbl2 has 200 rows, a Cartesian Product would produce 100 * 200 or 20,000 rows. The Where clause is probably filtering the results down plus the query might include the DISTINCT predicate or group by clause. The Where clause should give you some idea of what the join should be.

Hi, sorry could you explain BE and FE please - I am guessing back end and front end?.
I will take a look at sql view of the main queries to see if I can find Where and Distinct.
Also, do you think it would help if I added new primary key fields with autonumber data type to each table in terms of indexing and data integrity.

Thx,

John

 

[Pat Hartman]

1. Is your database split or are the tables in the same database as the forms, etc.?
2. Did you go to the relationship window and press the show all relationships button?
3. Changing primary keys will be traumatic. If you will be expanding the database and making enhancements, it might be worthwhile especially if the tables currently have no PK or compound PKs.
4. If the tables have no PKs and no relationships currently, I'm sure you will find lots of bad data. You will need to figure out how to deal with that.
5. The fact that the queries are probably producing Cartesian Products isn't really relevant to this discussion. You can fix them later after you fix the tables.

 

[john_y]

Hi, answers detailed with in your question

1. Is your database split or are the tables in the same database as the forms, etc.? - tables and forms in same database
2. Did you go to the relationship window and press the show all relationships button? - didn't press show all relations (in queries?)
3. Changing primary keys will be traumatic. If you will be expanding the database and making enhancements, it might be worthwhile especially if the tables currently have no PK or compound PKs. - if I create new primary key autonumber data types will the autonumbers auto populate the 800+ records
4. If the tables have no PKs and no relationships currently, I'm sure you will find lots of bad data. You will need to figure out how to deal with that. - that's true admin are finding produces odd results - have a meeting with admin staff to determine their requirements soon.
5. The fact that the queries are probably producing Cartesian Products isn't really relevant to this discussion. You can fix them later after you fix the tables.

- ok thx

Again, thx for your comments.

John
N.B is it possible to send you a private message I want to explain something to you that I can't publicly

 

[gemma-the-husky]

john

just to clarify.

your database includes a set of tables. These should have been designed in a relational manner, normalised, so that there are no data redundancies. Doing so helps you work with the database better, makes development more harmonious, and prevents errors caused when the same data is stored in more than place -but differently in different places. So given a set of tables, let's discuss what relations are.


When you create a query you can include more than one table. Generally (not always, but assume always for these purposes) you need to explain to access how thos tables are related. so that a particular field in one table corresponds to a field in a different table (maybe the same name, but not necessarily)

by doing it this way, you are able to show information from both tables in a single query. (and therefore in a single form)

as you will nknow, you can draw a line from a field in one table to a field in another, to establish the join. if two fields have the same name, access may include the join automatically. sometimes thisi s incorrect. you may have a field called ID in both tables, but they are not supposed to match, and you have to delete the jon access automatically creates.

Now these joins between tables can be preconfigured in the relationships window. they do not have to be - they just can. If a permanent relationship is established, then access will automatically include the link when you put both tables in a query which can save you time. You can always create the joins manually, though.

The final step is that the relationship can be set as having RI. relational integrity. This takes it a step further, and says that you cannot have a linked record, if you do not have a main record.

so if you have a table for customers and orders, RI means that you cannot enter an order for a non-existent customer. (which is unlikely to happen) but also prevents you inadvertently deleting a customer, if orders exist.


Relationships are useful, but are nothing to do with database design. The tables should be designed correctly in a normalised manner irrespective of whether you set relationships and RI.


hope you find this helpful

 

[john_y]

I have embedded my replies in your comments

john

just to clarify.

your database includes a set of tables. These should have been designed in a relational manner, normalised, so that there are no data redundancies. Doing so helps you work with the database better, makes development more harmonious, and prevents errors caused when the same data is stored in more than place -but differently in different places. So given a set of tables, let's discuss what relations are.[/COLOR] - the 'author' was ict subject leader and was under pressure to develop an all singing un-clearly defined database and he had a reasonable knowledge of sql


When you create a query you can include more than one table. Generally (not always, but assume always for these purposes) you need to explain to access how thos tables are related. so that a particular field in one table corresponds to a field in a different table (maybe the same name, but not necessarily)

by doing it this way, you are able to show information from both tables in a single query. (and therefore in a single form)agreed

as you will nknow, you can draw a line from a field in one table to a field in another, to establish the join. if two fields have the same name, access may include the join automatically. sometimes thisi s incorrect. you may have a field called ID in both tables, but they are not supposed to match, and you have to delete the jon access automatically creates. ok thx

Now these joins between tables can be preconfigured in the relationships window. they do not have to be - they just can. If a permanent relationship is established, then access will automatically include the link when you put both tables in a query which can save you time. You can always create the joins manually, though.simpler if it's designed this way so future developers can understand

The final step is that the relationship can be set as having RI. relational integrity. This takes it a step further, and says that you cannot have a linked record, if you do not have a main record.this is the way i am going to change the dbase

so if you have a table for customers and orders, RI means that you cannot enter an order for a non-existent customer. (which is unlikely to happen) but also prevents you inadvertently deleting a customer, if orders exist.


Relationships are useful, but are nothing to do with database design. The tables should be designed correctly in a normalised manner irrespective of whether you set relationships and RI. tables are not normalised - i think I am going to do this to 3nf


hope you find this helpful Definately thx