Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2007

    Post Unanswered: Help in Creating Duplicate Rows

    I need to create a duplicate set of my rows (into another table / query?).
    The desired number of duplicates is found as a number in an adjacent column.

    I am not, by all means, an expert in Access.

    Any help will be appreciated.

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    There are a few different ways you can do this. A couple are:

    1. If the data entry is through a form, you can use the AfterInsert event of the form to run an append query which appends just that record (i.e. set the criteria on the primary key/autonumber in the append query to equal the primary key/autonumber field on the form and bring all the other fields into the append query to append to the duplicate table).
    2. You can write code in the AfterInsert event of the form to open the duplicate table recordset and add a new record from the values on the form.
    3. There are probably some other ways - this is just a couple.

    If you use either of the above ways, you want to grab that specific record on the form and only append that specific record to the duplicate table.

    One question would be: what do you want to do if they open an existing record and make changes? Do you want to open that record in the duplicate table and update the changes to the matching record in the original table or do you want to add a new record in the duplicate table? Depending on your answer, you may need to design a way to update the matching record in the duplicate table (update query or through code utilizing the AfterUpdate event) or append a new record (again, matching on the primary key/autonumber field).

    Another question would be: what is the purpose of creating a duplicate table? To track changes in the original table?

    What do you want it to do if they delete a record in the original table?

    "The desired number of duplicates is found as a number in an adjacent column." - I'm not sure what you mean here.
    Last edited by pkstormy; 06-04-07 at 21:01.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jan 2007
    Provided Answers: 10
    Could you perhaps explain why you want to create duplicate data?
    Home | Blog

  4. #4
    Join Date
    Jun 2007
    OK, the reason for creating duplicates.
    Our new mining company, from time to time, requests a set of items posted on a form which is sent our head quarters on the other side of the world (almost)

    The request includes a request for several item sets (15 computers, 5 cameras, 12 GPS's)
    On the request form each item set (15 computers) is assigned department codes.

    However, as each item is delivered to our mine site.
    Each item needs to be assigned an individual IDcode.

    If a request Form includes a request for 5 radios, 3 GPS's
    Although the request form sent away states 5 radios and 3 GPS's

    My database must start with an individual row for each item.
    So each item can be given an individual IDcode.

    Thanks for your help.

  5. #5
    Join Date
    Feb 2004
    One Flump in One Place
    Numbers table (here is a SQL Server link:
    Getting the MaxId.

    something like:
    SELECT numbers.number + maxId.theMax AS uniqueId
     , myTable.MyField1
     , myTable.MyField2
     , myTable.Myfield3
    FROM myTable
     (SELECT MAX(uniqueId) AS theMax
     FROM myTable) AS maxId
    WHERE numbers.number BETWEEN 1 AND 30
    The "30" can be a parameter of some sort.
    Possible problem - concurrency
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2007
    I have found an fix for my problem

    For example, go from this:
    BLUE 3
    RED 1
    GREEN 2

    To this:

    And the fix
    Douglas J. Steele - 30 Aug 2006 18:49 GMT
    Actually, there is a way in SQL, but it involves a little trickery... <g>

    Assume that the existing table (for the sake of argument, call it Table1)
    has two fields Characteristic and OccurrenceCount.

    Create a second table (Table2) that contains a single numeric column
    (Counter). Populate Table2 with as many rows as you like (provided it
    exceeds the maximum number of occurrences for the characteristic). Have
    Counter = 1 on the first row, 2 on the second row, and so on up to n on the
    nth row.

    Create a query that joins the two tables (on Table1.OccurrenceCount to
    Table2.Counter) and returns the Characteristic field. Running that query
    will return one row for each row in Table1. However, go into the SQL view
    (View | SQL View from the menu bar). The SQL should look like:

    SELECT Table1.Characteristic
    FROM Table1 INNER JOIN Table2
    ON Table1.OccurrenceCount = Table2.Counter;

    Change the = to >= and rerun the query. You'll get exactly what you're
    looking for. (Note: You will not be able to go back to Design view once you
    make the change to the SQL, as Access has no way of illustrating what you've
    just done)

  7. #7
    Join Date
    Mar 2012

    dublicating rows

    Regarding this last post!

    Could someone actually dublicat this! I have been trying to do this for a while now but with no luck! I am new to Access, so some details would be appreciated!

    Thank you in advance.

Posting Permissions

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