Report with the Same ID being printed multiple times. (1 Viewer)

access2010

Registered User.
Local time
Today, 07:51
Joined
Dec 26, 2009
Messages
1,103
Until about October this report was being printed properly.
But someone changed something and now the report is not being printed properly. Any assistance will be appreciated.

Crystal
 

Attachments

Without looking at the report or data source, I assume the issue is with duplicate data or an improperly constructed data source.
 
Looking at the query underlying your report, I see this segment:
Code:
FROM Investments_Purchases_SalesT INNER JOIN Investments01_tbl ON Investments_Purchases_SalesT.Symbol_Stock = Investments01_tbl.Symbol_Stock

The easiest way to get duplicates is if you have multiple records with the same value of Symbol_Stock in both of those named tables - which you do. When you do a JOIN between two tables, each with duplicate entries, you get multiple records that appear to be duplicates. What they REALLY are is multiple combinations. To make it simple, if you have two records in each table that have the same Symbol_Stock value, the JOIN on Symbol_Stock will return FOUR records representing the four combinations of the first and second record in each table that have matching values in Symbol_Stock. Your choice of the field for your JOIN was inappropriate.

There is no way I can "fix" this, however, because I have no idea what was changed to get you where you are now. This is why you keep backups of the DB.
 
Find out who made the changes and tell them to change it back to the way it was before.
 
revert to or open a backup - at least to compare what it was then to what it is now
 
On a separate note, Crystal, your group account often gives us problems that stem from being careless with managing your app's development. If you do not take a very formal approach to developing your app - including backups before you start anything new and KEEPING the backup - this is going to happen repeatedly. No matter how short-handed you are, no matter how inexperienced you are, you still owe it to yourself - and your future self - to be methodical in keeping track of your app's changes.
 
Until about October this report was being printed properly.
But someone changed something and now the report is not being printed properly. Any assistance will be appreciated.

Crystal
Which Report are you referring to and what data are you expecting to be displayed.

First look at you relationship diagram shows that there are no relationships set between any of your tables.

You are trying to retrieve data from Investments01_tbl and Investments_Puchases_SalesT where you have joined both tables on a Text Data Type field

If you want a relationship between these two tables then the correct method is to have a PK named InvestmentID
in your Investments01_tbl with a related FK named InvestmentID in your related Investments_Puchases_SalesT
 
Looking at the query underlying your report, I see this segment:
Code:
FROM Investments_Purchases_SalesT INNER JOIN Investments01_tbl ON Investments_Purchases_SalesT.Symbol_Stock = Investments01_tbl.Symbol_Stock

The easiest way to get duplicates is if you have multiple records with the same value of Symbol_Stock in both of those named tables - which you do. When you do a JOIN between two tables, each with duplicate entries, you get multiple records that appear to be duplicates. What they REALLY are is multiple combinations. To make it simple, if you have two records in each table that have the same Symbol_Stock value, the JOIN on Symbol_Stock will return FOUR records representing the four combinations of the first and second record in each table that have matching values in Symbol_Stock. Your choice of the field for your JOIN was inappropriate.

There is no way I can "fix" this, however, because I have no idea what was changed to get you where you are now. This is why you keep backups of the DB.
Thank you Doc man and I will try to find out what was changed in the database that was created in 2010.

If I cannot find out the problem, please tell me how to join the records so I can have the Volunteers update the records.
Nicole
 
Which Report are you referring to and what data are you expecting to be displayed.

First look at you relationship diagram shows that there are no relationships set between any of your tables.

You are trying to retrieve data from Investments01_tbl and Investments_Puchases_SalesT where you have joined both tables on a Text Data Type field

If you want a relationship between these two tables then the correct method is to have a PK named InvestmentID
in your Investments01_tbl with a related FK named InvestmentID in your related Investments_Puchases_SalesT
Thank you Mike60Smart for your comments, This database was created in 2010 by one of our Volunteers. Something has been changed over the years. I will try your suggestion this weekend by adding a PK to each of our tables.
Nicole
 
