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!

 

[MSAccessRookie]

Without seeing data, we can only guess, so I will try, and will limit the scope of this idea to the first part of the problem only.

You have 114.195 records in tblRevenues. When you Join it (ASSUMING INNER JOIN HERE), to tblCategories, your count of records goes down to 113.850.

This sounds like a a classic missing data problem. Change the INNER JOIN to a LEFT JOIN, and display tblRevenues.CategoryID and tblCategories.CategoryID Ordered by tblCategories.CategoryID first and tblRevenues.CategoryID second. This will show all of the Category IDs in tblRevenues that do not have matching IDs in tblCategories.

Once you have done this, you can do the same for any other Tables that are causing additional loss of data.

-- Rookie

 

[spikepl]

Errr relationships galore? Maybe I don't quite understand what the data is about, but there seems to be a lot of redundant keys and relations around. Eg. tblRevenues, tblProductLines and tblProducts. What is the function of ProductLineID in tblRevenues?

 

[MSAccessRookie]

Errr relationships galore? Maybe I don't quite understand what the data is about, but there seems to be a lot of redundant keys and relations around. Eg. tblRevenues, tblProductLines and tblProducts. What is the function of ProductLineID in tblRevenues?

The Field ProductLineID in tblRevenues appears to serve as a Foreign Key to the data contained in the Table tblProductLines.

 

[spikepl]

Yeah, but what for? It's redundant when you also have a ProductID, and if the Revenue relates to Product ID and not to ProductLineID

 

[MSAccessRookie]

Yeah, but what for? It's redundant when you also have a ProductID, and if the Revenue relates to Product ID and not to ProductLineID

I was not commenting as to the correctness or appropriateness of the design, I was only responding to the question regarding the purpose of the Field. Perhaps there is a relationship between the two tables that would change if another table were involved. Perhaps there might be an occasion where only the two tables are required. I am not sure if either of the relationships is redundant, only that they exist.

Also, you have not indicated what happened when you tried the LEFT JOIN suggestion that I made earlier.

 

[spikepl]

OK. I was commenting on the appropriateness of the design, hence my question. It was just one of them. The entire scheme appears overconstrained here and there by seemingly redundant relations and keys, eg. also CategoryID.

 

[doma23]

It's not the join problem. When I just bring in two tables in query, tblRevenues and tblCategories, I get all the records. But the problem is when I bring in the third table, tblClients, because then I have two connections from tblCategories, one that goes to tblRevenues and other that goes to tblClients.
Like somebody pointed me on another forum, it's because some combinations of ClientID and CategoryID in tblClients don't exist in tblRevenues.
When I delete the connection between tblClients and tblCategories in a query (not in a relationship view), then I'm again able to see all the records.

My whole point was that I thought it's possible to make Relationships in Relationship view in that way that it wouldn't interfere with queries. According to what I've been told, that's not possible.
I always need to adjust relationships (in a query view) to suit my query.

Please note here, that if I would be able to separately set up and save three instances of tblCategories 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. Query would show all records without any adjustments.

Regarding the ProductLineID and CategoryID in tblRevenues, I've put them on purpose there as I need to preserve the historical values of these two fields. I've made the relationships on them only to enforce Referential Integrity. Therefore denormalization in this case is justified.
Actual state (not historical) of CategoryID for each client and ProductLineID for each product is visible in tblClients and tblProducts.