Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142

    Export Data to an Existing Excel Spreadsheet

    Here's my problem. I have a large database. I occasionally want to run a query on the data that ranks certain records within several groups. The query works fine, but it can literally take hours to run. It's at the point where that's no longer a viable solution. So what I was thinking of doing instead of was exporting the data to an Excel spreadsheet where I can use a superior and quicker ranking method. It takes a few minutes for me to export the data to a spreadsheet, add in my ranking code, massage it a little, and then import the new data back to Access. It's not that time consuming, but it's still all manual. I would way rather run a macro that would export the data to a preexisting spreadsheet with the code already there and then import it in another step. Better yet I'd like to have a macro that does it all. Unfortunately, I'm not a VBA guy and that seems to be the only way to get the data into a pre existing spreadsheet. I'm literally starting from zero here. I don't even know how to go about creating this VBA object let alone getting to work. Any help would be appreciated.





    Here's the Access ranking code I am using now if anyone has any ideas for making it faster.

    SELECT RS1SORT.RCTRACK, RS1SORT.RCDATE, RS1Sort.Horse, RS1Sort.Date, (select Count(*) from RS1 where Date < RS1Sort.Date
    and
    RCDATE = RS1SORT.RCDATE
    and
    RCTRACK = RS1SORT.RCTRACK
    and
    Horse=RS1Sort.Horse
    ) +1 AS rank
    FROM RS1 AS RS1Sort
    ORDER BY RS1Sort.horse;

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Possibly have to use Excel automation. Many examples. Here is one source http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

    However, not sure Access can automate an xlsm workbook.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    However, not sure Access can automate an xlsm workbook.
    If you mean at least write data to the workbook, then yes. However, I don't know if there are any restrictions on this, such as what happens if there are macros. Works in a file with a vba project at least, but my file has nothing else in it.

    EDIT:
    Level of difficulty would also depend on whether each subsequent push to Excel replaces existing data or appends to it.
    Last edited by Micron; 09-06-2018 at 07:12 PM. Reason: spelin and gramur
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    swas is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2017
    Posts
    32
    G'day wcrimi,

    I avoid using sub queries in Access as I find them v e r y slow unless a small table.

    Shift your sub query into a separate query altogether, link the two, and I'd be pretty confident your speed will be substantially better.

    Your present query has to run the sub query for every record on RS1SORT.

    I can't help thinking going the Excel path while workable is probably not necessary, and also adds complexity.

    Hope this helps.

    swas

  5. #5
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    41
    Post a sample of the data and I will see what I do can do.

    My personal preference is to do everything in Access and then copy the results of a query with not too many rows over to Excel for analysis if necessary (for quick and easy simple things like running totals). Going back and forth, especially programmatically, is a bad idea in my opinion. Access is so much more powerful.

    If you have a large amount of data, Excel is pretty much useless. That is what Access is for.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you can try joining the table to itself with a modified join on the date field. This cannot be written in the query window, only in SQL

    Code:
    SELECT 
       RS.RCTRACK,
       RS.RCDATE, 
       RS.Horse, 
       RS.Date, 
       Count(RC.Horse)+1 AS Rank
    
    FROM
       RS1 AS RS INNER JOIN RS1 AS RC ON
         RS.RCDATE = RC.RCDATE
         AND RS.RCTRACK = RC.RCTRACK
         AND RS.Horse=RC.Horse
         AND RC.Date<RS.Date
    
    GROUP BY
       RS.RCTRACK,
       RS.RCDATE, 
       RS.Horse, 
       RS.Date
    
     ORDER BY RS.horse;
    The code in Red is the modified join. There is a cheat to create it. In the query design window, create an ordinary join, then go into the sql window and change the = to a <. If you go back into query design you will be told the join cannot be displayed. Providing you don't modify anything you can return to the sql window and the join will still be there. If you do modify the query, go to the sql window and modify the join again.

    Note that Date is a reserved word and should not be used for field names

    Performance will also be affected by lack of indexing, so ensure the fields used in the join are all indexed

  7. #7
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    Thanks everyone.

    Let me read though all the comments. This should give me some things to try. I may have some more questions.

  8. #8
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    Here's an example of what the data looks like after the query. Before the query is run, it's the first 4 fields without the rank. The number in each group can vary from 1 to 4.


    RCTRACK RCDATE Horse Date rank
    ELP 9/3/2018 A P's Gusto 6/21/2018 1
    ELP 9/3/2018 A P's Gusto 8/10/2018 4
    ELP 9/3/2018 A P's Gusto 7/7/2018 2
    ELP 9/3/2018 A P's Gusto 8/4/2018 3
    ELP 9/3/2018 Accent of Gold 8/2/2018 3
    ELP 9/3/2018 Accent of Gold 6/29/2018 1
    ELP 9/3/2018 Accent of Gold 8/10/2018 4
    ELP 9/3/2018 Accent of Gold 7/8/2018 2
    DMR 9/3/2018 Acclimate 5/11/2018 1
    DMR 9/3/2018 Acclimate 7/27/2018 2
    DMR 9/3/2018 Acclimate 8/17/2018 3
    DMR 9/3/2018 Achieved 7/25/2018 1
    DMR 9/3/2018 Achieved 8/17/2018 2
    DMR 9/3/2018 Activated 9/2/2017 3
    DMR 9/3/2018 Activated 10/1/2016 1
    DMR 9/3/2018 Activated 12/4/2016 2
    DMR 9/3/2018 Activated 10/6/2017 4
    PRX 9/3/2018 Afleet Tizzy 4/10/2018 3
    PRX 9/3/2018 Afleet Tizzy 2/12/2018 2
    PRX 9/3/2018 Afleet Tizzy 1/27/2018 1
    PRX 9/3/2018 Afleet Tizzy 5/1/2018 4

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Here's an example of what the data looks like after the query.
    is that your query or the one I provided? If the latter - was it faster? (which was the point of the thead)

    Also what happens if you have the same horse running two different races on the same day at the same track? I appreciate you are using a date column but I don't understand how that is relevant to your data other than (perhaps) providing a unique ID in some way - and before you say I can see that situation exists, your first horse appears to have undertaken 4 races for example, just don't understand why you have 4 dates spread over 6 months

  10. #10
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    >is that your query or the one I provided? If the latter - was it faster? (which was the point of the thread)<

    That's my own query. Someone above asked to see data. I didn't get a chance to make your modifications yet.

    >Also what happens if you have the same horse running two different races on the same day at the same track?

    That's theoretically possible, but I don't think I've seen in it in decades. Way more likely is a horse entered on the same day at 2 different tracks.

    >I appreciate you are using a date column but I don't understand how that is relevant to your data other than (perhaps) providing a unique ID in some way - and before you say I can see that situation exists, your first horse appears to have undertaken 4 races for example, just don't understand why you have 4 dates spread over 6 months<

    The ranking field is a way of me quickly knowing which race is the most recent, 2nd most recent, 3rd most recent, or 4th most recent in the horse's record because there are subsequent queries that do different things with related data depending on that sequence. I might ask, give me his next to last race. I use the rank to find it quickly.



  11. #11
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    41
    The fastest way to calculate rank is to run a VBA function. It would run in seconds even if you had tens of thousands of horses with this table structure:

    RCTRACK (Text) PrimaryKey
    RCDATE (Date/Time) PrimaryKey
    Horse (Text) PrimaryKey
    xDate (Date/Time) PrimaryKey
    rank (number Integer)

    I populated the first four fields with your sample data (leaving rank blank). I changed your 4th field "Date" to "xDate" because you should not use reserved words as field names, and I don't know what that date represents. When you implement this, change "xDate" to something descriptive. The VBA code does not use this field (other than two comments). It only uses the primary key which can have any name for "xDate" since it is ranking the data based on the first three fields of the primary key.

    I can see from your sample data that each row has a unique combination of RCTRACK, RCDATE, Horse, and "xDate", which is why I created a table with a primary key that consists of those four fields. This is why I have no query at all in the code. The existence of the primary key when going through the rows is effectively the same thing but it is extremely fast.

    If you don't already have a module, create one. Copy the VBA code, compile, and save.

    Code:
    Public Function CalcRank()
    'rank each set of RCTRACK, RCDATE and Horse in ascending order of xDate (1,2,3,...)
    
    Dim db As Database
    Dim HorseRst As Recordset
    
    Dim CurrentRCTRACK As String
    Dim CurrentRCDATE As Date
    Dim CurrentHorse As String
    
    Dim cont As Boolean
    Dim RankNum As Integer
    
    Set db = CurrentDb()
    Set HorseRst = db.OpenRecordset("horse data")
     HorseRst.Index = "PrimaryKey"       'RCTRACK, RCDATE, Horse and xDate
    
    CurrentRCTRACK = HorseRst![RCTRACK]
    CurrentRCDATE = HorseRst![RCDATE]
    CurrentHorse = HorseRst![Horse]
    
    cont = True
    RankNum = 1
    
    Do While cont           'loop once for each row in order and reset rank to 1 for each unique combination of RCTRACK, RCDATE and Horse
      HorseRst.Edit
      HorseRst![rank] = RankNum
      HorseRst.Update
      
      HorseRst.MoveNext
      
      If HorseRst.EOF Then      'done
        cont = False
      Else
        If HorseRst![RCTRACK] <> CurrentRCTRACK Or HorseRst![RCDATE] <> CurrentRCDATE Or HorseRst![Horse] <> CurrentHorse Then
          CurrentRCTRACK = HorseRst![RCTRACK]       'new data set
          CurrentRCDATE = HorseRst![RCDATE]
          CurrentHorse = HorseRst![Horse]
          RankNum = 1                               'start ranking again for the next horse etc.
        Else
          RankNum = RankNum + 1
        End If
      End If
    Loop
    
    HorseRst.Close
    db.Close
    MsgBox ("done")
    
    End Function
    Then, create a macro with one line:
    RunCode CalcRank()

    When you want to calculate the rank, run the macro. I included the msgbox "done" at the end just so that you know you did run the macro. It runs so fast that without the msgbox, you might not know you ran it! LOL

    If you change any of the data, you will need to run the macro again. The code will not care if there is already a rank populated. It will overwrite whatever is there (blank or not).

  12. #12
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    Thanks.

    I'll work on this over the weekend. I have never created a VBA function. I do everything with queries. So I'll have to get up to speed on that first. This could open some new doors for me.

  13. #13
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    @JSR - you raise an interesting concept, I'll try it when the opportunity arises

    but it also reminded me of a simple fact - there are many out there who believe they need to see everything in a list before deciding which record they actually want to see- even if that means a list of a million records (basically an Excel mentality). But comments like this

    The ranking field is a way of me quickly knowing which race is the most recent, 2nd most recent, 3rd most recent, or 4th most recent in the horse's record
    I might ask, give me his next to last race
    indicate that wc is interested in a particular horse. If there are 1000 horses in the data set then (simplistically) that reduces the number of records to 0.1% of the original dataset - a completely different scenario for performance

    The same applied to time - which horse was the fastest over the last 12 months? - OK all the horses, but over a limited time period

  14. #14
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    Actually, I am looking for each of those records during the stream of queries that are running, but the processing is different depending on whether it's a 1, 2, 3 or 4 record.

  15. #15
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    Quote Originally Posted by Ajax View Post
    you can try joining the table to itself with a modified join on the date field. This cannot be written in the query window, only in SQL

    Code:
    SELECT 
       RS.RCTRACK,
       RS.RCDATE, 
       RS.Horse, 
       RS.Date, 
       Count(RC.Horse)+1 AS Rank
    
    FROM
       RS1 AS RS INNER JOIN RS1 AS RC ON
         RS.RCDATE = RC.RCDATE
         AND RS.RCTRACK = RC.RCTRACK
         AND RS.Horse=RC.Horse
         AND RC.Date<RS.Date
    
    GROUP BY
       RS.RCTRACK,
       RS.RCDATE, 
       RS.Horse, 
       RS.Date
    
     ORDER BY RS.horse;
    The code in Red is the modified join. There is a cheat to create it. In the query design window, create an ordinary join, then go into the sql window and change the = to a <. If you go back into query design you will be told the join cannot be displayed. Providing you don't modify anything you can return to the sql window and the join will still be there. If you do modify the query, go to the sql window and modify the join again.

    Note that Date is a reserved word and should not be used for field names

    Performance will also be affected by lack of indexing, so ensure the fields used in the join are all indexed

    This code flew, but it dropped off one of the records. It seemed to be dropping off oldest record in each group. The #1 rank record. I'm getting 2, 3 and 4 for each group.

    I tried changing the modified join to of RC.Date<RS.Date to RC.Date<=RS.Date.

    That picked up the missing record, but now they are ranked 2 through 5 instead of 1 through 4.


Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 07-14-2018, 07:22 AM
  2. Replies: 1
    Last Post: 02-02-2015, 04:08 PM
  3. Importing Excel spreadsheet and appending it to an existing table
    By Access_Novice in forum Database Design
    Replies: 3
    Last Post: 08-21-2014, 07:01 AM
  4. export data to existing excel file
    By joshynaresh in forum Import/Export Data
    Replies: 7
    Last Post: 01-27-2014, 10:57 PM
  5. Replies: 1
    Last Post: 08-12-2010, 10:04 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