09-17-16, 11:51 #1Registered User
- Join Date
- Nov 2013
Unanswered: Compare tables and display non-matching results
Someone PLEASE help me, I'm struck in a weird scenario in MS Access 2013 where,
I'm trying to compare FileNames listed in Table_A and Table_B. The output should display the missing fileNames in Table_B
In Table_A, the User is assigned the access to few files when compared to the complete list of files listed in Table_B
I've tried using the "UnMatched query" in Access (with various combinations, Not NULL options), but it doesn't seem to work.
SELECT TableA.User, TableB.File
FROM TableA RIGHT JOIN TableB ON TableA.[File] = TableB.[File]
WHERE (((TableA.File) Is Null));
Here's how the Output should look like.
"Table_A" has 2 columns "User", "File"
"Table_B" has 1 column "File"
Output (should display the non-matching fileName entries in TableA, TableB)
Request you to kindly help mewith the query for VBA.
Thanks in advance for the timley help.
09-18-16, 03:36 #2Registered User
Provided Answers: 4
- Join Date
- May 2016
I suggest this solution:
First query : Select all possible combinations for each user
SELECT DISTINCT TableA.User, TableB.File FROM TableA, TableB
SELECT qryBesi.User, qryBesi.File FROM TableA RIGHT JOIN qryBesi ON (TableA.File = qryBesi.File) AND (TableA.User = qryBesi.User) WHERE (((TableA.User) Is Null) AND ((TableA.File) Is Null));
SELECT tblRef.User, tblRef.File FROM TableA RIGHT JOIN (SELECT DISTINCT TableA.User, TableB.File FROM TableA, TableB) as tblRef ON (TableA.File = tblRef.File) AND (TableA.User = tblRef.User) WHERE (((TableA.User) Is Null) AND ((TableA.File) Is Null));
09-18-16, 13:57 #3(Making Your Life Easy)
Provided Answers: 10
- Join Date
- Feb 2004
- New Zealand
If you dont understand the above
Create a Query join the 2 Table them right click on the black line that join the tables and change the Join optionhope this help
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
THEY'RE BEHIND YOU FOR A REASON