Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

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

    (Edited by WendellB on 14-Mar-03 06:13. The original thread this <!post=refers to,225997>refers to<!/post> had gotten very long, so this portion of the thread has been moved to a new thread.)

    As an aside, how long does the data dump from DB2 to Access take?
    Pat

  2. #2
    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 Question (Access)

    Hi Pat!

    When I tried to do it with the Top Values query, it never finished running (I have a two-hour threshold). Pulling the data first and then doing top values took less than a minute once I indexed three critical fields. Pulling the data only takes about five minutes (over a very slow WAN) and results in about 30,000 records.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Top Values Question (Access)

    The data pull is good, 5 minutes to go thru over a million records to get 30,000 records is not too shabby.
    There is another alternative to this solution if you are interested, it involves using an append query from DB2 to an existing access table in a particular order.

    But if you're happy with what you have got, that's cool, it's a great solution.

    This forum is unbelievable, people from all around the world helping each other, it's great !!

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    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 Question (Access)

    Hey Pat--

    I'd LOVE to hear your method! I'm always into learning new things! Even if it doesn't get into this project (tight deadline), it probably will make it into the next *g*

    Maybe a new thread? Mainframe data pulls?

    I'm not quite sure the sum total of the number of records in the db. A million just may be an understatement....

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Top Values Question (Access)

    (Edited by patt on 13-Mar-03 15:30. Change CallDate DESC to CallDate ASC in data pull from DB2)

    When you pull the data from DB2 into the Access table you would need to specify the sort sequence in this query as ID ASC and CallDate ASC.
    Then it's just a query in a query to get the desired result.
    The inside query called [qry 101 Max RefID per Customer] on my system is:
    SELECT ReferencedPersonNEW.[Referenced Person's Name], Min(ReferencedPersonNEW.RefID) AS MaxRefID
    FROM ReferencedPersonNEW
    GROUP BY ReferencedPersonNEW.[Referenced Person's Name];

    The following query uses the above query as part of it's source:
    SELECT ReferencedPersonNEW.RefID, ReferencedPersonNEW.ReferencedPersonID, ReferencedPersonNEW.[Referenced Person's Name], ReferencedPersonNEW.CallDate, [qry 101 Max RefID per Customer].MaxRefID, MaxRefID+1-RefID AS NewNo
    FROM ReferencedPersonNEW INNER JOIN [qry 101 Max RefID per Customer] ON ReferencedPersonNEW.[Referenced Person's Name] = [qry 101 Max RefID per Customer].[Referenced Person's Name]
    WHERE ((([MaxRefID]+1-[RefID])>-2))
    ORDER BY ReferencedPersonNEW.[Referenced Person's Name], ReferencedPersonNEW.RefID;

    These queries use a name and a ref ID for sorting into the correct. The test for > -2 is to get the top 3 for each refID.

    I have cut down the database and include it here. The query [qry 2 Final Query showing Order] is the one to use for a report, this query took 3 seconds to establish the result on a AMD300 PC with 64Mb of RAM utilizing 26,600 records.

    Hope you get something from it.
    Pat
    Attached Files Attached Files

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Top Values Question (Access)

    Hi Cecilia
    Ignore my last post, it was all wrong and I hope the following makes up for it and makes it clearer of what I was trying to do.

    1. Ensure that the Access table (called tblDB2DataPull) that the data pull from DB2 is going into has an Autonumber field (called SpecID).
    We need to delete all records in this table prior to the data pull.

    2. The data pull query is an append query which has an order by of ClientID and CallDate, and also has a filter to exclude records of non "Sales" type.
    Let's say the following is pulled from DB2:
    ClientID ClientName CallDate SpecID
    1 AAA company 1/1/2001 1
    1 AAA company 5/1/2001 2
    2 BBB company 11/7/1999 3
    2 BBB company 1/2/2001 4
    2 BBB company 3/2/2001 5
    2 BBB company 11/2/2002 6
    2 BBB company 12/2/2002 7

    3. Create a query (called qryGetMaxSpecID4Client) which gets the maximum value of SpecID for each ClientID. The query is:

    SELECT tblDB2DataPull.ClientID, Max(tblDB2DataPull.SpecID) AS MaxSpecID
    FROM tblDB2DataPull
    GROUP BY tblDB2DataPull.ClientID;
    We would get the following from this query:
    ClientID SpecID
    1 2
    2 7

    4. Create a query joining the table tblDB2DataPull and the query created in 3 joining them on ClientID and ordered by ClientID and SpecID.
    Only show records where MaxSpecID + 1 - SpecID < 4. This gives the top 3 for each ClientID
    nb. MaxSpecID is from the query qryGetMaxSpecID4Client and SpecID from the table tblDB2DataPull. The query would be:

    SELECT tblDB2DataPull.ClientID, tblDB2DataPull.ClientName, tblDB2DataPull.CallDate, tblDB2DataPull.SpecID, qryGetMaxSpecID4Client.MaxSpecID,
    [MaxSpecID]+1-[SpecID] AS NewNo
    FROM tblDB2DataPull NEW INNER JOIN qryGetMaxSpecID4Client ON tblDB2DataPull.ClientID = qryGetMaxSpecID4Client.ClientID
    WHERE (((qryGetMaxSpecID4Client.[MaxSpecID]+1- tblDB2DataPull.[SpecID])<4))
    ORDER BY tblDB2DataPull.ClientID, tblDB2DataPull.SpecID;
    This would then give:
    ClientID ClientName CallDate SpecID MaxSpecNo NewNo
    1 AAA company 1/1/2001 1 2 3
    1 AAA company 5/1/2001 2 2 2
    2 BBB company 3/2/2001 5 7 3
    2 BBB company 11/2/2001 6 7 2
    2 BBB company 12/2/2002 7 7 1
    You don't need the last 3 fields of this query, it's only there to show you how I did this.

    All you need to do is to run the query created in 4 above to see the Top 3, you would also use this query as the source of the report.

    Pat

Posting Permissions

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