Combine all data into one field.

[hmongie]

Hi, hope someone can help.


I have two tables.

Table1 and Table2

Table1 has three fields.
Id, book, initials

Table2 has two fields.
Id, initials


Table1 is linked to Table2 on the ID to ID

I need to have all of Table2's initial data combined into Table1's initials field. The inputs are made into Table2. Table1's books are assigned values. Table 1 shows which students have read which books.

I need the the form for Table1 to display in the following manner.

For example:

Table1

ID book initials
1 123 MR, BC, UK
2 234 BC, HC, TY
3 222 GH, MR

Table2

ID initials
1 MR
1 BC
1 UK
2 BC
2 HC
2 TY
3 GH
3 MR


Thanks for any help.

 

[KeithG]

Paste the below function into a module and you can call it from a query. You need to add some error handling as this is just an example. I dont have a copy of Access on this Pc so I have not tested it.

Public Function List_Intials(ID as Long) as string
	Dim myRec as DAO.Recordset
	Dim strSQL as string
	dim strInt as string
	Dim lngID as long

	lngId=Id

	strSQL="Select * from table2 where ID=" & lngID
	set myRec=currentdb.openrecordset(strSQL)
	myRec.movefirst
	do until myRec.Eof
		strInt=strInt & myRec.fields("Intials") & ","
                          myRec.MoveNext

	loop
	myRec.close
	List_Intials=strInt
	set myRec=nothing
End Function

 

[Premy]

Not sure what yer really after, but if u just want to combine data from different fields in 1 textbox on your form, u could try setting the txtbox control source up like (for example):
=[Id] & " " & [Book] & " " & [Initials]


the fields Id, Book and Initials do not need to be on the form, but they should be present in the form's Fields List.

HTH
Premy

 

[KeithG]

Just to make sure I understand, you actualy want to combine the values from the intials field for each id into one field correct? That was the intention of my above function.

 

[hmongie]

Thank you all. I will try the aboves and will let you know how it works. Thanks again for all your posts.

 

[hmongie]

hmm.. still having problems with this..

Does anyone have any other ideas.
I couldn't get the others to work.

The results must be displayed in a form.

How about a SQL statement to say something like,

Select * initials from table2
where id.table1 =id.table2.

Any ideas on how to get the sql to work in the control source of a form?

 

[hmongie]

Thanks all,

I seems to have managed to work something out. I just did a collumn down and displayed all the inputs based on the ID fields. It looks okay. I would had like to display them horizontally but this will do.

Thanks all.