merging a one to many relationship into single record results using one sql statement

[fkotulak]

Here's my problem. I'm trying to get row results where these two tables are combined. I should be able to be do this with joins and unions I just can't seem to get it to work.




table1
recnum
last name


table2
recnum
childname
ordinal

1 to many relationship


sample


Table1
1, Jones
2, Smith


Table2
1,Mary,1
1,John,2
1,Petunia,3
2,Louise,1
2,Sal,2


I need a single SQL command that will give me row results like


Jones, Mary, John, Petunia
Smith, Louise, Sal

Any help appreciated!

[pbaldy]

See if this works for you:

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

[fkotulak]

I actually solved this some time ago when I was actively doing database programming. But I forgot and I didn't save the solution. I know it can be done in one SQL select command. I remember it took me awhile to figure it out!

If I get no other single command select responses I will try and implement your solution which looks like it should work and it is what I'm trying to do!

See if this works for you:

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

[pbaldy]

It could be done with subqueries, but that wouldn't be as dynamic. I'll be interested to see the method if it exists.

[InsuranceGuy]

What you're attempting to do is not possible in MS Access without using VBA (AFAIK). In SQL Server and Oracle there are ways to do this using aggregate concatenating functions, but those do not exist in MS Access.

A VBA function could take your value from Table 1 and build the concatenating values from table 2 though, but that requires a query and a VBA function--doesn't sound like what you wanted to do.


Jeff

[fkotulak]

SELECT t1.*,
t2.Child as Child1, t2.birthdate as Bday1,
t3.Child as Child2, t3.birthdate as Bday2,
t4.Child as Child3, t4.birthdate as Bday3,
t5.Child as Child4, t5.birthdate as Bday4
FROM (((direct AS t1
LEFT JOIN children AS t2 ON (t1.[record number] = t2.[record number] and t2.Ordinal = 1))
LEFT JOIN children AS t3 ON (t1.[record number] = t3.[record number] and t3.Ordinal = 2))
LEFT JOIN children AS t4 ON (t1.[record number] = t4.[record number] and t4.Ordinal = 3))
LEFT JOIN children AS t5 ON (t1.[record number] = t5.[record number] and t5.Ordinal = 4)
WHERE ((t1.Business)=No)
ORDER BY t1.[last name], t1.[First Name]
WITH OWNERACCESS OPTION;

This is fairly standard SQL but the problem came down to Access required parens! This will not work if you do not use the parens. I found out that multiple joins in Access require parens around each. Also you will get "Joins are not supported errors" unless you paren the ON statements when you have a value test?? Huh! This is an apparent "bug" in Access that has not been corrected in 2010. Another Access "bug" occurs after you've run the above type query and it works and then you decide to modify it some more. Access takes out all the ON statement parens. So you have to put them back in again every time. I guess this is the difference between a pseudo-database and a real database like SQL Server.

What you're attempting to do is not possible in MS Access without using VBA (AFAIK). In SQL Server and Oracle there are ways to do this using aggregate concatenating functions, but those do not exist in MS Access.

A VBA function could take your value from Table 1 and build the concatenating values from table 2 though, but that requires a query and a VBA function--doesn't sound like what you wanted to do.


Jeff

[pbaldy]

Like I said, not dynamic, plus it requires the ordinal field which in many/most situations won't exist.