Show results not using join??

[nathansav]

Hi

I have 2 tables, one tbl_Application contains details of applications, tbl_A_Numbers contains details of account numbers for that application. I want to run a query which puts all the records from tbl_A_Numbers into one column, rather than the conventional link.

So, i have APplication 1, 1234,1235,1236

rather than

Application 1, 1234
Application 1, 1235
Application 1, 1236

and so on.

Is this possible, just in SQL?

I have tried using (Select A_Number from tbl_A_Numbers where A_Number.Application_ID=tbl_A_Numbers.Application_ID) and it doesnt work

Thanks

 

[pbaldy]

Check out UNION queries and see if that's what you're after.

 

[nathansav]

A Union query would not do it, that would again put the information underneath.

Essentially i want to see the application, then a list of the application numbers, in one column, so Application 1 in column 1, then in column 2, 1234,1235,1236 etc.

Thanks.

Nathan.

 

[pbaldy]

Sorry, I reversed it in my head. How about:

http://allenbrowne.com/func-concat.html

or

http://access.mvps.org/access/modules/mdl0004.htm

 

[nathansav]

Cheers,

I've used VBA before to do it, but wandered if there was a SQL method. Reason being it is for a piece of work i am to hand over as soon as done, so didnt want to have a "How to Code" lesson before hand.

Cheers anyway.

Nathan.