Results 1 to 11 of 11
  1. #1
    Eremit is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    5

    Copy data for a specific year and paste them into a new year including linked data

    Hi


    Simplyfied I have the following structure and dependicies in my access database:

    - client
    - tasks
    - year
    - a) places of activity b) further details
    For each year I record additional info which apply to a specific year. The places of activities might change from year to year and the numer of different places is not fix. Not to have too many columns in the year table I have split further details, which are not applicable for all tasks/year into a separate table, eg if outsouced, than following data are required.

    For every new year, I want to copy prior year and paste it into the same tables with a new year referece. Of course some of the fields with variable info would remain empty. I have prepared an adding query where I select all entries from prior year and add them with new year info.

    However I am struggling with also copy the linked data in places of activity and further details and paste them with the new link to the new year.

    Does anybody have an idea?
    I think this a task for a VBA programming. But maybe it is even simpler to resolve and a query or multiple queries would resolve it as well.
    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You don't copy paste,
    You make an append query to pull the data from 1 year,add 1 ,then append to the target table.
    Make a form,add a text box to enter the year, txtYr.
    the query,qaAddNewYear, would use this text box to pull that data,
    select * from table where year = forms!myForm!txtYr

    then set the query to append, but instead of adding the year field,make another field to add:
    forms!myForm!txtYr+1

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    First thing is that "Year" is a reserved word and a built-in function and shouldn't be used as an object name.

    Next, having a table for each year is not a normalized structure.


    For every new year, I want to copy prior year and paste it into the same tables with a new year referece
    You should have one table with a field for the year. (all data for each year in 1 table.)
    This is called normalizing the tables.


    Adding a new tables for each year means you have to create new forms/queries/reports or modify forms/queries/reports.... what a PITA!

  4. #4
    Eremit is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    5
    Thanks,
    I am not struggling with adding complete prior year entries with a new year. My problems arise with the additional info to a specific year, referenced in a separate table, which should also be created, but referencing the new year id number.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Still cannot visualize what you are after. Can you post the dB or a picture of the relationship window?

  6. #6
    Eremit is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    5

    Open datasets for a new year

    I have prepared a simplyfied database with a work around. However I am not 100% sure it is working without any error.
    Tab itr consists of annual information to a task.
    Tab deadlines consists of further data of a year whereas the number of dependent records per year amount from 1 to 26 max.
    Creating records for a new year it should take over part of the info from prior year and create also new records with deadlines for all new entries in itr.
    For the workaround I have added an additional field with the itr id of the year which needs to be copied and works as follows:
    add itr for a new year (basis a prior year) and add prior year itr id to itrold
    add deadlines for a new year (basis old year) and add prior year itr id to itrold
    update deadlines replaces itr related itr where itrold are the same.
    As the tables have principally a 1:n relation I am not convinced this procedure would also work with much more datafields and records.
    Attached Files Attached Files

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Here is a site that shows how to duplicate main form records and sub form records. But you don't have/aren't using forms.
    I modified the code.....

    You didn't specify what fields you wanted to duplicate, so I picked a couple.
    Attached Files Attached Files

  8. #8
    Eremit is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    5

    Smile

    Wow, Great solution and exactela what I was looking for.
    I hope I will find out where to adjust the formula to select the right fields to copy.
    Thanks a lot Steve.

  9. #9
    Eremit is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    5
    Unfortunately I have follow-up issues, which I did not manage do include in the module for my mor complex database:
    tab itr:
    how to add further fields to be copied? eg. GDS where GDS is yes/no or employee, where employee is a number. I have tried this with adding select records, but that didn't work. I have added those fields in the table
    how to define different values in a new year, eg checkbox completed always "no" in a new year. I assume that feilds which need to be empty in a new year simply do not need to be included in the selection.
    tab deadlines:
    how can the deadline date of the copied record be increased by one year in the new year. Rem. the year of the deadline usually is also the following year itr.newyear.
    Thanks

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Eremit,

    Unfortunately I have follow-up issues, which I did not manage do include in the module for my mor complex database
    It appears that you are designing as you encounter things--not a good strategy. That is, you don't have any documented requirements or a clear description of what you are trying to do. You would do yourself a major favor if you would write a clear description of the business and processes you are trying to automate. Start at the 30,000 foot overview, and gradually add more detail. Review your description/facts with others to ensure it is clear. Adjust as necessary to improve meaning/understanding.

    You wouldn't build a house without blueprints and a plan---database is no different.

    Good luck.

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In addition to orange's comments, providing accurate, complete names (table names, field names, form names) makes it easier to provide good answers.


    how to define different values in a new year, eg checkbox completed always "no" in a new year. I assume that feilds which need to be empty in a new year simply do not need to be included in the selection.
    This is correct.



    how to add further fields to be copied? eg. GDS where GDS is yes/no or employee, where employee is a number. I have tried this with adding select records, but that didn't work. I have added those fields in the table
    Are "GDS" or "employee" main table fields?
    What are the actual field names?
    See below

    tab deadlines:
    how can the deadline date of the copied record be increased by one year in the new year. Rem. the year of the deadline usually is also the following year itr.newyear.
    See below



    I thought I had added enough comments that you could figure out what to do. You really NEED to understand what the code does..

    (this is) Below:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub cmdDupe_Click()
    'On Error GoTo Err_Handler
    'Purpose:   Duplicate the main form record and related records in the subform.
    
        Dim db As DAO.Database
        Dim s As DAO.Recordset  'source record set (year to duplicate)
        Dim r As DAO.Recordset  'recordset to add new records to
        Dim r2 As DAO.Recordset  ' recordset to check number child records
        Dim strSql As String    'SQL statement.
        Dim lngID As Long       'Primary key value of the new record.
        Dim iNewYear As Integer
        Dim RC As Integer  ' record count
        Dim TaskID As Long  ' for new record
        Dim NewITR_PK As Long
    
        Set db = CurrentDb
    
        'Save any edits first
        If Me.Dirty Then
            Me.Dirty = False
        End If
    
        '====== Main table records ===============
        'select main table records to duplicate
        ' this SELECT query is where you would ADD fields you want to be duplicated from the itr table
        strSql = "SELECT itr.itrID_PK, TaskID_FK, itr.TaskYear, itr.EMPLOYEE"     ' <<<<---- Change EMPLOYEE to your field names
        strSql = strSql & " FROM itr"
        strSql = strSql & " WHERE itr.TaskYear = " & Me.cboYear & ";"
        '    Debug.Print strSql
        Set s = db.OpenRecordset(strSql)
    
        'recordset to add records to
        Set r = db.OpenRecordset("itr")
    
        'Make sure there is a record to duplicate.
        If Not s.BOF And Not s.EOF Then
            s.MoveLast
            '        Debug.Print s.RecordCount
            s.MoveFirst
    
            iNewYear = Me.cboYear + 1
    
            'Duplicate the main record: add to form's clone.
            Do While Not s.EOF
    
                TaskID = s!TaskID_FK
                '            OldITR_PK = s!itrID_PK
    
                With r
                    .AddNew
                    !TaskID_FK = TaskID
                    !TaskYear = iNewYear
                    !EMPLOYEE = s!EMPLOYEE    '  <<<<---- Change EMPLOYEE to your field names
                    'this is to link to the child records
                    'Save the primary key value, to use as the foreign key for the related records.
                    NewITR_PK = CLng(r!itrID_PK)
                    .Update
    
    
                    '================= CHILD table records =====================
                    '*** Now Duplicate the related records: append query.
    
                    'check to see if there are child records to duplicate
                    strSql = "SELECT deadlines.itrID_FK FROM deadlines WHERE itrID_FK = " & s("itrID_PK") & ";"
                    '                Debug.Print strSql
                    Set r2 = db.OpenRecordset(strSql)
                    If Not r2.BOF And Not r2.EOF Then
                        r2.MoveLast
                        RC = r2.RecordCount
                    End If
                    r2.Close
    
                    If RC > 0 Then
                        'child records found.... duplicate them
                        strSql = "INSERT INTO deadlines ( itrID_FK, canton, deadline, dteCompleted )"
                        strSql = strSql & "SELECT " & NewITR_PK & " As NewID,  deadlines.canton, " & DateAdd("y", 1, deadlines.deadline) & ", deadlines.dteCompleted"
                        strSql = strSql & " FROM deadlines"
                        strSql = strSql & " WHERE deadlines.itrID_FK = " & s("itrID_PK") & ";"
                        '                    Debug.Print strSql
                        db.Execute strSql, dbFailOnError
                    Else
                        MsgBox "Main record duplicated, but there were no related records."
                    End If
                    '================= end  CHILD table records =====================
    
                End With
                s.MoveNext
            Loop
    
            '====== end Main table records ===============
        End If
    
    Exit_Handler:
        'clean up
        r.Close
        s.Close
        Set r = Nothing
        Set r2 = Nothing
        Set s = Nothing
        Set db = Nothing
    
        MsgBox "Done - Duplicated records of " & Me.cboYear & " for new year of " & iNewYear & "."
    
        Exit Sub
    
    Err_Handler:
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
        Resume Exit_Handler
    End Sub
    If you don't know what the DateAdd() function does, check HELP....

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

Similar Threads

  1. Replies: 3
    Last Post: 06-13-2016, 03:26 PM
  2. Replies: 3
    Last Post: 06-22-2015, 06:36 AM
  3. Replies: 3
    Last Post: 03-19-2015, 02:11 PM
  4. Querying specific weeks data within any given year?
    By McArthurGDM in forum Queries
    Replies: 1
    Last Post: 07-30-2014, 02:02 PM
  5. Dsum data between to dates for this year and last year
    By sdel_nevo in forum Programming
    Replies: 1
    Last Post: 06-13-2013, 06:48 AM

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