Select query doesn't show all records after join

[doma23]

I have 114.195 records in tblRevenues, however when I try to do the query (in order to replace various foreign keys to actual names) after I add the tblCategories, my count of records goes to 113.850, and more, after I add tblProductLines it goes down to 102.488 records.

These both tables are linked to another tblRevenues and to one other table, and that is where the problem is. When I delete connection between tblCategories and tblClients, and between tblProductLines and tblProducts that works and I get full count of records in tblRevenues, but these are natural relationships, so I shouldn't be deleting anything in order to get full count of records in tblRevenues.

Pictures should make it clearer.
Query:


Relationships:


Also, would appreciate if somebody experienced could comment on set up Relationships.

Thanks!

[jimatqsi]

Doma23, your images are just blank squares, they don't show anything in my browser.

What column/field name are you joining the tables on? Apparently some values in this column on one side of the join don't exist in the table on the other side of the join. Try this: double-click on the line that joins the two tables and you'll get a little popup window to change some join parameters. Change first to include all of the records from one table and only the matching records from the other table. Note the results and then do the process again, but switch which table you want all of the records from.

For instance, maybe each record in tblProducts has a ProductLine column; every value in that column should exist in the tblProductLines . But if some are missing you will not see all of the rows in both tables. Some categories are missing from either tblRevenues or tblCategories (if you are joining on category).

Does that help,

Jim

[Rabbit]

Your query shouldn't join the category id to both tables like that. In fact, category id shouldn't even be in both tables. It should only be in one. What you're doing is duplicating data and that makes your structure denormalized. This makes querying more difficult.

Anyways, the reason you're not getting all your records is because there exists records in your client table where the combination of client id and category id does not exist in your revenue table.

[doma23]

@jimatqsi, I don't know why you don't see the images. They should be there. I checked from another computer.
I've tried various join types, that doesn't seem to be the issue.
Categories are not missing when joined "stand alone", I've checked that.
But like Rabbit noted, it's because some combinations of ClientID and CategoryID in tblClients don't exist in tblRevenues.

@rabbit
CategoryID and ProductLineID are duplicated on purpose, because I need to preserve these information, as they will change over time.*

I think you are right in that some combinations of ClientID and CategoryID in tblClients don't exist in tblRevenues.
It's because in tblClients I have the present situation with the actual CategoryID for each Client.
But in tblRevenues there are historical Values of Category ID preserved, as I need to know how each Client changed Categories over various Periods.
Both tblRevenues and tblClients are connected to tblCategories in order to enforce Referential Integrity.

So, still don't know how to solve this, because I would like to use natural relationships as shown in relationship window.
I don't want to delete in query certain relationships in order for it to work, I mean, it doesn't seem right.

*There is a good article about database normalization that I've read yesterday from which I've learned when data should be denormalized.

[Rabbit]

In that case, all you need to do is bring in the category table twice. One to join to revenue and one to join to client.

[doma23]

Where do I bring in the Category table twice, in relationship window or in query?
If I bring it twice in relationship window, that doesn't change anything, because when I bring it after that in the query, it only brings one instance and it automatically makes double connection, one to Revenues and other to Clients, it doesn't take into account that there are two instances of Categories table used in Relationship window.

Therefore, I still need to manually change it in order for it to work.
And that's what I'm trying to avoid, because I don't want to think about what relationships do I need to delete in order to see all the relevant records, in every query that I make.

EDIT:
OK, it seems that the problem is in Relationship window. After I deleted the current connection between Category and Revenues, and added one more instance of Category and connected it to Revenues, it doesn't save it correctly.
After I save and close the window, and then open it again, the other Category instance is there but not connected as it should be. But it reconnects the original Category again to Revenues.
Is this Access bug? I'm using Access 2002. (Oh Bill...)

BTW. Then I guess I would need three instances, one to Clients, one to Revenues and the third to RWA.

[Rabbit]

In the query. What you do in the relationship does not affect what you can or must do in query design.

[doma23]

Then why would I need two instances in query when I can just delete the connection between Categories and Clients?

My whole point was that I thought it's possible to make Relationships in that way that it wouldn't interfere with queries. According to what you are saying that's not possible.

When I bring in the table in the query, it automatically connects the fields based on Relationship window.

Please note here, that if I would be able to separately set up and save three instances in Relationship view, one that would connect to Revenues, one to Clients and third to RWA, and if Access wouldn't delete them and automatically use just the first one to connect to all three tables, I wouldn't need to adjust any relationship connections in query.

In this case I just need simple replacement of various foreign keys in Revenues to the real names.
So it would mean that I need to manually adjust relationships in every query that I make to get what I want.

Wouldn't be than much more simpler If I just used the real names as foreign keys in Revenues table, and not the numerical autonumber IDs?
In that case I wouldn't need to bring in any other table except Revenues, and the referential integrity would still be enforced (as Client, Country, ProductLine and Products all have unique names).
The Query would also be faster, as there wouldn't be so many joins.

I guess the underlying Revenues table would be a bit heavier, as now the names (some of which might be long, like company name) would be used instead of long integer autonumbers.
But how much of a problem can that really represent?

[Rabbit]

There isn't a way to set up the relationships such that you could create your current query without changing the joins.

You certainly could use a natural key (names) instead of a surrogate key (autonumber). However, you should be aware of the disadvantages of using a natural key. One of which, as you say, is larger storage requirements.

This is an excerpt from the wikipedia article on natural keys.

The main disadvantage of choosing a natural key is that its value may change and the relational database engine may not be able to propagate that change across the related foreign keys. For example, if person_name is used as the primary key for the person table, and a person gets married and changes name, then all of the one-to-many related tables need to be updated also. The secondary disadvantage of choosing a natural key is identifying uniqueness. The primary key must consist of the attributes that uniquely identify a row. However, it may be difficult (or it may add constraints) to create a natural key on a table. For example, if person_name is used as a primary key for the person table, many persons may share the same name and all but the first entry will be rejected as a duplication. The uniqueness constraint may be overcome by adding an additional column to the primary key, like street_address, to increase the likelihood of uniqueness.