Thank you Mike60Smart for your comments, This database was created in 2010 by one of our Volunteers. Something has been changed over the years. I will try your suggestion this weekend by adding a PK to each of our tables.
Nicole
Hi
Does the Report now give you the correct result?
 

Attachments

If I cannot find out the problem, please tell me how to join the records so I can have the Volunteers update the records.

Nicole, this is going to be a problem because of the way you have the tables structured. The way to stop the duplicate listings is to not JOIN both sides of the INNER JOIN on a non-unique field. The field you have in the particular JOIN is not unique in either of its sources.

What you are getting is called a "permutation" JOIN because Access (and other SQL engines) generate their JOINs using combinations. The simple explanation is that your two tables join on StockSymbol - but your structure allows records A and B (hypothetical names) from the first table to have the same StockSymbol, and 1 and 2 (again, hypothetical names) from the second table to have the same StockSymbol. What you will get when you do the INNER JOIN will be combinations of records A1, A2, B1, and B2 - all with the same StockSymbol.

I can't tell you how to do what you need to do because we don't know what you were trying to do. However, INNER JOIN queries are going to work best when one of the fields in question is a no-duplicates case. And StockSymbol or whatever it is called isn't a no-duplicates case in either table.
 
Actual relationships where you enforce RI always join FK in the many-side table to PK in the one side table. They never join data field to data field.
 
Actual relationships where you enforce RI always join FK in the many-side table to PK in the one side table. They never join data field to data field.

Doesn't look they enforce RI. In fact, I don't recall seeing a relationship. I think it must have been a manually entered JOIN.
 
Doesn't look they enforce RI. In fact, I don't recall seeing a relationship. I think it must have been a manually entered JOIN.
The attached is the Relationship Window and there are no Relationships set.
 

Attachments

  • Relationships.png
    Relationships.png
    76 KB · Views: 15
I think it must have been a manually entered JOIN.
Joins are not relationships. You can join any field to any field as long as the data types are compatible. You can join FirstName from tblA to Address in tblB. You can join StateID in tblA to CustomerID in tblB. No problem, except you get nonsense results or no matches at all. The only reason you would ever create this kind of join is if you have a very bad file where the user has been very sloppy with data entry and you are trying to clean it up. But, the resultset will probably not be updateable since it cannot identify specific records for the rows of the recordset.

This OP has been warned many times regarding the poor schema.
 
Joins are not relationships. You can join any field to any field as long as the data types are compatible. You can join FirstName from tblA to Address in tblB. You can join StateID in tblA to CustomerID in tblB. No problem, except you get nonsense results or no matches at all. The only reason you would ever create this kind of join is if you have a very bad file where the user has been very sloppy with data entry and you are trying to clean it up. But, the resultset will probably not be updateable since it cannot identify specific records for the rows of the recordset.

This OP has been warned many times regarding the poor schema.

I have been among the voices warning access2010 about their way of doing things. Pat, you are preaching to the choir here. I explicitly told them they had picked the wrong fields for their JOIN and since there are no indexes (much less unique indexes) you are just about guaranteed to run into issues of permutation JOINs. They are prime examples of the old saying, "If you don't have enough time to do it right, how will you EVER find time to do it over?"
 
One of my earliest bosses had that mantra as a framed embroidery hanging in his office. I'm assuming his wife did the needlework;)
 
One of my earliest bosses had that mantra as a framed embroidery hanging in his office. I'm assuming his wife did the needlework;)

But of course she did. Isn't it the main duty of a good wife to needle her husband when he needs it?
 
But of course she did. Isn't it the main duty of a good wife to needle her husband when he needs it?
My wife is a prolific knitter, and you should see the size of some of her needles. 😲
 
My husband's brother joined an Ashram (cult) some years ago and Chris decided (before we knew the Ashram was a cult) to make a rug with a religious symbol for his brother so I taught him some basic stitches and bought the materials. To his credit, my husband actually finished the rug. It took him three years because he only worked on it during football season because the rug was so heavy and hot;)
 

Users who are viewing this thread

Back
Top Bottom