Looking to create- Three table join query. Two sources / one reference table variance reconciliatio

[AO0_1]

Hi all,
Hoping for a little help. I'm trying to create a query which can look at variances between two sources. I've tried a few things but they all require multiple queries which does work but wondering more out of curiosity, if it is possible to create the desired result with simply one query. Of course everything works fine if there is only one security but since either A or B can contain records for which there is no match in the other table this causes issues. Joining the tables in most of the manners I can think of result in a ambiguous join errors which I suspect is due to the fact that the query has to capture everything in the reference table but needs to join on the security in order to stop the query from comparing every record in A to those in B. Attempting to add a new table which has all the Securities also causes an ambiguous join error.


Inputs
Table A(Source A)
Field1 - Fund
Field2 - Security
Field3 - Amt
Fund Security Amt
A S1 100
B S1 100
C S2 150

Table B(Source B)
Field1 - Account
Field2 - Security
Field4 - Amt
Account Security Amt
1 S1 100
2 S1 50
3 S2 50
1 S2 50
4 S2 50

Table C(Reference)
Field1 - Fund
Field2 - Account
Fund Account
A 1
B 2
C 3
D 4

Expected Results

Fund A / Account 1
Security S1 no variance
Security S2 variance of 50 (tblB long 50)

Fund B / Account 2
Security S1 Variance 50 (tblB short 50)

Fund C / Account 3
Security S2 Variance 100 (tblB short 100)

Fund D / Account 4


Security S2 Variance 50 (tblB long 50)

[CJ_London]

I note that you have had 33 views and no responses. This implies that potential responders do not understand your requirement (I'm one of them). Suggest you put some example data with realistic names and the required output into excel and upload the file (use the advanced editor). At the same time, explain how you get to your required output. While your post count is less than 10 you will need to zip the file before uploading.

[Minty]

I understand the question (I think) and is relatively easily achieved in SQL server with a FULL OUTER JOIN and a couple of Union queries.

I'm not sure it is very easy at all in access without some temporary tables; a explanation here https://www.w3schools.com/sql/sql_join_full.asp

I have used them a few times to reconcile difference between stock counts.
You need to know what is in both Table A & B, and more importantly what isn't in Table A that is in Table B, and what isn't in Table B that is in Table A , all at the same time.

[AO0_1]

Thanks both of you,
Minty has the idea of what i'm looking to accomplish. Essentially a full join which will allow for a reconciliation between tables A & B where table C joins the two tables. As mentioned it is possible using multiple queries, eg left, inner, and right along with temporary tables however I'm looking for a simpler, perhaps more efficient route.
I'll take a look at the site you mentioned to see if that allows for creation of something easier or if I can just learn a new method.

Also attached is the zip file with a snip of the tables and some sample data.
Table A contains the expected data. Table B contains the actual data. Table C is the reference table which joins A & B. The output would then show all records from both tables A & B along with a new field showing the variance.