1

So I have an access 2000 database and i want to write a sql query that would do one SELECT query and based on an id of each row returned in that SELECT query call another nested SELECT query that would concat all those results and the id are linked as a relationship so i just need to concat all the results of the nested second select query

so if the databases are like this...

   Table 1                      Table 2
|ID | First Name|         |ID | Notes|
-----------------         ------------
|1 | Mike       |         |1 | testing|
|2 | Alex       |         |1 | test2  |
|3 | Jon        |         |2 | testing|

so when the query is called it returns

1 mike testing test2
2 alex testing
3 jon
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
italiano40
  • 496
  • 9
  • 18
  • See these questions: [Is there a group_concat function in ms-access?](http://stackoverflow.com/questions/2852892/is-there-a-group-concat-function-in-ms-access) and [Returning SQL rows with data concated per row in access](http://stackoverflow.com/questions/6721727/returning-sql-rows-with-data-concated-per-row-in-access) – ypercubeᵀᴹ Jan 17 '12 at 06:48

1 Answers1

0

A LEFT JOIN or INNER JOIN, such as can be built in the query design window is only going to get you so far. It seems from the above that you also wish to concatenate several rows in table 2 when the id is the same. This cannot be done with Access (Jet) SQL. You will need a user defined function (UDF). You will find two examples here and a search for concatenate + Access should return others.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152