VBA programming with recordsets

  • Thread starter Thread starter Lincoln
  • Start date Start date
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 data into the format below. That is one unique
NetID,
with the multiple Courses concattenated into one field; then the degree
field 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
 
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 data into the format below. That is one unique
NetID,
with the multiple Courses concattenated into one field; then the degree
field 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
Click to expand...

use fConcatChild from www.mvps.org/access (Access web). It's in the
modules section.
 
Dirk;
The code with some minor "mods" now gives me output; PROGRESS.

My only trouble is that every record is now giving me the same concatenated
output. This suggests that the Where Clause; StrSQL = StrSQL & "Where" is the
trouble. That could be because of data type, or merely the syntax needs to be
altered.
 
Back
Top