473,324 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Select query doesn't show all records after join

doma23
107 100+
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!
Nov 9 '11 #1
8 2451
jimatqsi
1,271 Expert 1GB
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
Nov 9 '11 #2
Rabbit
12,516 Expert Mod 8TB
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.
Nov 9 '11 #3
doma23
107 100+
@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.
Nov 9 '11 #4
Rabbit
12,516 Expert Mod 8TB
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.
Nov 9 '11 #5
doma23
107 100+
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.
Nov 9 '11 #6
Rabbit
12,516 Expert Mod 8TB
In the query. What you do in the relationship does not affect what you can or must do in query design.
Nov 9 '11 #7
doma23
107 100+
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?
Nov 9 '11 #8
Rabbit
12,516 Expert Mod 8TB
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.
Nov 9 '11 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Graham Leggett | last post by:
Hi all, I am trying to do a query that returns all rows that are _not_ part of a join, and so far I cannot seem to find a query that doesn't take 30 minutes or more to run. The basic query...
1
by: (Pete Cresswell) | last post by:
..DefaultView = Continuous Forms ..RecordSource = a query. While the form & subform are open, I can open up the .RecordSource query and see five records. If I insert a record, then re-open...
9
by: MLH | last post by:
I have a table (tblCorrespondence) holding records with fields like , , , , , , , etc... About a dozen 's are defined and I often use queries to extract records of a given . That's pretty easy....
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
3
by: torpecool | last post by:
Hello Everyone, I have been searching around for an answer to this question with no luck. I hope that some of you may have a couple of good ideas I could try. I am running MySQL 4.1.20. ...
8
by: Gem | last post by:
Hi I'm struggling with a query returning too many records. I have 3 related tables. tblPatients tblOperations tblTargets Each patient in tblPatients can have more than one operation...
4
by: kdubble | last post by:
Hi I am trying to get the results of a query to show only unique student records (not duplicates). Is there a simple way to make the criteria field do this? I am not too familiar with SQL. ...
11
by: bikefixxer | last post by:
I'm a beginner with Access and am using the 2007 version on XP. I've created a database that keeps track of employee hours where I work. Everything has worked fine until we recently hired someone...
3
by: Coll | last post by:
I have a form that had been setup with a combo box. You'd select a value from the combo box and a query would open and display matching records. I now need to remove the combo box and set up a text...
1
by: TZEM | last post by:
Hi - I'm new to Access and trying to create a complicated database that records info about system interfaces. I want to create a crosstab query that returns a field from the interface record, if a...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.