Results 1 to 7 of 7

Thread: Access Query

  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Unhappy Unanswered: Access Query

    Hello All,

    I am not a DBA and have little database knowledge and I am struggling to create what I think would be an easy Access query... Maybe someone can help or stear me in the right direction. I am trying to create a query to filter a table for a customers latest CallInDate.

    For example:

    Current Table Data
    FName LName Company CallInDate
    Jane Doe Carp 10/02/2003
    Jane Doe Carp 10/05/2003
    John Doe Carp 10/05/2003
    John Doe Carp 10/02/2003

    This is a simple example. The table contains various people from the same and different companies with different CallInDates that can range from any time frame. I would expect my query results to display one record for each person (the person does not have a unique identifier) with the company name and the latest CallInDate.

    Expected Query Results from above data would be
    Jane Doe Carp 10/05/2003
    John Doe Carp 10/05/2003

    Is this possible to do?
    Any suggestions are appreciated.
    Last edited by MissMell; 10-17-03 at 23:25.

  2. #2
    Join Date
    Sep 2003
    Location
    Gloucester
    Posts
    28
    You need a Totals Query where the result is
    "Grouped by" the FName, LName, Company
    and the CallinDate Shows the "Last" instance of the date called.

    which would look something like this

    SELECT Customers.FName, Customers.LName, Customers.Company, Last(Customers.CallInDate) AS LastOfCallInDate
    FROM Customers
    GROUP BY Customers.FName, Customers.LName, Customers.Company
    ORDER BY Last(Customers.CallInDate);


    this takes this data

    FName LName Company CallInDate
    Jane Doe Pulhams 01-Jan-03
    John Doe chocobox 01-Jan-03
    Peter Smith ThongsRUs 02-Jan-03
    William Jones Songs from Our Valleys 02-Jan-03
    Mary Grey Weather analysis 01-Jan-03
    Joan Arc Fuel Supplies 01-Jan-03
    Jane Doe Pulhams 02-Jan-03
    Peter Smith ThongsRUs 03-Jan-03
    John Doe chocobox 02-Jan-03
    Jane Doe Pulhams 03-Jan-03
    Jane Doe Pulhams 04-Jan-03
    Jane Doe Pulhams 05-Jan-03


    And produces


    FName LName Company LastOfCallInDate
    Mary Grey Weather analysis 01/01/2003
    Joan Arc Fuel Supplies 01/01/2003
    William Jones Songs from Our Valleys 02/01/2003
    John Doe chocobox 02/01/2003
    Peter Smith ThongsRUs 03/01/2003
    Jane Doe Pulhams 05/01/2003


    OK?

  3. #3
    Join Date
    Oct 2003
    Posts
    5
    THANKK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    It worked great. I cannot say how much I appreciate your reply. I was driving myself crazy over this.
    Thank You again.

  4. #4
    Join Date
    Oct 2003
    Posts
    5
    One more question in regards to taking this further, if I am not pushing it.........

    If for example I included another field say Type which would differ for each record, can I still return one record for each person with the last CallInDate and show the Type in the query results?

    Example:
    FName LName Company CallInDate Type
    Jane Doe Carp 10/02/2003 HelpCall
    Jane Doe Carp 10/05/2003 SalesCall
    John Doe Carp 10/05/2003 QCall
    John Doe Carp 10/02/2003 TCall

    I would like to get the following reslts:
    Jane Doe Carp 10/05/2003 SalesCall
    John Doe Carp 10/05/2003 QCall

    Thank You

  5. #5
    Join Date
    Sep 2003
    Location
    Gloucester
    Posts
    28
    Same procedure

    SELECT Customers.FName, Customers.LName, Customers.Company, Last(Customers.CallInDate) AS LastOfCallInDate, Last(Customers.[Call type]) AS [LastOfCall type]
    FROM Customers
    GROUP BY Customers.FName, Customers.LName, Customers.Company
    ORDER BY Last(Customers.CallInDate);


    takes

    FName LName Company CallInDate Call type
    Jane Doe Pulhams 01-Jan-03 Enquiry
    John Doe chocobox 01-Jan-03 Sales Pitch
    Peter Smith ThongsRUs 02-Jan-03 Order
    William Jones Songs from Our Valleys 02-Jan-03 Enquiry
    Mary Grey Weather analysis 01-Jan-03 Order
    Joan Arc Fuel Supplies 01-Jan-03 Enquiry
    Jane Doe Pulhams 02-Jan-03 Order
    Peter Smith ThongsRUs 03-Jan-03 Enquiry
    John Doe chocobox 02-Jan-03 Sales Pitch
    Jane Doe Pulhams 03-Jan-03 Enquiry
    Jane Doe Pulhams 04-Jan-03 Order
    Jane Doe Pulhams 05-Jan-03 Sales Pitch




    and provides

    FName LName Company LastOfCallInDate LastOfCall type
    Mary Grey Weather analysis 01/01/2003 Order
    Joan Arc Fuel Supplies 01/01/2003 Enquiry
    William Jones Songs from Our Valleys 02/01/2003 Enquiry
    John Doe chocobox 02/01/2003 Sales Pitch
    Peter Smith ThongsRUs 03/01/2003 Enquiry
    Jane Doe Pulhams 05/01/2003 Sales Pitch

  6. #6
    Join Date
    Oct 2003
    Posts
    706

    Thumbs up

    What you are asking for is to see the results of a query with the following options:

    FName Group By
    LName Group By
    Company Group By
    Call-In Max

    Press the "E" key on the query toolbar (actually, it's a sigma) to get options like "Group By" and "Max."

    Here's what you're telling the computer to do:

    (a) "Every unique combination of [FName, LName, Comp] forms a 'group' that is of interest to me. I want one row in my output for each group that you find.
    (b) For each group, I also want to know the Maximum (latest...) Call-In-Date that you found for that group.

    There's one more property of queries that you may need to use; not in this case. That's "Unique Values." Let's say that you want to know all the [FName, LName, Comp] values that occur, but you don't want to know more than that, and in this case you're not asking any questions (min, max, etc) about a group. If you simply query the three fields you might get many copies of the same value (one for each call-in-date, presumably). Turning "Unique Values = Yes" causes the query to Select only Distinct combinations of the values and to return exactly row for each combination that was found. (Essentially what the previous query did to /find/ the groups in question...)
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  7. #7
    Join Date
    Oct 2003
    Posts
    5
    Thank You both very much for you suggestions. From the assistance with the syntax to a break down of what I was trying to do has been extremely helpful and a learning experience. I now have the query working as expected.

    Thanks 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
  •