Merge two records into one

[rpadams]

I have a table with the following fields for each record:
StudentID CsrID Average FinalExam

For most StudentID/CsrID there are two records, one with the Average and a null FinalExam, one with the FinalExam and a null Average. If there is no FinalExam, no record is generated.

How can I merge the records so that I will have only one record for each StudentID/CsrID that includes both the Average and the FinalExam?

 

[neileg]

The way I would do this is as follows (there may be more elegant ways, however!).

Create a new table with same structure as your existing table. Create a select distict query to return the StudentID and CsrID fields and append these to the new table. Create a new query to return the StudentID, CsrID and Average excluding nulls on the Average and update this to the table. Create a new query to return the StudentID, CsrID and FinalExam excluding nulls on the FinalExam and update this to the table.

 

[EMP]

Alternatively, you can use a Totals query to group the records:

SELECT StudentID, CsrID, Min([TableName].Average) AS Average, Min([TableName].FinalExam) AS FinalExam
FROM [TableName]
GROUP BY StudentID, CsrID;

^