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?