Results 1 to 5 of 5

create table based on existing data

  1. #1
    LVL is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Dec 2018

    Question create table based on existing data


    Hope I am in the correct forum!
    I have an access table containing +- 3000 records (call it table 1).
    One of the fields (call it ID) contains data (short text) formatted as follows: xx-yy-zzz or xx-yy-zzzz where xx, yy and zzz(z) are figures.
    I want to recreate a table (table 2) where field 1 (call it Ident) is formatted as autonumber [with format xx-yy-zzz(z)] and then fill this table in the way that the data of table 1 are transferred to table 2 with ID = Ident.
    In other words: in table 2, if Ident is 01-01-001 then the record should contain the data of the record in table 1 where ID is 01-01-001

    To be complete: in table 1, not all ID figures are used: example: 01-01-002, next is 01-01-004 (no 01-01-003!).

    I am NOT a whizzkid, so if possible, step-by-step would be appreciated!!

    thanks ever so much for help!!


  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    make your sub table (table2)
    then make an append query to add data from table1 to table2

  3. #3
    LVL is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Dec 2018
    Ranman 256,

    thanks for the reply.
    How will I define the query?
    Taking into account that autonumber is just that autonumber and that not all numbers are present in table 1...


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

    See this re Autonumbers.
    You will also find that relational databases work very well when using an old principle
    --one fact in one field.

    Good luck with your project.

  5. #5
    LVL is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Dec 2018
    Hello Orange,

    thank you very much for this clear and enlightening answer! Point taken

    Then, let me take you to the origin of the problem:
    In fact, I am talking about a database with info related to "ID numbers" (the famous xx-yy-zzz(z) )
    Up to now, the info comes in via Excel worksheet that has a macro to assign new ID numbers that are imported into the database afterwards.
    The Excel macro uses info from the database to run ("highest ID number used").

    Where things get annoying is here:
    when importing the new ID numbers and their info into the database, a check is done to see if the info (the person) already exists in the database. If this is the case, the info is NOT imported.
    However, this means I "loose" ID numbers in the series.
    import from excel:
    01-01-010 info1 info2 info3
    01-01-011 info1 info2 info3
    01-02-012 info1 info2 info3

    when adding to table via query, it seems that the person given ID 01-01-011 by the Excel macro already exists in the database under 01-01-007 and will therefore not be imported.
    I am looking for a way to avoid the loss of ID's as the info comes in via Excel sheet and the ID numbers need to be attributed and are exclusive...

    sorry for the complications...


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

Similar Threads

  1. Replies: 5
    Last Post: 09-04-2017, 08:22 PM
  2. Create table from existing junction table
    By Khalil Handal in forum Access
    Replies: 2
    Last Post: 07-29-2017, 03:37 AM
  3. Replies: 5
    Last Post: 04-26-2016, 04:30 AM
  4. Replies: 5
    Last Post: 08-22-2013, 03:32 PM
  5. Replies: 3
    Last Post: 03-28-2013, 06:22 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
Tech Forums: Microsoft Office Forums