CONCAT

  • Thread starter Thread starter fingermark
  • Start date Start date
F

fingermark

I have a client that is requesting I support MSACCESS, but I am stumped
trying to concatenate as follows:

I have three tables.
SELECT Clients.ClientID, Clients.ClientName, CONCAT(SELECT * FROM
Orders WHERE ClientID = [Clients.ClientID]) AS Orders FROM Clients;

Any ideas on how I would do something like this... so, what I want is:
ClientID | ClientName | Orders
2 | Bob | Computer, Motherboard, Mouse
3 | Tom | Keyboard, Motherboard, Mouse

I need this to work in a web app, so a custom module won't work here --
as i don't think the Jet engine will allow it.
 
This might better be answered in a "web app" news group. With most web apps,
you would create a recordset and loop through it. You should be able to
convert the Concatenate function and use it with a recordset like:
SELECT Clients.ClientID, Clients.ClientName, Orders.*
FROM Orders INNER JOIN ClientID ON Orders.ClientID = Clients.ClientID
ORDER BY ClientID;
 
What concatenate function? Also, I'm not seeing how that SQL statement
would generate a comma separated list for one of the fields.

Duane said:
This might better be answered in a "web app" news group. With most web apps,
you would create a recordset and loop through it. You should be able to
convert the Concatenate function and use it with a recordset like:
SELECT Clients.ClientID, Clients.ClientName, Orders.*
FROM Orders INNER JOIN ClientID ON Orders.ClientID = Clients.ClientID
ORDER BY ClientID;

--
Duane Hookom
MS Access MVP


I have a client that is requesting I support MSACCESS, but I am stumped
trying to concatenate as follows:

I have three tables.
SELECT Clients.ClientID, Clients.ClientName, CONCAT(SELECT * FROM
Orders WHERE ClientID = [Clients.ClientID]) AS Orders FROM Clients;

Any ideas on how I would do something like this... so, what I want is:
ClientID | ClientName | Orders
2 | Bob | Computer, Motherboard, Mouse
3 | Tom | Keyboard, Motherboard, Mouse

I need this to work in a web app, so a custom module won't work here --
as i don't think the Jet engine will allow it.
Click to expand...
Click to expand...
 
That SQL statement won't create a comma separated list. You need to create
the values in your ASP (or other) source code. You would need to loop
through records and concatenate the values until the ClientID changes. The
write the value to the web page.

Again, this isn't an Access question.


--
Duane Hookom
MS Access MVP

What concatenate function? Also, I'm not seeing how that SQL statement
would generate a comma separated list for one of the fields.

Duane said:
This might better be answered in a "web app" news group. With most web
apps,
you would create a recordset and loop through it. You should be able to
convert the Concatenate function and use it with a recordset like:
SELECT Clients.ClientID, Clients.ClientName, Orders.*
FROM Orders INNER JOIN ClientID ON Orders.ClientID = Clients.ClientID
ORDER BY ClientID;

--
Duane Hookom
MS Access MVP


I have a client that is requesting I support MSACCESS, but I am stumped
trying to concatenate as follows:

I have three tables.
SELECT Clients.ClientID, Clients.ClientName, CONCAT(SELECT * FROM
Orders WHERE ClientID = [Clients.ClientID]) AS Orders FROM Clients;

Any ideas on how I would do something like this... so, what I want is:
ClientID | ClientName | Orders
2 | Bob | Computer, Motherboard, Mouse
3 | Tom | Keyboard, Motherboard, Mouse

I need this to work in a web app, so a custom module won't work here --
as i don't think the Jet engine will allow it.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top