How do I combine duplicate rows into one?

Have table with many duplicate rows, such as:
StudNum      MARK      Type
12345      68      Math
12345      71      English

I'd like to combine these into one row, if the StudNum is the same, through a query, if possible.
lbarnett419Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jinesh KamdarCommented:
What are all the different possible values in the Mark and Type column for the same StudNum?
0
lbarnett419Author Commented:
The mark field can contain values from 1-100. The type field contains either "English" or "Math".
Ideally, I'd like the record to look like:
StudNum     Math     English  
12345           68          71

Sorry, I wasn't clear about that earlier. Thanks.

0
Rey Obrero (Capricorn1)Commented:
you will need a UDF to do that. where do you want the result? in a new table
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

lbarnett419Author Commented:
One more thing: The table can contain a combination of duplicate ids along with info as well as non duplicate lines, such as:
StudNum      MARK      Type
12345         68             Math
12345         71            English
33456         74            English
67676         68            Math
45678         72           English
45678         71             Math

I'd still like to see the field values of type turned into the field name, with the mark (grade) as the value attached. The reason why some students only have one grade is that they don't meet a certain threshold, so their English or Math grade could possible be blank. That's ok.
StudNum     Math     English  
12345           68          71                (2 recs combined into 1)
33456                         74                (single rec with one grade)
67676           68                              (single rec with one grade)
45678          71           72                (2 recs combined into 1)

0
lbarnett419Author Commented:
The results created in a new table are fine.
0
lbarnett419Author Commented:
What I meant was, the results CAN be created in a new table...it doesn't matter. I just don't know how to do it. Having a confusing day here on my part.....
0
Rey Obrero (Capricorn1)Commented:
sorry..just got back.

can you run this query and post the result here

select distinct [type] from NameofTable

change NameofTable to the actual name of the table
0
lbarnett419Author Commented:
Type (header)
English
Math
0
Rey Obrero (Capricorn1)Commented:
just two ?

create a table  tblNewGrades with fields StudNum, Math, English Number type

place this codes in a module

change  tblGrades with the name of your table..


Sub updateNewTable()
Dim rs As DAO.Recordset, rsNew As DAO.Recordset, rs1 As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("select distinct studnum from tblGrades")
Set rsNew = CurrentDb.OpenRecordset("tblNewGrades")

rs.MoveFirst
Do Until rs.EOF
    Set rs1 = CurrentDb.OpenRecordset("select * from tblGrades where studnum=" & rs("studnum"))
        rsNew.AddNew
            rsNew("StudNum") = rs("studnum")
        Do Until rs1.EOF
           
            rsNew(rs1("type")) = rs1("mark")
           rs1.MoveNext
        Loop
        rsNew.Update
rs.MoveNext
Loop
rs.Close
rs1.Close
rsNew.Close

End Sub
0
lbarnett419Author Commented:
Capricorn1, you said:
"create a table  tblNewGrades with fields StudNum, Math, English Number type"
Were there supposed to be commas after English to distinguish the fields?
Thanks


0
Jinesh KamdarCommented:
How about this?
SELECT StudNum,
       IIF(Type = 'Math'   , Marks, 0) AS Math,
       IIF(Type = 'English', Marks, 0) AS English
FROM Students
GROUP BY StudNum

Open in new window

0
Rey Obrero (Capricorn1)Commented:
you just need three fields StudNum, Math, English
0
lbarnett419Author Commented:
Cap1:
I get a data type error on this line:
Set rs1 = CurrentDb.OpenRecordset("select * from tblGrades where studnum=" & rs("studnum"))

Both tables have studnum as text with length of 12.
0
Rey Obrero (Capricorn1)Commented:
change this

Set rs1 = CurrentDb.OpenRecordset("select * from tblGrades where studnum=" & rs("studnum"))


to

Set rs1 = CurrentDb.OpenRecordset("select * from tblGrades where studnum='" & rs("studnum") &"'")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.