Many fields into One?

[smbarney]

I have a one to many question. I am in the middle of converting an Access database over to MS SQL 2005. I am converting all of the queries into pass though queries. One of my queries takes a sub-table that has many fields related to the primary table and concentrates them into one field using Duane Hookom's Concatenate function (you can download it at http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16). In this way, I can run a report or display a list of all of the people from the sub table associated with the primary key of the main table. For the database I am working in, this is a critical function. The problem is the database is getting too big and has too many users to run as an Access Database anymore.

In converting over to SQL, this function won't work as a pass-through query at least I haven’t got it to work. Is there a way to concentrate fields in a One to Many relationship using SQL and pass through queries? Or is there way to concentrate the fields on the SQL server?

Any advice you all could offer would be most welcome. Thanks.

 

[WayneRyan]

sm,

You can create a T-SQL function that uses a cursor and returns a
comma-seperated string value.

Need more info in order to post any code.

Wayne

 

[pbaldy]

I saw a slick function posted by blindman here:

http://www.dbforums.com/showthread.php?t=1605725

 

[smbarney]

Thanks for the replys....

Here is what I have.

tblIntakeMain (Primary Key: IntakeMainID) is contains data related to cases. A sub-table (stblPersonnel, Primary Key: PersonnelID, Foreign Key: IntakeMainID) contains people associated with each case. The number of people per case various between one to as many as 10 (there is no limit). In Access, I have a function that takes all of the people associated with a case and places them in one field, based on the IntakeMainID.

For example:

stblPersonnel
PersonnelID IntakeMainID FullName
1 1 Bob Smith
2 1 John Doe
3 2 Jane Badger
4 2 Kim Hinton
5 1 Matt Hoffman
6 4 Kaitlin Hogan

After the I run the function, I get a query that looks like this:

qPersonnelConcentrate
IntakeMainID FullName
1 Bob Smith, John Doe, Matt Hoffman
2 Jane Badger, Kim Hinton
3
4 Kaitlin Hogan

I looked at this SQL function:

create function dbo.Concat_ICD(@RECID int)
returns varchar(200) as
begin
declare	@tmpICDchar varchar(200)
set	@tmpICDchar = ''
select	@tmpICDchar = @tmpICDchar + ICD
from	(select	top 29
		Cast(pr_icd1 as Char(5)) as ICD
	from	tblProcedure
	WHERE	Record_ID = @RECID) TopValues
return	@tmpICDchar
end

and use this code for the table:

SELECT	Distinct
	Record_ID,
	dbo.Concat_ICD(Record_ID)
FROM	tblProcedure

What I am not clear on is how my tables would be used with this function and how would I display the results in a MS Access form?

Thanks for the help.