Access DATA Manipulation issue

[humanmaycry]

I right now have a table like this:



Name PPgroup FINgroup COURSE Course_Stream
Chris pp-ny1 cm-ny2 PPcourse1 PP
Chris pp-ny1 cm-ny2 CMcourse1 CM

As you can see, Chris has belongs to 2 groups, 1 is pp-ny1 and the other is cm-ny2; Course is the courses Chris chosen and Course_stream represents the stream of Course belong to.

I want to turn the 2 groups as one; as you can see course stream is related to the group. so the result I want is:

Name Group Course Course_stream
Chris pp-ny1 PPcourse1 PP
Chris cm-ny2 CMcourse2 CM

Thanks for your help.

Regarfds

[rpeare]

Why are you storing the PP group and FIN group on the two different records if you don't want to see the data that way?


I can't think of a way to do this other than a union query

something like
(note I'm changing your NAME field to PERSONNAME and taking out spaces in your field names, NAME is a reserved word and will probably cause you problems with programming as will spaces, special characters and other reserved words)

SELECT PersonName, PPGroup as GROUPNAME, Course, Course_Stream FROM <TableName> WHERE ((Course_Stream) = 'PP')
UNIION ALL
SELCT PersonName, FINGroup as GROUPNAME, Course, Course_Stream FROM <Tablename> WHERE ((Course_Stream = 'CM')

[humanmaycry]

Thanks so much, this really helps.
The data was imported from excel, so you know how bizarre the data can be!

Thanks a lot.