Joined Table Not Returning Expected Dataset

[MaxCA1000]

My database has two main tables populated by data imported from Excel - data that I've downloaded from my brokerage account and that originates in CSV format that I've saved in Excel format.

The two tables are joined with an inner join in a query - the left side one on the primary key, Symbol, and the right on a field of the same name that is not primary. This should produce a dataset from both tables, but it doesn't. It pulls either nothing at all or data only from one table if I reverse the join. If I delete the join I get a Cartesian dataset. So there doesn't seem to be anything wrong with the data itself. I've carefully checked it - there are no odd characters, spaces, etc, and I've used Trim in Excel to loop through all the data and Len in VB to check for extraneous characters.

I have used temporary joins and not relationships because I cannot enforce referential integrity.  I own stocks that don't pay dividends and I will have dividends in my records from stocks that I no longer own.

When I make copies of the two tables and populate them with hand-entered data, the query works exactly as it should producing the joined dataset I expect. So there's nothing wrong with the way the fields are set up nor with the SQL.

I've used the Len function to compare imported data with manually entered data just in case there are any hidden characters in the imported data, but the data is identical.

The problem seems to be with data imported into Access tables from Excel, but I can't find any explanation of this or any workaround. Can anyone help?

[#2]

Your description is somewhat unclear. You mention an inner join .... then reversing it....which isn't possible.
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.

[#3]

@isladogs 

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. 

[#4]

@MaxCA1000 

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.

[#5]

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.

[#6]

Sorry - I missed this off from my last post:
Thank you for your reply. I visited your website and clearly, you know about Access.

[#7]

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

[#8]

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;

[#9]

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.

[#10]

I think the only way forward is for you to anonymise your data and upload the relevant parts of your database

[#11]

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.

[#12 · Accepted solution]

@MaxCA1000 

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

[#13]

yes - this is the solution and thank you for it, a great piece of detective work on your part.

[#14]

No problem. Glad its now working for you