Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Posts
    11

    Question Unanswered: Select # of Records per Agent

    Hello Everyone,

    It has been a while since I had last used this forum. I have a problem and need your suggestion.

    I am trying to do a query (or queries) in which I get 15 records per agent for a daily mailing.

    Here is the scenario:

    There are two tables (CustomerList and Agent_Info). CustomerList contains the customer information and Agent_Info contains the agent information.

    The common field that are linked between these tables is Agent_Name.

    I have created a query that simply select the fields along with the agent information. For eg.

    CustomerList.ID
    CustomerList.Cust_Name
    CustomerList.Address
    CustomerList.City
    CustomerList.State
    CustomerList.Zip
    CustomerList.Agent_Name
    Agent_Info.Agent_Name
    Agent_Info.Phone
    Agent_Info.Customer Center
    Agent_Info.Email
    Agent_Info.UPJ_ID (this is the agent's ID#)

    Let's say there are 10 agents records in Agent_Info and about 10,000 records in AddTable, how do I begin?

    Below is my feeble attempt in creating a query for this issue and of course it is not working:

    SELECT [Agent_Info].Agent_Name, [Agent_Info].[Customer Center],[Agent_ Info].[Phone], [Agent_Info].Email
    FROM [Agent_Info]
    WHERE Exists
    (SELECT TOP 15 CustomerList.ID, CustomerList.[Cust_Name], CustomerList.Address, CustomerList.City, CustomerList.State, CustomerList.Zip, CustomerList.Agent_Name
    FROM CustomerList
    WHERE CustomerList.Agent_Name =[Agent_Info].Agent_Name
    ORDER BY CustomerList.Agent_Name DESC)
    ORDER BY [Agent Information].Agent_Name;

    Please help, I really appreciate any feedback to my situation.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There are two ways to do this. One is tricky (in fact, I'd need to remind myself how to do it) and will give you a single result with all agents and 15 customers per agent. The other is easier - one query per agent containing 15 rows of customer data (built using dynamic SQL). This might be better anyway since this is for a "daily mailing".

    Pe3rhaps it would be best to tell us exactly what you intend to do with the data once you get it to work out the ideal method?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2006
    Posts
    11
    Quote Originally Posted by pootle flump
    There are two ways to do this. One is tricky (in fact, I'd need to remind myself how to do it) and will give you a single result with all agents and 15 customers per agent. The other is easier - one query per agent containing 15 rows of customer data (built using dynamic SQL). This might be better anyway since this is for a "daily mailing".

    Pe3rhaps it would be best to tell us exactly what you intend to do with the data once you get it to work out the ideal method?
    -----------------------------------------------------

    Thank you for your response. I had resolved the issue and was able to print the # of the letters required for each agent.

    Thank you again.

Posting Permissions

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