Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2010
    Location
    Minnesota
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I知 working on compiling three queries into on

    The first one counts the number of apps
    The next one sums the told worth of apps
    And the last one ranks the reps by apps

    Three Problems I知 having:

    **When running the query is takes 20 -30 minutes. (I知 assuming this is do to the Ranking field)
    **I知 not able to get the mail table to index (gives me a error about duplicates)
    **I compact and repair a couple times throughout the day

    **When running the query it starts to show duplicates.
    **When I run the query by themselves they show the correct number of records
    **The output information for my Ranking data has to many numbers after the .list=1]It looks like this 4.57842136
    **I would like it to look like this: 4.6

    Code:
    SELECT [Master Table_Total_per_Month].OMNI_Number, [Master Table_Total_per_Month].Account_Executive_Name, [Master Table_Total_per_Month].[Correspondent Name], [Master Table_Count_Apps].[Total Of AMOUNT_ACTUAL_LOAN] AS Total_LoanYTD, [Master Table_Total_per_Month].[Total Of AMOUNT_ACTUAL_LOAN] AS Total_Loan_AmountTYD, [Master Table_Count_Apps].Jan AS Jan_App_Count, [Master Table_Total_per_Month].Jan AS Jan_Loan_Amount, [Master Table_Count_Apps].Feb AS Feb_App_Count, [Master Table_Total_per_Month].Feb AS Feb_Loan_Amount, [Master Table_Count_Apps].Mar AS Mar_App_couont, [Master Table_Total_per_Month].Mar AS Mar_Loan_Amount, [Master Table_Count_Apps].Apr AS April_App_Count, [Master Table_Count_Apps].Apr AS April_Loan_Amount, [Master Table_Count_Apps].May AS May_App_Count, [Master Table_Total_per_Month].May AS May_Loan_Amount, [Master Table_Count_Apps].Jun AS June_App_Count, [Master Table_Total_per_Month].Jun AS June_Loan_Amount, [Master Table_Count_Apps].Jul AS July_App_Count, [Master Table_Total_per_Month].Jul AS July_Loan_Amount, [Master Table_Count_Apps].Aug AS Aug_App_Count, [Master Table_Total_per_Month].Aug AS Aug_Loan_amount, [Master Table_Count_Apps].Oct AS Oct_App_Count, [Master Table_Total_per_Month].Oct AS Oct_Loan_Amount, [Master Table_Count_Apps].Nov AS Nov_App_count, [Master Table_Total_per_Month].Nov AS Nov_Loan_Amount, [Master Table_Count_Apps].Dec AS Dec_App_Count, [Master Table_Total_per_Month].Dec AS Dec_Loan_Amount, [Master Table_Total_per_Month].[3Month], [Master Table_Total_per_Month].[6Month], [Master Table_Total_per_Month].[9Month], [Master Table_Total_per_Month].[12Month], [Master Table_Total_per_Month].CustomerTotal, Master_Table_Rank.Rank, Master_Table_Rank.TotalRecords, Master_Table_Rank.[0to5Rank]
    FROM ([Master Table_Total_per_Month] LEFT JOIN [Master Table_Count_Apps] ON [Master Table_Total_per_Month].OMNI_Number = [Master Table_Count_Apps].OMNI_Number) LEFT JOIN Master_Table_Rank ON [Master Table_Count_Apps].OMNI_Number = Master_Table_Rank.OMNI_Number
    GROUP BY [Master Table_Total_per_Month].OMNI_Number, [Master Table_Total_per_Month].Account_Executive_Name, [Master Table_Total_per_Month].[Correspondent Name], [Master Table_Count_Apps].[Total Of AMOUNT_ACTUAL_LOAN], [Master Table_Total_per_Month].[Total Of AMOUNT_ACTUAL_LOAN], [Master Table_Count_Apps].Jan, [Master Table_Total_per_Month].Jan, [Master Table_Count_Apps].Feb, [Master Table_Total_per_Month].Feb, [Master Table_Count_Apps].Mar, [Master Table_Total_per_Month].Mar, [Master Table_Count_Apps].Apr, [Master Table_Count_Apps].Apr, [Master Table_Count_Apps].May, [Master Table_Total_per_Month].May, [Master Table_Count_Apps].Jun, [Master Table_Total_per_Month].Jun, [Master Table_Count_Apps].Jul, [Master Table_Total_per_Month].Jul, [Master Table_Count_Apps].Aug, [Master Table_Total_per_Month].Aug, [Master Table_Count_Apps].Oct, [Master Table_Total_per_Month].Oct, [Master Table_Count_Apps].Nov, [Master Table_Total_per_Month].Nov, [Master Table_Count_Apps].Dec, [Master Table_Total_per_Month].Dec, [Master Table_Total_per_Month].[3Month], [Master Table_Total_per_Month].[6Month], [Master Table_Total_per_Month].[9Month], [Master Table_Total_per_Month].[12Month], [Master Table_Total_per_Month].CustomerTotal, Master_Table_Rank.Rank, Master_Table_Rank.TotalRecords, Master_Table_Rank.[0to5Rank];

  2. #2
    New Lounger
    Join Date
    Dec 2010
    Location
    Minnesota
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, if anyone can help me understand why when I attempt to combine all these of these queries, that I知 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

    Help Removing Duplicates record

    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
    Code:
    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
    Code:
    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
    Code:
    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;

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Without seeing the queries as they currently exist, it is difficult to visualize the issues you are having. Can you post the SQL strings for the three indivicual queries? Also some info on your table designs would be helpful - what the field types are and what indexes do exist. In all probability it has to do with missing indexes on your tables. Also an idea of the number of records in each of the table would be helpful.

    UPDATE: After reading your next thread, I realized you have provided the SQL strings I asked for in it, so I merged it with your first question since they are on the same subject. Note that your first query is a "crosstab" query, so it is very difficult to combine that query with the other queries that use it. Information on indexes and field types and the number of records would help us understand the scope of your problems. If possible, uploading a sanitized version of your database with just a few sample records would be even better.
    Wendell

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    From the description you give, it seems odd that you are using Cross Tab queries. I would have thought that Totals (or Group by) queries would be what you want. It is unusual to join multuple cross tabs together.

    Are you sure that all the queries return the correct number of rows? I had a similar situation this week where I had a query that broguht together 10 other group by queries that each summed different data. the final query was showing one person twice. One of the 10 queries showed that person twice, because I had put in a Group By field that should not have been there. If one of the queries has a duplicate, then the final query also has a duplicate.

    I agree with Wendell that it is hard to offer much more help without seeing a sample db.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •