access2010
Registered User.
- Local time
- Today, 07:51
- Joined
- Dec 26, 2009
- Messages
- 1,103
FROM Investments_Purchases_SalesT INNER JOIN Investments01_tbl ON Investments_Purchases_SalesT.Symbol_Stock = Investments01_tbl.Symbol_Stock
Which Report are you referring to and what data are you expecting to be displayed.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
Thank you Doc man and I will try to find out what was changed in the database that was created in 2010.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 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.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
HiThank 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
If I cannot find out the problem, please tell me how to join the records so I can have the Volunteers update the records.
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.
The attached is the Relationship Window and there are no Relationships set.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.
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.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.
One of my earliest bosses had that mantra as a framed embroidery hanging in his office. I'm assuming his wife did the needlework![]()
My wife is a prolific knitter, and you should see the size of some of her needles.But of course she did. Isn't it the main duty of a good wife to needle her husband when he needs it?