Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2007
    Posts
    74

    Unanswered: Select and a Count in 1 query from 1 table

    Hi everyone,

    I am not sure if this will make sense or not but here we go. I have a table with information about reps who submit orders with invalid customer email addresses. I need to me able to run a query that will return the following data:

    State - Center Manager - Sales Person - How many invalid orders the rep had

    This data should be distinct rows based on a selected date range (will pull a month at a time). So the result would look like this:

    NJ - Bob Jones - Tammy Davis - 4
    NJ - Bob Jones - Bill Frist - 7
    NJ - Bob Jones - Jackie Lang - 9
    NJ - Bob Jones - Bruce Lee - 2
    NJ - Karen Wall - Kim Ju - 2
    NJ - Karen Wall - Sally Siu - 2
    NJ - Karen Wall - Joe Harmon - 2
    PA - Bill Turley - Jules Leigh - 7
    PA - Bill Turley - Hamad Kassan - 1
    PA - Bill Turley - Jack Thompson - 9



    My database is not normalized at all and for now, there is no time to accomplish that task. All of the data I need is in the "Invalid_Email" table. The columns needed for the query and the associated data are as follows:

    Field Name Contains
    SERVICE_STATE State
    CCM Name Center Manager Name
    SALES_PERSON Sales Person Name
    RECIEVED_DATE Date used for date range in query



    This is what I have so far and I am sure I am way off base can anyone offer any guidance?


    Code:
    SELECT DISTINCT [Invalid_Email].[SERVICE_STATE], [Invalid_Email].[CCM Name], [Invalid_Email].[SALES_PERSON]
    FROM Invalid_Email
         INNER JOIN Invalid_Email AS [IE2] ON ([Invalid_Email].[SALES_PERSON] = [IE2].[SALES_PERSON])
         INNER JOIN (SELECT [IE2], COUNT('SALES_PERSON') AS Total_Count FROM [Invalid_Email] WHERE [SALES_PERSON] = [IE2].[SALES_PERSON])

  2. #2
    Join Date
    May 2009
    Posts
    258
    This seems pretty straightforward, I suggest using GROUP BY:
    Code:
    SELECT [SERVICE_STATE], [CCM Name], [SALES_PERSON], COUNT(*) AS Invalid_Orders
    FROM Invalid_Email
    WHERE RECEIVED_DATE BETWEEN Beg_Date_Range AND End_Date_Range
    GROUP BY [SERVICE_STATE], [CCM Name], [SALES_PERSON]
    Unless I'm missing something, that should about do it. You'll have to specify the date range of course.

    Regards,

    Ax

  3. #3
    Join Date
    Jun 2007
    Posts
    74
    Firstly, Thank you!

    Not sure why I was trying to make is so difficult. Let's say I wanted to do return the top 10 offenders for each CCM_Name. Can you tell me what that would look like?

  4. #4
    Join Date
    May 2009
    Posts
    258
    This is a bit more difficult to do in Access. Are there a lot of CCM's?
    Last edited by Ax238; 10-08-09 at 13:15.

  5. #5
    Join Date
    Jun 2007
    Posts
    74
    There are 232 CCMs in the region I need to analyze and 1252 if I have to expand this company wide..

  6. #6
    Join Date
    May 2009
    Posts
    258
    Assuming the first query I sent to you is stored as qryTest, the following should do it, although I haven't tested:
    Code:
    SELECT [CCM Name], [SALES_PERSON], Invalid_Orders
    FROM qryTest A
    WHERE Invalid_Orders IN
    (SELECT TOP 10 Invalid_Orders FROM qryTest B
    WHERE A.[CCM Name]=B.[CCM Name]
    ORDER BY Invalid_Orders DESC)
    Regards,

    Ax

  7. #7
    Join Date
    Jun 2007
    Posts
    74
    Well, I modified your solution with my query name as follows.

    SELECT [CCM_NAME], [SALES_PERSON], Invalid_Orders
    FROM [DQ Extract Data by Date Range] A
    WHERE Invalid_Orders IN
    (SELECT TOP 10 Invalid_Orders
    FROM [DQ Extract Data by Date Range] B
    WHERE A.[CCM_NAME]= B.[CCM_NAME]
    ORDER BY Invalid_Orders DESC);


    This is the error I get when I try to run it:

    This expression is typed incorrectly, or it is to complex to be evaluated. For example, a numeric expression may contain too many complicatd elements. Try simplifying the expression by assigning parts of the expression to variables.


    WOW

  8. #8
    Join Date
    May 2009
    Posts
    258
    Try explicitly setting the dates in the DQ Extract Data by Date Range query, such as:
    WHERE RECEIVED_DATE BETWEEN #9/1/2009# AND #9/30/2009#

Posting Permissions

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