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
Thank you for your reply. I visited your website and clearly, you know about Access.
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
- I can't see any difference in the data between what I import from Excel and what I enter myself. The tables with the dummy data are just copies of the imported data tables - all the fields, the calculated fields, etc, are identical. I've used the Len function to compare imported to dummy data and they're the same.
I'm stumped by this.- I think the only way forward is for you to anonymise your data and upload the relevant parts of your database
- I can't really anonymize the data because that would mean destroying the condition we're seeking to rectify. So I deleted much of it and left a small subset that still demonstrates the bizarre problem and I emailed the resulting database to you.
- This is the SQL:
SELECT [Copy Of tblHoldings].Symbol, [Copy Of tblHoldings].CurrentValue, [Copy Of tblDivReceived].Amount, [Copy Of tblDivReceived].DivReceiptQandY
FROM [Copy Of tblDivReceived] LEFT JOIN [Copy Of tblHoldings] ON [Copy Of tblDivReceived].Symbol = [Copy Of tblHoldings].Symbol;