Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    2

    Unanswered: update query that populates values from multiple records into a single field

    MS Access 2003

    Problem:
    Create an update query that populates values from multiple records into a single field

    I have a table with the following 3 fields:
    ID (Primary Key)
    SubprojectID
    DupFrom

    I want to create an update query that will update the SubprojectID value for any record in the DupFrom field matches a value from the ID field.

    Sample Data:

    ID,SubprojectID,DupFrom
    1000001,1,0
    1000002,1,0
    1000003,1,0
    1000004,1,0
    1000005,1,0
    1000006,1,0
    1000007,1,1000004
    1000008,1,1000005
    2000001,2,1000001
    2000002,2,1000002
    2000003,2,1000003
    2000004,2,1000004
    2000005,2,1000005
    2000006,2,1000006
    2000007,2,1000004
    2000008,2,1000005
    3000001,3,1000001
    3000002,3,1000002
    3000003,3,1000003
    3000004,3,1000004
    3000005,3,1000005
    3000006,3,1000006
    3000007,3,1000004
    3000008,3,1000005

    For example ID 1000002 has 2 equal values in the DupFrom field (2000002,3000002). I want to populate the corresponding SubprojectID values from records 2000002,3000002 into a new table, but the results would be delimited as a single field. The new table would look like so:

    NewTable:

    ID,MasterSubprojectID
    1000002,2;3

    Any help would be appreciated

    Luke

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Question INSERT / MAKE TABLE Query

    Hi Luke,

    You could achieve this, although I am interested on why you are trying to do this and what the data represents.

    What is this table going to be used for?

    I have my suspicions that there may be an issue with your table structure - Are you able to post a .zip of your database with dummy data and give us a al little more explanation as to what is going on here?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    May 2009
    Posts
    2
    Quote Originally Posted by garethdart

    You could achieve this, although I am interested on why you are trying to do this and what the data represents.
    Kinda complicated so I'll do my best to explain....

    The table represents a list of documents that may contain duplicate records (these are identified by the DupFrom field). The goal is to eliminate duplicate records from the database and leaving only one unique record in an seperate table. There is one stipulation, if any duplicate records are removed the corresponding section number for the duplicate record(SubprojectID field) is captured and populated for the unique record as a single field. Most unique records will have multiple SubprojectID's and will need to be delimited with semicolons for each value.

    The data represents multiple sets of native files that are broken in sections. Each section is filtered using the metadata from each file. Metadata such as date/time created and the hash code for each file is stored in a database. The files that do not meet the date critera or have a duplicate hash code are removed. Then only the unique records are exported to a delimited text file and assigned a new primary key.

    In the unique set of data there needs to be a field that will list what sections may of had a duplicate file. The primary key for the duplicate record is not important but the section that the file came from is.

    Quote Originally Posted by garethdart

    What is this table going to be used for?
    The table will be reviewed by a third party.


    Quote Originally Posted by garethdart

    I have my suspicions that there may be an issue with your table structure - Are you able to post a .zip of your database with dummy data and give us a al little more explanation as to what is going on here?
    Yes, please see the attached zip file this will contain the .mdb file.

    I have been able to get the update query to update the field but it only populates the last value in the table. I sure there is an expression that will allow for updating mulitple values in a single field. Although I haven't been able to find any examples in this forum or via google.

    Thanks
    Attached Files Attached Files

Posting Permissions

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