Help Removing Duplicates record

[TBC]

Hi, if anyone can help me understand why when I attempt to combine all these of these queries, that I’m getting duplicity records on some of then. Am I joining them wrong, should I be doing something different, please help me learn how to correctly join these queries

I have attached the db to help understand

1. The first one counts the number of apps
2. The next one sums the told worth of apps
3. Last one ranks the reps by apps


1. The first one counts the number of apps

TRANSFORM Count([Master Table].AMOUNT_ACTUAL_LOAN) AS CountOfAMOUNT_ACTUAL_LOAN[/FONT]
[FONT=Tahoma]SELECT [Master Table].orig_code AS OMNI_Number, [Master Table].NAME_WHOLESALE_ACCOUNT_EXEC AS Account_Executive_Name, [Master Table].orig_name AS [Correspondent Name], [Master Table].NAME_WHOLESALE_ACCOUNT_EXEC, Count([Master Table].AMOUNT_ACTUAL_LOAN) AS [Total Of AMOUNT_ACTUAL_LOAN][/FONT]
[FONT=Tahoma]FROM [Master Table][/FONT]
[FONT=Tahoma]WHERE ((([Master Table].CORRESPONDENT_PURCHASE_DATE)>#1/1/2010#))[/FONT]
[FONT=Tahoma]GROUP BY [Master Table].orig_code, [Master Table].NAME_WHOLESALE_ACCOUNT_EXEC, [Master Table].orig_name, [Master Table].NAME_WHOLESALE_ACCOUNT_EXEC[/FONT]
[FONT=Tahoma]PIVOT Format([CORRESPONDENT_PURCHASE_DATE],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

2. The next one sums the told worth of apps

TRANSFORM Sum([Master Table].AMOUNT_ACTUAL_LOAN) AS SumOfAMOUNT_ACTUAL_LOAN[/FONT]
[FONT=Tahoma]SELECT [Master Table].orig_code AS OMNI_Number, [Master Table].NAME_WHOLESALE_ACCOUNT_EXEC AS Account_Executive_Name, [Master Table].orig_name AS [Correspondent Name], Sum([Master Table].AMOUNT_ACTUAL_LOAN) AS [Total Of AMOUNT_ACTUAL_LOAN], Sum(IIf([CORRESPONDENT_PURCHASE_DATE]>DateAdd("m",-3,Date()),[Amount_Actual_Loan],0)) AS 3Month, Sum(IIf([CORRESPONDENT_PURCHASE_DATE]>DateAdd("m",-6,Date()),[Amount_Actual_Loan],0)) AS 6Month, Sum(IIf([CORRESPONDENT_PURCHASE_DATE]>DateAdd("m",-9,Date()),[Amount_Actual_Loan],0)) AS 9Month, Sum(IIf([CORRESPONDENT_PURCHASE_DATE]>DateAdd("m",-12,Date()),[Amount_Actual_Loan],0)) AS 12Month, Sum(IIf([CORRESPONDENT_PURCHASE_DATE]>DateAdd("m",-12,Date()),[Amount_Actual_Loan],0)) AS CustomerTotal[/FONT]
[FONT=Tahoma]FROM [Master Table][/FONT]
[FONT=Tahoma]WHERE ((([Master Table].CORRESPONDENT_PURCHASE_DATE)>#1/1/2010#))[/FONT]
[FONT=Tahoma]GROUP BY [Master Table].orig_code, [Master Table].NAME_WHOLESALE_ACCOUNT_EXEC, [Master Table].orig_name[/FONT]
[FONT=Tahoma]PIVOT Format([CORRESPONDENT_PURCHASE_DATE],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

3. Last one ranks the reps by apps

SELECT qryCustomerTotals1.OMNI_Number, qryCustomerTotals1.Account_Executive_Name, qryCustomerTotals1.[Correspondent Name], qryCustomerTotals1.CustomerTotal, (Select count(*) from qryCustomerTotals1 as B where qryCustomerTotals1.CustomerTotal > B.customerTotal) AS Rank, (Select count(*) from qryCustomerTotals1) AS TotalRecords, [Rank]/([TotalRecords]-1)*5 AS 0to5Rank[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]FROM qryCustomerTotals1[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]ORDER BY qryCustomerTotals1.OMNI_Number, qryCustomerTotals1.Account_Executive_Name, qryCustomerTotals1.[Correspondent Name], qryCustomerTotals1.CustomerTotal DESC;

Thank you for all your help

 

[namliam]

How are you combining them?
If you are getting duplicates then either the data is simply duplicated in the DB or your Join's are indeed faulty....
If you look at query 1. That returns OMNI_Number 213 three times because the Correspondent name is different 3 times:
Correspondent Name

 backackESS mnaNE 123AGE ackmna.L
backackESS mnaNE 123AGE ackmna.L
backackESSmnaNE 123AGE ackmna.LLack

If that is your question, then your answer is to clean up yourdata so that there is only one unique name.