Hello.
For the life of me I can't figure out why I am getting duplicate results for some of my records in a query.
I have unique values set to Yes. I have also validated that the tables I am using don't have duplicate data. Thanks for your help.
SQL is below.
SELECT DISTINCT [tbl_Rewards Activity Report - By Member Number].[Member Number], [tbl_Rewards Activity Report - By Member Number].[Last Four], [tbl_Rewards Activity Report - By Member Number].[BAL ID], [tbl_Rewards Activity Report - By Member Number].[Primary Name], [qry_Xref - Last Four of Card Number].[Principal Bank Identifier], [tbl_Rewards Activity Report - By Member Number].[Points Earned_Month], [tbl_Rewards Activity Report - By Member Number].[Points_Redeemed Month], [tbl_Rewards Activity Report - By Member Number].[Points_Expired Month], [tbl_Rewards Activity Report - By Member Number].[Available Point Bal#], [tbl_Rewards Activity Report - By Member Number].[Number of Redemptions], [tbl_Rewards Activity Report - By Member Number].[Work of Date]
FROM [qry_Xref - Last Four of Card Number] RIGHT JOIN [tbl_Rewards Activity Report - By Member Number] ON [qry_Xref - Last Four of Card Number].[Member Number] = [tbl_Rewards Activity Report - By Member Number].[Member Number]
WHERE (((IIf([Points Earned_Month]+[Points_Redeemed Month]+[Available Point Bal#]=0,"X",""))<>"X") AND (([tbl_Rewards Activity Report - By Member Number].Status)="A"))
GROUP BY [tbl_Rewards Activity Report - By Member Number].[Member Number], [tbl_Rewards Activity Report - By Member Number].[Last Four], [tbl_Rewards Activity Report - By Member Number].[BAL ID], [tbl_Rewards Activity Report - By Member Number].[Primary Name], [qry_Xref - Last Four of Card Number].[Principal Bank Identifier], [tbl_Rewards Activity Report - By Member Number].[Points Earned_Month], [tbl_Rewards Activity Report - By Member Number].[Points_Redeemed Month], [tbl_Rewards Activity Report - By Member Number].[Points_Expired Month], [tbl_Rewards Activity Report - By Member Number].[Available Point Bal#], [tbl_Rewards Activity Report - By Member Number].[Number of Redemptions], [tbl_Rewards Activity Report - By Member Number].[Work of Date]
HAVING ((([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"CU, TRUWEST" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"CU,TRUWEST" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Test,TRUWEST 1" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Test,TRUWEST 2" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Test,TRUWEST 3" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Test,TRUWEST 4" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Test,Alliance1" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"TLP, Test Account" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"TLP, Test Account 2" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Test, Account" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Test, Account Two" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"TLP, Testing 4" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Account, Test Three" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Loyalty, Breakaway" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Loyalty,Breakaway" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"test account, truwest" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"test,account" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"test's,TLP" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Test, Promo" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"TLP Promo, Test"));
For the life of me I can't figure out why I am getting duplicate results for some of my records in a query.
I have unique values set to Yes. I have also validated that the tables I am using don't have duplicate data. Thanks for your help.
SQL is below.
SELECT DISTINCT [tbl_Rewards Activity Report - By Member Number].[Member Number], [tbl_Rewards Activity Report - By Member Number].[Last Four], [tbl_Rewards Activity Report - By Member Number].[BAL ID], [tbl_Rewards Activity Report - By Member Number].[Primary Name], [qry_Xref - Last Four of Card Number].[Principal Bank Identifier], [tbl_Rewards Activity Report - By Member Number].[Points Earned_Month], [tbl_Rewards Activity Report - By Member Number].[Points_Redeemed Month], [tbl_Rewards Activity Report - By Member Number].[Points_Expired Month], [tbl_Rewards Activity Report - By Member Number].[Available Point Bal#], [tbl_Rewards Activity Report - By Member Number].[Number of Redemptions], [tbl_Rewards Activity Report - By Member Number].[Work of Date]
FROM [qry_Xref - Last Four of Card Number] RIGHT JOIN [tbl_Rewards Activity Report - By Member Number] ON [qry_Xref - Last Four of Card Number].[Member Number] = [tbl_Rewards Activity Report - By Member Number].[Member Number]
WHERE (((IIf([Points Earned_Month]+[Points_Redeemed Month]+[Available Point Bal#]=0,"X",""))<>"X") AND (([tbl_Rewards Activity Report - By Member Number].Status)="A"))
GROUP BY [tbl_Rewards Activity Report - By Member Number].[Member Number], [tbl_Rewards Activity Report - By Member Number].[Last Four], [tbl_Rewards Activity Report - By Member Number].[BAL ID], [tbl_Rewards Activity Report - By Member Number].[Primary Name], [qry_Xref - Last Four of Card Number].[Principal Bank Identifier], [tbl_Rewards Activity Report - By Member Number].[Points Earned_Month], [tbl_Rewards Activity Report - By Member Number].[Points_Redeemed Month], [tbl_Rewards Activity Report - By Member Number].[Points_Expired Month], [tbl_Rewards Activity Report - By Member Number].[Available Point Bal#], [tbl_Rewards Activity Report - By Member Number].[Number of Redemptions], [tbl_Rewards Activity Report - By Member Number].[Work of Date]
HAVING ((([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"CU, TRUWEST" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"CU,TRUWEST" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Test,TRUWEST 1" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Test,TRUWEST 2" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Test,TRUWEST 3" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Test,TRUWEST 4" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Test,Alliance1" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"TLP, Test Account" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"TLP, Test Account 2" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Test, Account" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Test, Account Two" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"TLP, Testing 4" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Account, Test Three" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Loyalty, Breakaway" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Loyalty,Breakaway" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"test account, truwest" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"test,account" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"test's,TLP" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"Test, Promo" And ([tbl_Rewards Activity Report - By Member Number].[Primary Name])<>"TLP Promo, Test"));
