VB Programming

L

Lincoln

This seems a simple VB programming issues but I am not familiar enough with
manipulating collections. So I am asking for help.

I have a table in the following formatNetID Course DegreeType
john WWS 572d PHD
lincoln WWS 516b MPA
lincoln WWS 542 MPA
link WWS 562b MPA
link WWS 586d MPA
mary WWS 502 MPA
mary WWS 512b MPA
joe WWS 541 MPA
joe WWS 556f MPA
diaz WWS 317 MPA
diaz WWS 502 MPA
lisa WWS 512b MPA
lisa WWS 540 MPA
lisa WWS 502 MPA
robert WWS 508c MPA
robert WWS 512c MPA
julie WWS 586d MPA
jule WWS 594p MPA
juule WWS 594s MPAI need to then convert the date into the format below. That is one NetID,
with the multiple courses concattenated into one field then the degree as
shown below

NetID Course Degree
jule WWS 594p,WWS 594p, WWS 594s, MPA
lincoln WWS 516b, WWS 542

and so on...Any help would be hugely appreciated.

Lincoln
 
D

Dirk Goldgar

Lincoln said:
This seems a simple VB programming issues but I am not familiar enough
with
manipulating collections. So I am asking for help.

I have a table in the following format
NetID Course DegreeType
john WWS 572d PHD
lincoln WWS 516b MPA
lincoln WWS 542 MPA
link WWS 562b MPA
link WWS 586d MPA
mary WWS 502 MPA
mary WWS 512b MPA
joe WWS 541 MPA
joe WWS 556f MPA
diaz WWS 317 MPA
diaz WWS 502 MPA
lisa WWS 512b MPA
lisa WWS 540 MPA
lisa WWS 502 MPA
robert WWS 508c MPA
robert WWS 512c MPA
julie WWS 586d MPA
jule WWS 594p MPA
juule WWS 594s MPA
I need to then convert the date into the format below. That is one NetID,
with the multiple courses concattenated into one field then the degree as
shown below

NetID Course Degree
jule WWS 594p,WWS 594p, WWS 594s, MPA
lincoln WWS 516b, WWS 542

and so on...
Click to expand...



You can use the fConcatChild function posted here:

http://www.mvps.org/access/modules/mdl0004.htm
Modules: Return a concatenated list of sub-record values

Do you have a Users table of some sort that only has one record per NetID?
If so, then that table could be the parent table in your query, and this
table could be the child table. However, you can do the whole thing with
just this table, using a Totals query to get one record for each NetID.
Here's an example:


SELECT
NetID,
DegreeType,
fConcatChild("UsersCourses","NetID","Course","String",[NetID])
AS Courses
FROM UsersCourses
GROUP BY NetID, DegreeType;

That assumes a table named "UsersCourses".
 
Top