Results 1 to 8 of 8
  1. #1
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91

    how hard to insert rows where values exist, keep sort order, and assign values to new rows?

    Hello,



    The scenario is this... if this is too hard to implement, I probably won't be given the go-ahead to develop. BUt, if it is not too hard to program, perhaps I can do it fast and get a huge improvement in preparation time of a report. I have a fair amount of access skill, and a little VBA...

    Report is this (these steps currently being done in Excel, with over a thousand human inputs - process was NOT designed by yours truly ) Goal is to prepare this data prior to being exported to Excel for final formatting, arrangement, presentation etc.

    Relevant steps:
    (Spreadsheet contains a listing of all records keyed by each person, with a date/time stamp. However, there is no time-stamp for when the record was begun, unlike a call-log. The below steps are an effort to see how long it took to do each individual record)

    Preparer filters for rows where the time-gap between two rows is greater than 45 min.
    one then inserts two empty rows between said rows.
    then the Word "Project" is pasted into several columns of that row. the goal is to associate outlier time-gaps with "Project" time or saying "I was working on something that can't be tracked in this manner, or was out to lunch or the dentist"
    One must then insert the date/time from the above row into the first "project" row, and insert the date/time from the next row of data, into the above row.

    Click image for larger version. 

Name:	project_time_redacted.JPG 
Views:	34 
Size:	26.8 KB 
ID:	34072

    Once that new timestamp has been added to the project time rows, the gap now becomes associated with them, and not the person who apparently left the office for around an hour, and returned.

    My desire is ultimately to revamp this entire process, cuz it stinks. But for now, if I can determine how to prevent a person from needing to first, input 100+ pairs of empty rows, grab the preceding or following date/time as prescribed, then this would cut down on a huge potential for human error, and take a lot of the preparation time out of this report.

    Please ask clarifying questions if needed. Thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use macro recorder to record the manual execution of steps. Modify code to run from a button click. That's how I develop new code for Excel VBA. However, I don't expect the modifying to be quick nor simple in this case.

    However, if you want to do this in Access VBA, certainly possible and maybe easier.
    Last edited by June7; 05-17-2018 at 05:47 PM.
    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
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You mention spreadsheet =Excel everywhere, but you post this in the Access forum. Would you please provide some more info? Do you have an Access db where you import or link the spreadsheet into? Can you show us a sample of the spreadsheet with some dummy data? You can definitively do what you ask in Access, but not without more info from you.

    Cheers,
    Vlad

  4. #4
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    THanks both of you. I am attempting to replace a process in excel with vba in Access... sorry, I thought that was assumed (I even mentioned that, "I am fairly skilled in access")

    In terms of more detail, I cannot provide the information but I did include a screenshot above...

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    When I asked for more detail I was trying to save me some time to come up with field and table names and to save you some time when implementing the solution. Please have a look at the attached sample file (look at the form frmFillGaps), it adds the "dummy pairs in a temporary table from where you can review them and add them to your original one of even better combine them using a union query into the final result. You will need to adjust the code to fit your field and table names.

    Cheers,
    Vlad
    Attached Files Attached Files

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    rdougherty, have you had a chance to look at the sample file?

    Cheers,
    Vlad

  7. #7
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    Thank you for the sample database.

    While I haven't plugged it into my data just yet, it does appear to accomplish most of what I am needing.

    Two things:

    The field values can all be set to the string "Project" rather than just one field. Secondly, I would feel more comfortable if we could assign a sequence number to the new rows of "project" data, where it increments with all the rest of the table.

    I am concerned the duplicated timestamp from one row to the next will cause access to sort them differently, depending on the query run. I want something permanently solid to sort on.

    So:

    [Normal Record] = 01
    [Normal Record] = 02
    [Normal Record] = 03
    [Project record (inserted)] = 04
    [Project record (inserted)] = 05
    [Normal Record] = 06
    [Normal Record] = 07
    [Normal Record] = 08
    [Project record (inserted)] = 09
    [Project record (inserted)] = 10

    THANKS!

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    When you "plug" your own data in, just make sure all the fields you need to be "Project" are added to the code. As for the sequence part I don't think you can really do it as you show here; my approach was to create a separate temporary table independent of your "real" data for the inserted "dummy"records and then to put the two together using a union query, I am sure that in the resultant query you can find a way to sort in such a way as to assure that the timestamps fall in the right order. Once you do you can create a new append query based on it that could add all the records to a final table with an autonumber to give you the incremental ID you want.

    Cheers,
    Vlad

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 10-11-2016, 03:42 PM
  2. Replies: 6
    Last Post: 02-14-2015, 05:40 PM
  3. Replies: 1
    Last Post: 02-06-2013, 12:18 PM
  4. Sort order with null values
    By Alsail77 in forum Access
    Replies: 23
    Last Post: 08-27-2012, 05:04 PM
  5. Replies: 2
    Last Post: 08-28-2011, 06:06 AM

Tags for this Thread

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