1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Microsoft Access- Concatenate same column data together

Discussion in 'Business Applications' started by chudok01, Mar 25, 2019.

Thread Status:
Not open for further replies.
  1. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    202
    I have a table for example. for one customer i have 1 check that pays several invoices. I want the invoices to concatenate so i can have 1 line of data instead of 2

    name check invoice #
    Joe 123 45679
    Joe 123 98746
    Sam 456 11233
    Sam 456 56484

    I want it to return this

    name check invoice #
    Joe 123 45679 / 98746
    Sam 456 11233 / 56484
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I am not sure if this can be done with SQL.
    I think it can be done using VBA code using 2 methods, one would use a Query with a VBA module for the concatenation and then a second query to only select the record with all the Invoice numbers.
    The second would be to use VBA code to place the data in to a temporary Table with the concatenated data and run a query or report from that table.
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, using a new output table you can also use append and Update queries to achieve what you want without VBA code.
    The attached database has 2 tables called
    Table2
    and
    Table2 Output.
    Tables 2 Output has the fields from table 2 plus a control field which is set to Indexed without duplicates, it contains the FullName and Check concatenated to produce a unique ID.
    There is Query called Table2 Append which creates the unique records in Table2 Output and another Query called Table2 Update which updates the Invoices field with your concatented Invoices.
    Note that the Invoices field has to be Text.
     

    Attached Files:

    Last edited: Mar 26, 2019
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1224926

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice