Forum Discussion
Joined Table Not Returning Expected Dataset
Thanks for sending me the cut down version of the database
Problem solved. It’s a data issue with the Symbol field when imported from Excel.
When you imported your Excel data to tblDivReceived, the Symbol field had a leading space.
However, there is no leading space for that field in tblHoldings.
Compare the field lengths using the following queries:
SELECT DISTINCT tblDivReceived.Symbol, Len([Symbol]) AS LenSymbol
FROM tblDivReceived
ORDER BY tblDivReceived.Symbol;
And
SELECT tblHoldings.Symbol, Len([Symbol]) AS LenSymbol
FROM tblHoldings;
So the fields are never identical and the table join fails.
To fix the issue with your current dataset, run this update query to remove the leading space using the Trim function:
UPDATE tblDivReceived SET tblDivReceived.Symbol = Trim([Symbol]);
You will then find the qryTrueData works perfectly
To avoid the problem with future Excel imports, use the Trim function on the Excel data before import or when you import
I restored the leading space in the file I've sent back to you by email so you can check all this for yourself. Hopefully you’ll be OK from here onwards
Did you mean reversing an outer join i.e. left join=>right join (or vice versa).
To clarify your question, please post the SQL to both queries together with the output.
On a separate point, i suggest you LINK the Excel worksheet rather than import it. That will ensure you always have the latest Excel data and also avoid increasing the Access file size unnecessarily.
Thanks for replying to my question.
I used the term "reversed" instead of "left" and "right". I can't post the output because it contains personally sensitive information. This SQL:
SELECT tblDivReceived.Symbol, tblHoldings.CurrentValue, tblDivReceived.Amount, tblDivReceived.DivReceiptQandY, tblHoldings.NoOfShares
FROM tblDivReceived LEFT JOIN tblHoldings ON tblDivReceived.Symbol = tblHoldings.Symbol;
returns data only from tblDivReceived when run against the Excel data but produces the correct dataset when run against dummy data entered by hand.
This SQL:
SELECT tblDivReceived.Symbol, tblHoldings.CurrentValue, tblDivReceived.Amount, tblDivReceived.DivReceiptQandY, tblHoldings.NoOfShares
FROM tblDivReceived RIGHT JOIN tblHoldings ON tblDivReceived.Symbol = tblHoldings.Symbol;
returns data only from tblHoldings.
I can't link the Excel spreadsheet because I have to delete and replace the data every month. If the file size becomes a problem I'll compact the database.
Hmm. As I suspected, it wasn't an inner join.
Unless you can supply some dummy data to illustrate the point, its difficult to do more than make educated guesses. Its not clear to me whether you are a beginner or experienced in Access. So I suggest you read these two articles from my website Query Join Types and Synchronise Data with external sources as they may help you construct suitable queries.
As for the other point, it is very rare that deleting and replacing data is appropriate, let alone doing so on a monthly basis. Instead append and update queries should be used to get the latest data.
Occasionally it may be appropriate to archive data.
Even with what you're doing now, the Excel data does not need to be imported. Linking should work fine.
- Access. Yes - they're not inner joins.
I'm an experienced Access developer, although I haven't done anything serious in more than years in it (and have muddled up some of the terminology).
I emailed you three screenshots of dummy data tables and the query output. The same SQL yields entirely different and useless datasets.
Linking won't work. Each month I download a CSV file of my holding from my brokerage. That file changes in multiple details and I use it to calculate the current yields on my current holdings. The data from the previous month is entirely irrelevant and it has to be deleted. - Sorry - I missed this off from my last post:
Thank you for your reply. I visited your website and clearly, you know about Access.- Hi.
I got your email. This was my reply:
Difficult to give you any specific advice with that information.
Which query SQL gave that output?
Suggest you investigate what differences there are between the dummy data and the real data
Better still upload a modified database with a few altered records that illustrate the issue
