Query Duplicates

[sgremp1]

Hoping someone could help/not sure if it's even possible...



I have two sets of data, there are duplicates on each side, and they're purposely there. I need an unmatched query with outer joins, but I also need inner joins...

This is for commission reconciliation, so some accounts have more than one payment which is the reason for the duplicates, and for various other system reasons, not all accounts on each side match up. The company requesting the reconciliation sometimes makes mistakes, or sometimes my company makes a mistake. I need all the matched accounts to show up on the query results, but I also need all the UNmatched accounts to show up. Lets say one table has the following:

Acct #(table 1)
11111
11111
22222
33333

THen another table has:

Acct#(table 2)
11111
22222
33333
44444

I need an end result that looks like this:

Acct #(table 1)
11111
11111
22222
33333
_____

Acct #(table 2)
11111
_____
22222
33333
44444

So I need blanks in each table where the matching account # should go, but I don't want to delete ANY account # from either table, and I also don't want duplicates from both tables, like the result should not show 3 account 11111. This might be hard to understand and I don't konw if it's really possible,

[June7]

Need a dataset of all possible account numbers. If you don't have a table of account numbers, create dataset with a UNION query of the AccountNumber field from both tables.

Join queries to that dataset.


BTW, advise to avoid spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be AccountNum or Account_Num.

[sgremp1]

Thanks for your reply. I have tried a union query, the problem is it only returns unique records, I actually need the duplicates in there. So then I did a union all query, then it returns the duplicates, but the duplicates from BOTH tables, so if I have 2 exact records in one table, and 2 exact records in the other, I want it to return me JUST two records, union returns 1, and union all returns 4. But I also still want a blank record inserted where accounts don't match on either side, like if I have 2 of the exact record on one table, and only 1 matching record on the other table, it should still return the two exact matches in the results on one side, but also a blank on the other...hope that makes sense... Thanks.

[lfpm062010]

If I understand you correctly, to do the first result in table1 is simple. The result in table2 will require some thinking and maybe some clarification.

The SQL below should produce what you need for the result #1.

SELECT table1.[Acct #] FROM table1 WHERE table1.[Acct #] IN (SELECT table2.[Acct #] FROM table2);

NOTE: You might be able to use the same idea for table2.

[June7]

Problem with the example for Table2 output is that Table2 account 11111 will match to both of the Table1 account 11111 records. Consider:

Query1:
SELECT Table1.AcctNum, Table1.ID, DCount("*","Table1","AcctNum=" & [AcctNum] & " AND ID<" & [ID])+1 AS TCt
FROM Table1
ORDER BY Table1.AcctNum, Table1.ID;

Query2:
SELECT Table2.AcctNum, Table2.ID, DCount("*","Table2","AcctNum=" & [AcctNum] & " AND ID<" & [ID])+1 AS TCt, *
FROM Table2
ORDER BY Table2.AcctNum, Table2.ID;

Query3:
SELECT TCt, AcctNum FROM Query1
UNION SELECT TCt, AcctNum FROM Query2;

Query4:
SELECT Query3.AcctNum, Query2.AcctNum
FROM Query2 RIGHT JOIN Query3 ON (Query2.TCt = Query3.TCt) AND (Query2.AcctNum = Query3.AcctNum)
ORDER BY Query3.AcctNum;