Help Removing Duplicates record (is it my join?)

[TJVFree]

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

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
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]
FROM [Master Table]
WHERE ((([Master Table].CORRESPONDENT_PURCHASE_DATE)>#1/1/2010#))
GROUP BY [Master Table].orig_code, [Master Table].NAME_WHOLESALE_ACCOUNT_EXEC, [Master Table].orig_name, [Master Table].NAME_WHOLESALE_ACCOUNT_EXEC
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
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
FROM [Master Table]
WHERE ((([Master Table].CORRESPONDENT_PURCHASE_DATE)>#1/1/2010#))
GROUP BY [Master Table].orig_code, [Master Table].NAME_WHOLESALE_ACCOUNT_EXEC, [Master Table].orig_name
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
FROM qryCustomerTotals1
ORDER BY qryCustomerTotals1.OMNI_Number, qryCustomerTotals1.Account_Executive_Name, qryCustomerTotals1.[Correspondent Name], qryCustomerTotals1.CustomerTotal DESC;

I'm not sure why I cant attached anything yet?

Thanks for all your help

 

[dhookom]

You can't attach files because the site doesn't allow it.

You should also abandon this thread and stick with the same question you asked in the queries forum.

Duane
Hook'D on Access
MS Access MVP