Results 1 to 7 of 7
  1. #1
    alpinegroove is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Jan 2011
    Posts
    114

    Query for combining records

    Occasionally I receive an Access Database file containing the names of approximate 1000 students. The fields are: Student Name, Student ID Number, Date of Birth, Exam Score 1, Exam Score 2, Exam Score 3.



    For some reason that is not in my control, instead of all the exam scores being listed in the same record, each student with more than one exam score gets multiple records.

    The first record includes data under Student Name, Student ID Number, Date of Birth, and Exam Score 1 but nothing under Exam Score 2 and 3.

    The second record includes the same data under Student Name, Student ID Number, and Date of Birth. Exam Score 2 is populated, but Exam Score 1 and 3 are blank.

    The third record includes the same data under Student Name, Student ID Number, and Date of Birth. Exam Score 3 is populated, but Exam Score 1 and 2 are blank.

    Is it possible to combine the multiple records of each student to produce one record with the three exam scores without doing it manually?

    What kind of query can I use to achieve that?

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    select studentName, studentID, sum([exam Score 1]) as examScore1, sum([exam Score 2]) as examScore2, sum([exam Score 3]) as examScore3 group by studentName,studentID

  3. #3
    alpinegroove is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Jan 2011
    Posts
    114
    Thank you for your reply. I am somewhat of a beginner. Can you please explain a bit further?
    Thanks again.

  4. #4
    Mohammad Siddiqali is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2011
    Location
    India
    Posts
    2
    Will you tell me your problem clrarly.i have a solution to solve it without entering more then one record in the table .We can update it and fill the data in the fields even without wrting query to if you say the problem clearly or mail me mdsiddiqali@hotmail.com.

    Thanks,
    MDSIDDIQALI.

  5. #5
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    I have tried to replicate the problem that you have have posted:

    I have a table Table1 with the following Fields:
    ID {Primary Key}
    Student_Id {Assuming ecah student has a definite ID that doesnot Change}
    DOB
    Score1
    Score2
    Score3
    {These Fields are the Examscore feilds}

    Assumptions:
    1) I have assumed each child has a unique Id
    2) Every Child has three Records one each for Score1,Score2,Score3 Respectively (pls refer to the sample attached).

    I have a simple select query where I have isolated each unique child ID using select Distinct the SQL as follows:

    SELECT DISTINCT Table1.student_Id, ExamScore([student_id],1) AS Expr1, ExamScore([student_id],2) AS Expr2, ExamScore([student_id],3) AS Expr3
    FROM Table1;

    Now I have a Function ExamScore which I use to Find use to Display the Scores in a single row:

    ExamScore([student_id],1)


    The Function as follows:
    Option Compare Database
    Dim intStudentID As Integer 'Student ID assuming unique for each student
    Dim intExamScoreNumber As Integer 'Indicates the score Number: 1=ExamScore1,2=ExamScore2,3=ExamScore3
    Dim strFieldName As String 'Name of the Field to be Search
    Dim strSQL As String
    Function ExamScore(intStudentID, intExamScoreNumber) As Integer
    If IsNull(intStudentID) Then 'Student ID Field required for the Search
    Exit Function
    End If
    'As there are only three Exam score Fields you cannot enter a number <=0 or >3
    If IsNull(intExamScoreNumber) Or intExamScoreNumber <= 0 Or intExamScoreNumber > 3 Then
    Exit Function
    End If
    'Feild name generated based on the intExamScorenumber 1=ExamScore1,2=ExamScore2,3=ExamScore3
    Select Case intExamScoreNumber
    Case Is = 1
    strFieldName = "Score1"
    Case Is = 2
    strFieldName = "Score2"
    Case Is = 3
    strFieldName = "Score3"
    End Select
    'Opening a record criteria is StudentID

    strSQL = "Select " & strFieldName & " From Table1 Where student_id=" & intStudentID
    Set rst = CurrentDb.OpenRecordset(strSQL)
    If rst.RecordCount > 0 Then
    rst.MoveFirst 'Looping through the records to find the scores
    Do While Not rst.EOF
    If rst.Fields(strFieldName) <> "" Then 'The real loop exit condition.
    ExamScore = rst.Fields(strFieldName)
    Exit Do
    End If
    rst.MoveNext
    Loop
    Else
    ExamScore = 0
    End If
    Set rst = Nothing
    End Function


    How does the function work:

    The function opens a record set based on the Student_ID Loops through the Fields Score1,Score2 or Score3 as specified by the intExamScoreNumber. the loops continues and breaks only if it finds a number otherwise returns a 0.

    How to make the function work:

    Create a calculative field with the Following expression:
    ExamScore(intStudentID, intExamScoreNumber)

    intStudentID=Field with the student_id
    intExamScoreNumber {Type 1 to display Score1, 2 for Score2 and 3 for Score3}

    Please refer to the sample attached see:
    Table1
    Query3
    Module1

  6. #6
    maximus's Avatar
    maximus is offline Expert
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Please remeber to change the names of the Fields and tables and use the names of the Tables and Fields in your database before using the codes.

  7. #7
    alpinegroove is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Jan 2011
    Posts
    114
    Thank you very much for your detailed reply and attached files. I am new to all of this, so I need to take some time to try and figure it out.
    I appreciate your help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Combining / Merging Records
    By alpinegroove in forum Access
    Replies: 8
    Last Post: 01-27-2011, 09:43 AM
  2. Combining Tables by Query
    By jlclark4 in forum Queries
    Replies: 9
    Last Post: 01-25-2011, 12:19 PM
  3. combining multiple records into one record
    By RedGoneWILD in forum Queries
    Replies: 5
    Last Post: 07-28-2010, 11:19 AM
  4. combining field from two records into one
    By RedGoneWILD in forum Queries
    Replies: 8
    Last Post: 07-13-2010, 09:47 AM
  5. Combining multiple Excel records into Access
    By Jadey78 in forum Import/Export Data
    Replies: 0
    Last Post: 05-04-2010, 07:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums