Help with combining records based on common id

[patelni]

Hello.



I relatively new at access and programming. I have a problem and thought I could use DB and a VB to accomplish the following on a data file. I am not sure of the script or statement needed to make the transformation. I have highlighted in bold some of the changes.

1 look at various records with different values and select the highest grade (A,B,C,D,E)
2 combine multiple records based on an id to make 1 single unique record.

Below is a sample of the raw data and what I am trying to get to.

Thanks

Table= Gradebook Stunum Lname Fname ELACrsNo-1 ELACrsName-1 ELAQ1-1 ELAQ2-1 ELAQ3-1 ELAQ4-1 ELAFinGr-1 ELACrsNo-2 ELACrsName-2 ELAQ1-2 ELAQ2-2 ELAQ3-2 ELAQ4-2 ELAFinGr-2 ELACrsNo-3 ELACrsName-3 ELAQ1-3 ELAQ2-3 ELAQ3-3 ELAQ4-3 ELAFinGr-3 5050 Smith John 1001010 M/J LANG ARTS 1 B C C C C 5050 Smith John 5050 Smith John 2121 holt andy 1001010 M/J LANG ARTS 1 A A B A B 2121 holt andy 2121 holt andy 5555 flask john 1000010 M/J INTENS READ (MC) A C C 5555 flask john 1000010 M/J INTENS READ (MC) A B A 5555 flask john 1001010 M/J LANG ARTS 1 C D C 5555 flask john 1001050 M/J INTENS LANG ARTS 1 C C B 5555 flask john 1001050 M/J INTENS LANG ARTS 1 B B C B What I would like Stunum Lname Fname ELACrsNo-1 ELACrsName-1 ELAQ1-1 ELAQ2-1 ELAQ3-1 ELAQ4-1 ELAFinGr-1 ELACrsNo-2 ELACrsName-2 ELAQ1-2 ELAQ2-2 ELAQ3-2 ELAQ4-2 ELAFinGr-2 ELACrsNo-3 ELACrsName-3 ELAQ1-3 ELAQ2-3 ELAQ3-3 ELAQ4-3 ELAFinGr-3 5050 Smith John 1001010 M/J LANG ARTS 1 B C C C C 2121 holt andy 1001010 M/J LANG ARTS 1 A A B A B 5555 flask john 1000010 M/J INTENS READ (MC) A B A 1001010 M/J LANG ARTS 1 C D C 1001050 M/J INTENS LANG ARTS 1 B C B

[June7]

That won't be easy. For one thing, can't have duplicate field names. So this data is in a text or spreadsheet file?

Here is one example of what can be involved in unusual transposition of data http://forums.aspfree.com/microsoft-...ry-322123.html

[patelni]

The data is in an excel file. I can pull in the duplicate numbers, sort them and get them to where I can see what needs to be combine but no automated way of combining and collapsing the results

[June7]

No intrinsic automated method. Requires code unique to your requirements. I provided example of another unique situation to learn from.