Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Top Values -- Awesome Solutions by Drew & Mark (Access 97, 2002)

    For the record, here is my original reply:

    You can do this either by using a subquery or creating a somewhat convoluted user-defined function. To use subquery (simpler approach) I first had to create a select distinct (unique values) query to select unique ClientID's from main table. This query is joined to main table on ClientID field in query that will select top 3 CallDates for each client. SQL for Select Distinct query:

    SELECT DISTINCT Table1.ClientID AS Client
    FROM Table1;

    This query is named "qryClientID". Note use of alias for ClientID field. SQL for 2nd query:

    SELECT Table1.ClientID, Table1.ClientName, Table1.CallDate, Table1.CallType
    FROM Table1 INNER JOIN qryClientID ON Table1.ClientID = qryClientID.Client
    WHERE (((Table1.CallDate) In (Select Top 3 [CallDate] From Table1 Where [ClientID]=[Client] Order By [CallDate] Desc)) AND ((Table1.CallType)="Sales"))
    ORDER BY Table1.ClientName, Table1.CallDate DESC;

    This query will select top 3 CallDates in descending order for each ClientID in Table1. To select different number modify TOP predicate to specify different value. When I tried to do this without join to the Select Distinct query did not work, even if I aliased ClientID; you need some way to distinguish the ClientID used in subquery criteria from ClientID in main query if main query does not involve 2nd table joined on this field. So I resorted to this trick. You may be able to adapt this example for your project.

    HTH

  2. #2
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Top Values -- Awesome Solutions by Drew & Mark (Access 97, 2002)

    For the record, here is my original reply:


    Okay, here's how to do this with ONE Query..... (I must admit, I had fun doing this....I knew it was possible, I just had to blow a few cobwebs out of my head to remember how!) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Okay, before I post the SQL, here's what I did. First, I added a Primary Key field. Just an AutoNumber. I'm sure your 'real' table structure has a key field for each 'call', if it doesn't, it should. Then, I used a little used SQL feature which lets you put SQL within SQL. (Not an Union Query, but an SQL subquery. SQL Subqueries let you compare data in another SQL statement, without having to have another stored query to do so. Look for the Keyword 'In' and also 'Subqueries' (Use a subquery to define a field of define criteria for a field)(Access 97 help files are the best place to look for this stuff). 'In' is just one of the methods you can build a subquery with.)

    Anyhow, I built a table just like your example, calling it tblCalls. Then I added the AutoNumber field called PKey. Then this SQL produces what you want:

    SELECT T1.ClientID, T1.ClientName, T1.CallDate, T1.CallType
    FROM tblCalls AS T1
    WHERE T1.PKey In
    (SELECT TOP 3 PKey As CheckKey
    FROM tblCalls
    WHERE (T1.ClientID=tblCalls.ClientID) AND (tblCalls.CallType="Sales")
    ORDER BY tblCalls.CallDate DESC)
    ORDER BY T1.ClientID,T1.CallDate DESC;

    If you tear this SQL apart, you'll see that the 'internal' SQL statement (the subquery) lists the top three PKey's by calldate (the OrderBy in the subquery), and where the Calltype is "Sales" and the ClientID matches the ClientID from the primary query. Note, to pull that off, you have to Alias the Primary Query's From clause, so it doesn't get confused.

    Also note, that this does not use grouping. There is no need to. You are already 'grouping' by only pulling up records that match the top 3.

    Essentially, what is happening when this query runs, is that when it starts, the JET engine is going to kick the subquery in, with the first ClientID and come up with the first 3 PKey's for that ClientID (which are Sales) (if there aren't three, you get however many there are.). It should 'cache' those values (not entirely sure how JET is working internally on this kind of stuff), so that when it compares the records in the Primary query to the In statement, it determines if the records qualify or not.

    Anyhow, thanks for the fun problem tonight....I needed it! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Top Values -- Awesome Solutions by Drew & Mark (Access 97, 2002)

    Both Drew and Mark came up with AMAZING solutions to the problem. Here's the variation that worked for my legacy data, along with some notes from Drew that were extremely helpful in figuring out how to adopt this to my actual problem:
    >>
    SELECT T1.ClientID, T1.ClientName, T1.CallDate, T1.CallType
    FROM tblCalls2 AS T1
    WHERE T1.CallDate In
    (SELECT TOP 3 CallDate
    FROM tblCalls2
    WHERE (T1.ClientID=tblCalls2.ClientID) AND (tblCalls2.CallType="Sales")
    ORDER BY tblCalls2.CallDate DESC) And CallType="Sales"
    ORDER BY T1.ClientID, T1.CallDate DESC;

    The difference is that it is still matching the ID between the primary and subquery, but now is determining what records to show based on if the Date is a match in the subquery. To prevent a 'goof' match between an indentical date with a different call type, it forces another criteria on the primary query of making sure the CallType="Sales". This of course is going to give you more then 3 records per ClientID if you have identical dates for the CallType of "Sales". With the fields given, it's the closest I can get. If there are more fields that you can use as 'identifiers', then you may be able to make a stronger 'key'.
    >>

    Note that in my final solution, I did not use the outer query criteria [And CallType="Sales"] because there were no cases of duplicate dates. Also note that I could not run this query directly from a legacy DB2 database and had to run that part locally. With As for SQL Server 97, as long as my fields were indexed, the query ran in just a few minutes.

    The final part of the final solution was to index the fields in my tables (patt, thanks for the additional assistance here!). In DB2 and SQLServer, I have no control over the indices. However, in my local database, I adapted the following code from Access Help:

    Sub CreateIndexX()

    Dim dbsNorthwind As Database
    Dim tdfEmployees As TableDef
    Dim idxCountry As Index
    Dim idxFirstName As Index
    Dim idxLoop As Index

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set tdfEmployees = dbsNorthwind!Employees

    With tdfEmployees
    ' Create first Index object, create and append Field
    ' objects to the Index object, and then append the
    ' Index object to the Indexes collection of the
    ' TableDef.
    Set idxCountry = .CreateIndex("CountryIndex")
    With idxCountry
    .Fields.Append .CreateField("Country")
    .Fields.Append .CreateField("LastName")
    .Fields.Append .CreateField("FirstName")
    End With
    .Indexes.Append idxCountry

    ' Create second Index object, create and append Field
    ' objects to the Index object, and then append the
    ' Index object to the Indexes collection of the
    ' TableDef.
    Set idxFirstName = .CreateIndex
    With idxFirstName
    .Name = "FirstNameIndex"
    .Fields.Append .CreateField("FirstName")
    .Fields.Append .CreateField("LastName")
    End With
    .Indexes.Append idxFirstName

    ' Refresh collection so that you can access new Index
    ' objects.
    .Indexes.Refresh

    Debug.Print .Indexes.Count & " Indexes in " & _
    .Name & " TableDef"

    ' Enumerate Indexes collection.
    For Each idxLoop In .Indexes
    Debug.Print " " & idxLoop.Name
    Next idxLoop

    ' Print report.
    CreateIndexOutput idxCountry
    CreateIndexOutput idxFirstName

    ' Delete new Index objects because this is a
    ' demonstration.
    .Indexes.Delete idxCountry.Name
    .Indexes.Delete idxFirstName.Name
    End With

    dbsNorthwind.Close

    End Sub

    My table of over 30,000 records ran in about ten seconds with just three indices. I can't believe this query works so well! How can I thank you all for this help???

    <img src=/S/cloud9.gif border=0 alt=cloud9 width=25 height=23>

    Again, just in case I haven't said it enough, many, many thanks to Mark, Patt, Wendell, and Drew -- without you guys, I don't know where I'd be!
    <img src=/S/pinkelefant.gif border=0 alt=pinkelefant width=20 height=20>

    Cecilia <img src=/S/hugs.gif border=0 alt=hugs width=41 height=25>

    <img src=/S/thewave.gif border=0 alt=thewave width=225 height=33>

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Top Values -- Awesome Solutions by Drew & Mark (Access 97, 2002)

    <P ID="edit" class=small>(Edited by WendellB on 12-Mar-03 09:41. The need to find the TOP N records for many different categories is a fairly common requirement. This post resulted in two different and elegant solutions, as well as some serious real-world performance problems. However the original thread got very long (see the pointer in the next paragraph), so the most significant details have been consolidated into this new thread.))</P><font face="Comic Sans MS"><big>It looks like we have a winner in the "How to Get the Top Values for Each Client" query. <!post=Here is the original thread that started it all.,225997>Here is the original thread that started it all.<!/post> Beware, lots of thought processes are displayed (and splayed) for all to see. This is my original post:</big></font face=comic>

    I have a table that lists client names and sales calls to the client (the table actually includes all contacts to a client, but I am able to filter the table to get actual sales calls because there is a field for that (yay!)).

    ClientID ClientName CallDate CallType
    1 AAA company 1/1/2001 Sales
    1 AAA company 3/1/2001 Info
    1 AAA company 5/1/2001 Sales
    2 BBB company 11/7/1999 Sales
    2 BBB company 1/2/2001 Sales
    2 BBB company 3/2/2001 Sales
    2 BBB company 11/2/2002 Info
    2 BBB company 12/2/2002 Sales

    I can use Top Values to pull up the three most recent sales calls for a particular client, but what I'd like to do is pull up the three most recent sales calls for each client, like this:
    ClientID ClientName CallDate CallType
    1 AAA company 1/1/2001 Sales
    1 AAA company 5/1/2001 Sales
    2 BBB company 1/2/2001 Sales
    2 BBB company 3/2/2001 Sales
    2 BBB company 12/2/2002 Sales

    I'd like to do this in as few queries as possible, because this is part of a "download" that goes into a backend.

    Has anyone done this before? Is there an easy way to get started? I'm really stumped.

    Cecilia <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

Posting Permissions

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