Page 1 of 4 123 ... LastLast
Results 1 to 15 of 58
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    I think this is a Top Values question.... (Access 2002)

    Hi All!

    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.


    TIA!

    Cecilia :-)

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: I think this is a Top Values question.... (Access 2002)

    Top Values (you can set the value to 3) should do the trick for you if you restrict the client to a single name, but that means you would need to run the query multiple times in some sort of loop and append the results to a temporary table. In your case that may work, as you are gong to download the data anyhow. You might look at the idea of a subquery as well - I haven't tinker with your situation, but it seems like a possibility. I did play with a GroupBy query, but that didn't seem to hold much promise.
    Wendell

  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: I think this is a Top Values question.... (Access 2002)

    Hi Wendell!

    I don't think running the query multiple times will work....I kinda really contrived the example to be a lot simpler than what I'm trying to do, but the number of customers in my table is in the thousands (not to mention that I have to select only customers that were active since 1999 in the query, further mucking up the works). I've also tried playing with the group by thing, it didn't seem to help, I can still only do a single customer.

    Any ideas on how I would set up a subquery for this? This is one idea that I saw elsewhere on this board, but I couldn't figure out how to start it.

    Another idea I had was to set up a cur field (to count down the current record for each customer), but I haven't come up with a formula for that either. Maybe I'm just pooped and fresh out of ideas <img src=/S/basket.gif border=0 alt=basket width=58 height=52>

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: I think this is a Top Values question.... (Access 2002)

    Hi Cecilia,
    I seem to be stuck on this at the moment - it seems to me it's a trick that solves this problem in SQL, but I haven't been able to find it. I'll keep digging, but perhaps someone else can come up with it. I played with a subquery within a subquery, trying to get three - it's fairly easy to get the top (most recent) record for each customer but that's as far as I get.
    Wendell

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

    Re: I think this is a Top Values question.... (Access 2002)

    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

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

    Re: I think this is a Top Values question.... (Access 2002)

    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>

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

    Re: I think this is a Top Values question.... (Access 2002)

    >>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!)

    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. <<

    Well, it's late, but I just had to tell you, I read your post and was laughing completely out loud! It's just way TOO familiar!

    I can tell you right off that there is no real primary key for this table. Maybe it should, but hte bigger point is that this is from a mainframe database that I have no control over (I work for the gov't, I'm used to having no control over things *grin*).

    I'm going to try this first thing tomorrow, if I get anywhere I will post results (it sounds exactly like what I'm looking for, whether I can do it or not is the bigger question), but in any case, I severely need to thank you ALL for your help!

    Good nite (for now),

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

    Re: I think this is a Top Values question.... (Access 2002)

    I hear ya on the legacy stuff. Well, government run/legacy....same thing! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>.

    Here's the SQL for the same data, I copied tblCalls to tblCalls2 and removed the PKey (Autonumber) field. This SQL does the same thing then:

    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 'identifies', then you may be able to make a stronger 'key'.

    Have fun! (P.S.--How's the bay area? I used to live in Alameda (in the US NAVY....worst...I mean best 6 years of my life!) <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: I think this is a Top Values question.... (Access 2002)

    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> both to Drew and Mark - for what ever reason I had a brain freeze yesterday. It's a reasonably common problem, but more than a bit tricky for users to figure out. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>
    Wendell

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

    Re: I think this is a Top Values question.... (Access 2002)

    Hi Drew--

    Now that I've had a good night's sleep (sorta--see below), let me see if I understand....

    The subquery calls the top three dates from the table, at the point where the subquery ID matches the main query's ID. So then the main query just calls the data that I want out of that result (limiting the call type to Sales)? That's pretty cool. <img src=/S/starstruck.gif border=0 alt=starstruck width=15 height=15>

    I don't think there are going to be any identical dates for any particular ID, simply because the mainframe system doesn't allow it, so technically, if you have a match between ID and date, that should be enough. Having the Type in there is definitely an added protection and should be plenty.

    I'm running the query against DB2 now...could be waiting forever for this big thing to happen....

    In the meantime, Off Topic: I actually "live" in Alameda--And I love it! They are in the process of converting the naval base to condos. Hope that's not going to ruin too many of your ... um ... memories. Right now I'm at our headquarters, in DC. Over the weekend we were hit with about 18" of snowfall, bringing this city pretty much to a halt (on Sunday the metro was running at pretty much 2-hour intervals, yesterday they said 1-hour, today the gov't is still closed). The only bright spot is that they've stopped with the constant Terrorist Alerts on TV. (Plus I get an extra day on the two-tier application I'm supposed to deliver start to finish in three weeks....YAY!)

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

    Re: I think this is a Top Values question.... (Access 2002)

    Wendell, that goes for all three of you, <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> <img src=/S/king.gif border=0 alt=king width=21 height=22>

    It may be just a simple trick to all you guys, but for those of us struggling to learn in the trenches, it's a life saver!

    <img src=/S/salute.gif border=0 alt=salute width=15 height=20> <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15> <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

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

    Re: I think this is a Top Values question.... (Access 2002)

    On topic, the Sales is a criteria in the subquery, because you said you wanted the last three 'SALES' calls. If that criteria wasn't there, you would get the last three calls, regardless of their type.

    Off topic....oh good ol' Alameda NAS. I was stationed on the USS Carl Vinson. I'll have to admit the 'Webster Street Crawls' were definitely fun for a 'bored out of his gourd' sailor. I can't remember my old address, but I lived a few blocks behind the tavern 'Lincoln's Address'.

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

    Re: I think this is a Top Values question.... (Access 2002)

    Off Topic Again: You picked right time to visit DC - our biggest snowstorm since '96! --tho be advised, this place can come to halt if there's snow flurries, let alone a full-scale snowstorm.... I also work for gov't - on "Snow Days" like this (as a lowly "non-essential" personnel) I get to stay home and, when not out playing in the snow, get to work on some of my Access programs that I don't have time to work on at "work" due to other "priorities" and "hot projects" (& they're ALL "hot").... <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

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

    Re: I think this is a Top Values question.... (Access 2002)

    Ugggh, I'm originally from NY, and let me tell you, we'd never get stopped by these little flurries! My boss (same org, but NY office) made me drive home in 4 feet of snow once....LOLOL Anyway, bad weather seems to follow me. My first year in SF was their very first snow in 40 years. Now I get here, and the whole city stops at my feet again! My friends here didn't seem worried by the snow so much as worried about losing their parking spaces *g*

    This time around, I'm thankful for the extra day it gave me. I'm still not caught up, but at least I had some extra time.

    Drew: Do you know if Lincoln's Tavern is still there? I'm on the south side of the island, away from the tubes. I hear the north side is much more happenin' ;-)

    On topic: The query works fine when it's local. When it's over DB2, it completely stalls. I kind of "fluffed" the data for the DB2 fill, so I'm not even going to bother with it for that, but I still have a SQL Server portion that I'm hoping it will work on tomorrow.

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

    Query Results

    Well, I tried running the query on my DB2 source....No luck. Just to verify that I was doing it properly, I ran it on a local source. It was so slow, I went to lunch, came back, and still waited a few for it to run. I was hoping that at the very least it would run on my SQL97 datasource, but with this slow result locally, I don't think it'll be much better.

    Oh, well. Two weeks to go ;-) If anyone can think of a better way to do this, I certainly would appreciate it!

Page 1 of 4 123 ... LastLast

Posting Permissions

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