Results 1 to 4 of 4
  1. #1
    Deisun is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2011
    Posts
    14

    Query to Concatenate multiple fields between a table and a sub table to 1 line

    I'm having trouble writing a query to concatenate multiple fields to 1 line. I have successfully done it with a single table, but I can't get it to work with 2 different tables.



    Here's a simple version of what I have:

    tbl1: Clients
    tbl2: Services

    tbl1 fields: First Name, Last Name
    tbl2 fields: Date of Visit

    So I wrote this query to concatenate First Name and Last name to 1 line:
    Code:
    SELECT Clients.ID, Clients.[First Name] & " " & Clients.[Last Name] AS Expr1
    FROM Clients
    WHERE (((Clients.[First Name] & " " & Clients.[Last Name]) Like "*" & forms!Clients!SrchText & "*"));
    This works fine and dandy. (SrchText is my textbox for search input btw)

    However...my problem is I want to get all the Date of Visits in the Services table to also be concatenated to that same line with the first/lastname.
    So I tried this:
    Code:
    SELECT Clients.ID, Clients.[First Name] & " " & Clients.[Last  Name] & Services.[Date of Visit] AS Expr1
    FROM Clients, Services
    WHERE (((Clients.[First Name] & " " & Clients.[Last Name] & " " & Services.[Date of Visit]) Like  "*" & forms!Clients!SrchText & "*"));
    However, all I get when I run this query is 1 line like:
    Jim Brown 1/24/10

    The problem is this record has multiple Date of Visits and instead I need it to say something like:
    Jim Brown 1/24/10 2/4/10 5/6/11

    Why is only 1 Date of Visit being concatenated for the whole record that has 3 Date of Visits? Any help in the right direction would be greatly appreciated. Also, the 1 date it's showing is the same for every line. How do I make it concatenate all the dates for each client?

  2. #2
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    if you want the result from several records pasted together in one record you'll need VBA.

    greetings
    NG

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Why do you need to see your data this way? it's extremely unusual that you would need to manipulate your data this way and perhaps there's an easier way for you to accomplish what you actually want.

  4. #4
    JohnB47 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    19
    I do have a solution for you - I can put a sample access 2007 db together if you want. It's something that I had great help putting together some years ago.

    But it will take some of my time (which I'm happy with) and a fair bit of yours to get it working. As others have said, why do you feel you need this? In what way are you going to use the concatenated field?

    I would hate for both of us to spend a load of time doing something that is more easily accomplished by other means.

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

Similar Threads

  1. Replies: 5
    Last Post: 07-14-2019, 05:22 PM
  2. Replies: 6
    Last Post: 04-07-2011, 12:33 PM
  3. Show data not in table A using multiple fields
    By mikesmith01 in forum Access
    Replies: 3
    Last Post: 02-25-2011, 08:38 AM
  4. Look up values in multiple table fields
    By nmcentire in forum Programming
    Replies: 2
    Last Post: 11-12-2010, 02:02 PM
  5. One table multiple fields identical properties.
    By swampdonkey in forum Queries
    Replies: 2
    Last Post: 09-29-2006, 10:53 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
  •  
Other Forums: Microsoft Office Forums