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?
 
Rey Obrero (Capricorn1)Connect With a Mentor 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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Rey Obrero (Capricorn1)Commented:
you will need a UDF to do that. where do you want the result? in a new table
0
 
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
All Courses

From novice to tech pro — start learning today.