Help help me understand why I'm getting Duplicates record

[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 DE

 

[dhookom]

How are we supposed to understand the "last one" when we don't have a clue what qryCustomerTotals1 is?

Your first query groups by NAME_WHOLESALE_ACCOUNT_EXEC twice.

There is an FAQ in this forum that describes how to create a crosstab with multiple values.

Duane
Hook'D on Access
MS Access MVP

 

[TJVFree]

sorry, I hope this helps

SELECT [Master Table_Total_per_Month].OMNI_Number, [Master Table_Total_per_Month].[Correspondent Name], [Master Table_Total_per_Month].Account_Executive_Name, [Master Table_Total_per_Month].CustomerTotal
FROM [Master Table_Total_per_Month]
ORDER BY [Master Table_Total_per_Month].OMNI_Number;

[codeSELECT qryCustomerTotals1.OMNI_Number, 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, CInt([0to5Rank]*10)/10 AS Star_Rating
FROM qryCustomerTotals1
ORDER BY qryCustomerTotals1.OMNI_Number, qryCustomerTotals1.CustomerTotal DESC;[/code]

 

[dhookom]

Try again using the preview button.

A couple suggestions:
Is the first sql statement, the SQL for qryCustomerTotals1? If so, please make this clear in your posts.

Where are you seeing duplicates? We can't see your data or uniqueness or anything else.

I never use a calculated alias in another expression in a query. You created RANK, TotalRecords, and 0to5Rank as alias' and then used them in other expressions.

Duane
Hook'D on Access
MS Access MVP