Page 3 of 3 FirstFirst 123
Results 31 to 43 of 43
  1. #31
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    >wcrimi -- Once you add a field to your table, call it ID, make it autonumber. Then you should be able to use<



    Let me think about this. It could impact some of my import procedures also.

    I get files in a fixed format.




  2. #32
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You can add the field for your required manipulations. If you're not passing the file onto others for processing, and even if you are,
    you could delete the field after you've run whatever you need to run.

    Try it with a copy of your full data and see how the timing changes. Hopefully a lot quicker.

  3. #33
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    Quote Originally Posted by orange View Post
    You can add the field for your required manipulations. If you're not passing the file onto others for processing, and even if you are,
    you could delete the field after you've run whatever you need to run.

    Try it with a copy of your full data and see how the timing changes. Hopefully a lot quicker.
    OK, here's what happened.

    1. I could not modify my existing table by adding an ID field with autonum because it already had data in it.

    2. I created a new table, added the field, set it to autonum.

    3. I copied the data from old table to new table

    4. I ran the code below with this new table for 30k records. Note: I made a minor adjustment to the sort sequence.

    5. Here's the great news. It literally took 5 seconds to run. wooo hooo

    6. Here's the quirk. It didn't return the last 5 races for the horse. It returned the first 5 races for the horse (oldest dates not newest dates).

    Perhaps that has to do with how the code is written or perhaps that has to do with how the table is indexed on that date, but I'd way rather change code if possible. I assume, when I actually go live with this, I will have to repeat the same process on my real table where I create a new one with the new field and then copy the data over so the ID field gets populated. Is that correct?




    SELECT *

    FROM

    (

    SELECT a1.rctrack, a1.RCDate, a1.rcRace, a1.horse, a1.Date, a1.Race,

    COUNT(*) AS CategoryRank

    FROM [Running Lines ID] AS a1

    INNER JOIN [Running Lines ID] AS a2

    ON (a1.horse = a2.horse) AND (a1.Id <= a2.ID)

    GROUP BY a1.rctrack, a1.rcdate, a1.rcrace, a1.horse, a1.date, a1.race, a1.id) AS RankingQuery

    WHERE (((RankingQuery.[CategoryRank])<=5))

    ORDER BY RankingQuery.rctrack, RankingQuery.rcdate, RankingQuery.rcrace, RankingQuery.horse, RankingQuery.CategoryRank DESC;

  4. #34
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    1. I could not modify my existing table by adding an ID field with autonum because it already had data in it.
    I made a minor adjustment to the sort sequence.
    Perhaps that has to do with how the code is written or perhaps that has to do with how the table is indexed on that date,
    I will have to repeat the same process on my real table where I create a new one with the new field and then copy the data over so the ID field gets populated.
    I can add autonumber field to any table that does not already have an autonumber field -- only 1 per table.

    Note: In my query which I thought you would use/adapt.

    .....,RankingQuery.CategoryRank; <---no descending

    You set up a routine to do what you need, and it could be automated. A few seconds processing compared toan hour seems a good trade off to me.

    Zip and post your full file, and we'll set up a routine.

  5. #35
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    Quote Originally Posted by orange View Post
    I can add autonumber field to any table that does not already have an autonumber field -- only 1 per table.

    Note: In my query which I thought you would use/adapt.

    .....,RankingQuery.CategoryRank; <---no descending

    You set up a routine to do what you need, and it could be automated. A few seconds processing compared toan hour seems a good trade off to me.

    Zip and post your full file, and we'll set up a routine.

    1. I added the field fine. When I tried to set it to autonum, it refused. It said it can't do it in a table that already has data in it. So I had to set up a new empty table. That's the least of the problems. Not worried about this.

    2. The reason I put "DESC" in there was because it wasn't working the other way. The DESC did not help. It's getting the 5 oldest dates instead of 5 newest both ways. I think the ID field (auto/numb) is getting populated in the wrong order when I create the new table. I'm trying multiple ways to fix that, but it's not working. Could it be because that date is a key field (which it has to be)? Try it on the demo file, you'll see what I mean.

    3. Why do you need the full file at this stage? Besides, when I tried to zip the full file last time it exceeded the limit. Routine?

  6. #36
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Well send me 10 or 20000 records, so I can set it up to run the query I provided and tested.

    Create an empty database, import your table. Zip the new database and post.

  7. #37
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    Here it is

    I think the issue is that we are somehow including that "date" field in a sort sequence in ascending order which makes the oldest dates get ranked higher than the most recent races. This is the file with the ID field (last field). I created this table without any of the normal index fields while experimenting with some possible solutions.
    Attached Files Attached Files

  8. #38
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly is the output sort sequence you require?

    I added some indexes, and created a query (Query1)
    Attached Files Attached Files

  9. #39
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    I think you are misunderstanding the problem.

    Forget about the sort sequence of the output file. That's not an issue. I can always tweak that. It's the output data that's still an issue.

    The goal is to select every horses' 5 most recent races. The way we determine the most recent races is by the values contained in the field called Date in the table. So if a horse has "x" races, we want the 5 races with the most recent 5 dates in the Date field of the table. The current query is picking up 5 races for each horse appropriately, but it's picking up the wrong races. Instead of picking up the most recent, it's pick up the 5 oldest (basically it is picking up races from 2011, 2012 etc.. instead of late in 20014)

  10. #40
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    Eureka I found it!!!

    This: AND (a1.Id <= a2.ID)

    Should be: AND (a1.Id >= a2.ID)

    When operating in ignorance, there's something to be said for trial and error.

    Let me do some more testing, but I believe we HAVE IT!

  11. #41
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Just saw your post.

    I have been out most of the day, just been at this for the last 30 min or so. I have also solved it, with out the need for the ID field.
    I realized that a1.Horse must = a2.Horse and a1.Date<=A2.Date.

    I went back and did the exercise with a TOP5 query, then created the one with Faster in the name.

    Here is the faster one without the ID.
    Code:
    SELECT *
    FROM (SELECT a1.rctrack, a1.RCDate, a1.rcRace, a1.horse, a1.Date, a1.Race, COUNT(*) AS CategoryRank 
    FROM [Running Lines Id] AS a1 INNER JOIN [Running Lines Id] AS a2 
    ON (a1.horse = a2.horse) AND (a1.date<= a2.date) 
    GROUP BY a1.rctrack, a1.rcdate, a1.rcrace, a1.horse, a1.date, a1.race
    )  AS RankingQuery
    WHERE (((RankingQuery.[CategoryRank])<=5) )
    ORDER BY RankingQuery.rctrack, RankingQuery.RCrace, RankingQuery.horse, RankingQuery.CategoryRank;
    Good luck with your project.

    I have a feeling that you will get more than 5 records with the method you mentioned.

    PS: I'd like to see what the execution/run time is for your large table.
    Also, you can move the fields in the ORDER BY line in the query above to get the sequence you want.
    Attached Files Attached Files
    Last edited by orange; 01-02-2015 at 07:26 PM. Reason: spelling tablename

  12. #42
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    Quote Originally Posted by orange View Post
    Just saw your post.

    I have been out most of the day, just been at this for the last 30 min or so. I have also solved it, with out the need for the ID field.
    I realized that a1.Horse must = a2.Horse and a1.Date<=A2.Date.

    I went back and did the exercise with a TOP5 query, then created the one with Faster in the name.

    Here is the faster one without the ID.
    Code:
    SELECT *
    FROM (SELECT a1.rctrack, a1.RCDate, a1.rcRace, a1.horse, a1.Date, a1.Race, COUNT(*) AS CategoryRank 
    FROM [Running Lines Id] AS a1 INNER JOIN [Running Lines Id] AS a2 
    ON (a1.horse = a2.horse) AND (a1.date<= a2.date) 
    GROUP BY a1.rctrack, a1.rcdate, a1.rcrace, a1.horse, a1.date, a1.race
    )  AS RankingQuery
    WHERE (((RankingQuery.[CategoryRank])<=5) )
    ORDER BY RankingQuery.rctrack, RankingQuery.RCrace, RankingQuery.horse, RankingQuery.CategoryRank;
    Good luck with your project.

    I have a feeling that you will get more than 5 records with the method you mentioned.

    PS: I'd like to see what the execution/run time is for your large table.
    Also, you can move the fields in the ORDER BY line in the query above to get the sequence you want.

    Thanks. Not using an ID field would be even better.

    I'm sort of burnt out by this. So I'll bookmark this new technique and take a look at it in a few days. I also want to revisit this using the date technique from earlier in the thread. I have an application where that would be perfect.

    The one I am using now IS working. It takes about 3-4 seconds on a database that has since grown to 75K records. So even if I eventually take it to 500K records or more and it takes a minute or two, that would still be awesome.

    I hope to eventually get to the point where I can solve these issues on my own. This was a step in the right direction. But it takes time when you are trying to self educate.

    Thanks for all the help.

  13. #43
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    No problem. Happy to help. Good luck.

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Group and Sum Records in a Query
    By majoh60 in forum Queries
    Replies: 7
    Last Post: 06-15-2013, 10:36 PM
  2. Assigning records to a group.
    By gemadan96 in forum Access
    Replies: 12
    Last Post: 10-30-2012, 03:43 PM
  3. Counting Detail Records by Group
    By Paul H in forum Reports
    Replies: 7
    Last Post: 10-21-2011, 02:53 PM
  4. Print records by group
    By lizzywu in forum Reports
    Replies: 1
    Last Post: 10-20-2011, 10:31 AM
  5. Trying to sum a group of records not working
    By shelbsassy in forum Reports
    Replies: 0
    Last Post: 04-10-2011, 07:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums