two nested select statements

[JS]

Let's say I have two tables:

t1
myname
apple
banana
cherry

t2
myname | value
apple | 1
banana | 2
apple | 3
banana | 4
cherry | 5
I want to do one query and get back the data like this:

+--------+-----------+
| myname | all_value |
+--------+-----------+
| apple | 1,3 |
| banana | 2,5 |
| cherry | 4 |
+--------+-----------+
In my head, it seems like the query should look like:

select t1.name, (select t2.value from t2 where t2.name=t1.name) as
all_value from t1;

But that just doesn't work. Is this possible without resorting to
code?

Thanks

[Bob Quintal]

jd***@hotmail.com (JS) wrote in
news:b0**************************@posting.google.c om:

Let's say I have two tables:

t1
myname
apple
banana
cherry

t2
myname | value
apple | 1
banana | 2
apple | 3
banana | 4
cherry | 5
I want to do one query and get back the data like this:

+--------+-----------+
| myname | all_value |
+--------+-----------+
| apple | 1,3 |
| banana | 2,5 |
| cherry | 4 |
+--------+-----------+
In my head, it seems like the query should look like:

select t1.name, (select t2.value from t2 where
t2.name=t1.name) as all_value from t1;

But that just doesn't work. Is this possible without
resorting to code?

Thanks

Moving each value to a comma delimited list will require code. If
you can live with

+--------+-----------+
| myname | all_value |
+--------+-----------+
| apple | 1 |
| apple | 3 |
| banana | 2 |
| banana | 5 |
| cherry | 4 |
| lemon | 0 |

try
SELECT t1.name,nz(t2.value,0) AS all_value
FROM t1 LEFT JOIN t2 ON t1.name=t2.name
ORDER BY t1.name,t2.value.

If the query is to feed a report, you can set up the detail section
to print multiple columns, giving the appearance you want.

If you really want it in the query as a comma delimited list,
you'll need a function that creates the list. Note to MicroSoft:
there really should be a domain aggregate function that does that.

Bob Quintal

[Pieter Linden]

This will do it:
http://www.mvps.org/access/modules/mdl0004.htm
"Return a concatenated list of sub-record values"