Results 1 to 14 of 14
  1. #1
    pooldead is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    26

    Help Merging Duplicate Records

    I am working with spreadsheets that have four columns: app username, first name, last name, role. The same user can have multiple roles in the spreadsheet. I am trying to merge these down into one, but want to have this step completed during the import process I have coded.



    By default, the table appears like:

    App Username First Name Last Name Role
    john.smith John Smith User
    john.smith John Smith Admin
    john.smith John Smith RO

    What I would like it to output as is (with the 'Role' field all being one cell):

    App Username First Name Last Name Role
    john.smith John Smith User
    Admin
    RO

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Should import each role as separate record. Also, should normalize data structure so the name info is not repeated, just UserID is saved with role. Have a Users table where each user is a unique record. UserRoles would be a related dependent table storing UserID as foreign key.

    Then in report output, combine the values into one string. Review http://allenbrowne.com/func-concat.html
    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
    pooldead is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    26
    Thank you June7, but I am very novice with Access. I get the idea of storing each user uniquely, but is this something I need to setup a query for? Queries and relationships are a big weak point for me with Access, but I absolutely want to learn.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    The function can be called in a query, a textbox, or another VBA procedure.

    Have you studied an introductory tutorial book?
    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.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    At this link https://www.accessforums.net/showthread.php?t=76842 I posted (post #7) an example file how to split an all-in-one exported/linked table into various tables. I think you'll find there one which is very similar with your case here.

  6. #6
    pooldead is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    26
    So I found this http://allenbrowne.com/func-concat.html which should seemingly do what I want it to. However, I'm now having trouble getting VBA to recognize my code. What am I doing wrong to call Allen Browne's function?

    Private Sub cmdImpTEST_Click()
    Dim strSQL As String

    DoCmd.DeleteObject acTable, "TEST_UL"
    DoCmd.TransferText acImportDelim, , "TEST_UL", "\\C:\User\Documents\TESTProd.csv", True

    strSQL = "SELECT * FROM [TEST_UL] WHERE ConcatRelated([ROLES], [TEST_UL], [APP_USERNAME = ""] & [APP_USERNAME] & """", [APP_USERNAME])"
    DoCmd.RunSQL (strSQL)

    MsgBox "TEST import complete."
    DoCmd.Close acForm, "frm_importUL"
    End Sub

    Note: I am trying to use Allen Browne's function to merge rows where APP_USERNAME is the same and the ROLES are different

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    RunSQL is for SQL actions (DELETE, UPDATE, INSERT) not to open a SELECT query.

    The query needs to be RecordSource of a form or report. So either build a query object and reference it or put the SQL directly in RecordSource property.

    Function arguments need to be within quote marks. The function is calculating a field, not criteria to filter dataset.

    SELECT *, ConcatRelated("[ROLES]", "[TEST_UL]", "[APP_USERNAME =]'" & [APP_USERNAME] & "'", "[APP_USERNAME]") AS Data FROM [TEST_UL];

    You will notice same concatenated data will show for multiple records because pulling all fields from TEST_UL and not aggregating by ROLES.

    Why does fieldname have special characters?
    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.

  8. #8
    pooldead is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    26
    When you say RecordSource, you are talking about ADO/DAO right? So if I wanted to put it directly, I would change strSQL from String to RecordSource (or however it's referenced in VBA), right?

    The special characters are the result of an issue with the report I'm importing. It is generated from a SQL query. I have someone working to fix it now, but until then I'm just trying to make sure everything matches.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I am talking about the RecordSource property of a form or report in Design. Run your import code (without the RunSQL) then open a report.

    The concatenated data is not saved to table. It is calculated in query when needed.
    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.

  10. #10
    pooldead is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    26
    Okay, so I found some code that I believe should allow me to update the recordsource. I am now getting an error "undefined function 'concatrelated' in expression when I do not include the DoCmd line. I do have the function declared as public, as was mentioned in Allen Browne's site. When I include it, I get a type mismatch.

    Also, I created a second table in order to create a relationship and store the modified data

    strSql = "SELECT * FROM [Test_Initial] where ConcatRelated([ROLES], [Test_UL], [APP_USERNAME =] & [APP_USERNAME], [APP_USERNAME])"
    DoCmd.RunCommand strSql
    Forms!frm_importUL.Form.RecordSource = strSql
    Forms.frm_importUL.Requery

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    You have not enclosed function arguments within quote marks. Review post 7 again.

    RunCommand is not applicable to query statement because query statement is not an Access command. Remove that line.
    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.

  12. #12
    pooldead is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    26
    No problem removing the runcommand, I wasn't really sure if I needed it anyways. When I add the quotations around each as you earlier specified, I get an error "Expected: End of Statement". This error goes away when the quotes are removed

  13. #13
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Here is a document that should help in the future in your design
    Attached Files Attached Files

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Okay, sorry, if you want to build the SQL statement in VBA, gets tricky. Double the embedded quote marks. Also, I had a typo in placement of = sign.
    Code:
    strSQL = "SELECT *, ConcatRelated(""[ROLES]"", ""[TEST_UL]"", ""[APP_USERNAME]='"" & [APP_USERNAME] & ""'"", ""[APP_USERNAME]"") AS Data FROM [TEST_UL];"
    But why use VBA? Just put SQL statement in RecordSource property. Usually this function is called in a report, not a form.
    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.

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

Similar Threads

  1. Merging Data From Duplicate Database Files
    By ComputerPower in forum Access
    Replies: 2
    Last Post: 03-18-2015, 06:51 PM
  2. Replies: 2
    Last Post: 12-05-2014, 07:59 AM
  3. Merging Records.
    By Tom Kidman in forum Forms
    Replies: 1
    Last Post: 05-27-2014, 06:11 AM
  4. Merging duplicate data
    By spenco in forum Access
    Replies: 2
    Last Post: 12-02-2013, 05:30 PM
  5. Merging and deleting duplicate records
    By Rider in forum Access
    Replies: 1
    Last Post: 01-06-2012, 01:44 PM

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