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.

MS Access Concatenate Fields

Discussion in 'Business Applications' started by dfriend846, Jan 28, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. dfriend846

    dfriend846 Thread Starter

    Joined:
    Aug 11, 2005
    Messages:
    208
    I know how to concatenate field values in a record but I'm wondering if it is possible to concatenate field values in separate records. My reason is that I have a form showing data from a query made from two tables. I want only one record in the underlying data and if I join two tables where one has multiple matches then there will be multiple records in the query. Table 1 has a record with joining field f1 (no duplicates allowed) and Table 2 has multiple records for f1 with different f2 values. Here's what I mean:
    T1 has f1 = 90000
    T2 has
    f1 = 90000, f2 = T
    f1 = 90000, f2 = Y
    f1 = 90000, f2 = N

    Is there a way to set up a query operating on T2 to give a table with the value
    f1 = 90000, f2 = Y,T,N

    If so, then a join on f1 with this table would yield only one record for the form I display. As a fallback position should I just do a summary query on T2 and show the resultant records in a small subform?

    Thanks in advance for any advice on this?
     
  2. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    Hello again, what are we up to now I wonder? I doubt if you can do this with a single query, but it might be possible with more than one. It is definitely do-able using VBA. Would you lke me to have a look at it for you?
     
  3. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    Unless you are trying to de-normalize your database, I would look for different approaches to whatever you're trying to do. What is the application of this? Your form/subform suggestion seems like the perfect solution to the problem (though I don't really know the problem).

    Either way, you can't do what you're trying with a single query.

    chris.
     
  4. dfriend846

    dfriend846 Thread Starter

    Joined:
    Aug 11, 2005
    Messages:
    208
    Hello OBP and Cristobal,
    As you have suggested I am using the subform approach in my form and my use for the concatenated data was in a report not a form. Here's why I thought it would be useful: my database is to store and report chemical and usage information on our raw materials. The raw materials have unique codes (assume 91522 is one of these codes) but are used in many products. Each product has one of four values in a category of interest (blank or N, T, Y or YT which is Y+T). It is easy to summarize the categories for all the formulas using 91522 (i.e. to get 3 records with Y, T or YT if there are e.g. 30 formulas using the raw material and having these categories). I would get 3 records 91522/Y, etc. What I wanted to do was show a single field on my report page for 91522 showing "T,Y,YT" as a field value. Since it is just for the report there will not be a proliferation of records in the dataset.
     
  5. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    no problem
     
  6. Sponsor

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/437900

  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