Does MS access(2003) have anything comparable to S

Question

I have a table, call it TBL. It has two columns,call them A and B. Now in the query I require one column as A and other column should be a comma seprated list of all B's which are against A in TBL. e.g. TBL is like this

1 Alpha

2 Beta

1 Gamma

1 Delta

Result of query should be

1 Alpha,Gamma,Delta

2 Beta

This type of thing is very easy to do with cursors in stored procedure. But I am not able to do it through MS Access, because apparently it does not support stored procedures. Is there a way to run stored procedure in MS access? or is there a way through SQL to run this type of query

Answer 1

@Remou on DHookom's Concatenate function: neither the SQL standard nor the Jet has a CONCATENATE() set function. Simply put, this is because it is a violation of 1NF. I'd prefer to do this on the application side rather than try to force SQL to do something it wasn't designed to do. Perhaps ACE's (Access2007) multi-valued types is a better fit: still NFNF but at least there is engine-level support. Remember, the question relates to a stored object: how would a user query a non-scalar column using SQL...?

@David W. Fenton on whether Jet has stored procedures: didn't you and I discuss this in the newsgroups a couple of years ago. Since version 4.0, Jet/ACE has supported the following syntax in ANSI-92 Query Mode:

CREATE PROCEDURE procedure (param1 datatype[, param2 datatype][, ...]) AS sqlstatement;

EXECUTE procedure [param1[, param2[, ...]];

So Jet is creating and executing something it knows (in one mode at least) as a 'procedure' that is 'stored' in the MDB file. However, Jet/ACE SQL is pure and simple: it has no control-of-flow syntax and a PROCEDURE can only contain one SQL statement, so any procedural code is out of the question. Therefore, the answer to whether Jet has stored procedures is subjective.

Answer 2

Perhaps instead of asking if Jet has stored procedures, you should explain what you want to accomplish and then we can explain how to do it with Jet (it's not clear if you're using Access for your application, or just using a Jet MDB as your data store).

Answer 3

You can use GetString in VBA which will return the recordset separated by any value you like (e.g. comma, dash, table cells etc.) although I have to admit I've only used it in VBScript, not Visual Basic. W3Schools has a good tutorial which will hopefully lend itself to your needs.

Answer 4

Well, you can use a Recordset object to loop through your query in VBA, concatenating field values based on whatever criteria you need.

If you want to return the results as strings, you'll be fine. If you want to return them as a query, that will be more complicated. You might have to create a temporary table and store the results in there so you can return them as a table or query.