SQL/access question: Do not append that student-term record if the student AND the term already exist!

Question

I have another SQL/access 2007 question that seems really basic but I'm not sure how to Google for it.

I have this table STUDENT-TERMS with the following fields:

StudentTermID

StudentID (links to STUDENT table)

TermID (links to TERMS table)

TermGPA (this is the nugget of info that needs to be recorded)

STUDENT table looks like this:

StudentID

Name

TERMS table looks like this:

TermID

Start date

End date

I have a document with all of the info that gets uploaded into the Access doc. This document that I get has the same student and term IDs as exist in the database.

Then I can run an append query to get the data into the STUDENT-TERMS table. Which is fine.

How do I write the query such that there is only one record of each term for each student? So for example I have this data in the table right now:

StudentTerm ID: 5

StudentID: Tara

TermID: 1011Autumn

TermGPA: 3.8

When I upload and append a new document, it might still contain that information about Tara and I want the db to say "Hey, we already have that, skip it" as it does the appending. How do I indicate that?

HERE IS THE CONCLUSION/ANSWER/RESOLUTION: Thanks for your help everyone. This is what I wound up doing:

1. I made a new field in the table called StudentTerm which contains a concatenation of the StudentID and the Term (like so: Tara1011Autumn). This field is indexed with no duplicates.

2. The query that performs the appending looks like this:

INSERT INTO StudentTerms ( StudentID, TermID, GPA, StudentTerm ) SELECT Upload_Students.StudentID, Upload_Students.TermID, Upload_Students.GPA, [Upload_Students]![StudentID] & [Upload_Students]![TermID] AS Expr1 FROM Students INNER JOIN Upload_Students ON Students.StudentID = Upload_Students.StudentID;

This query attempts to fill the StudentTerm field with Tara1011Autumn. If that string already exists in the table, it won't append the record.

Answer 1

Assuming you are doing a SQL based append query, you can use a NOT EXISTS clause.

Note
I'm assuming here that you can have multiple STUDENT-TERMS records for the same student if the StudentTermID is different.

INSERT INTO STUDENT-TERMS
          ( StudentTermId
          , StudentId
          , TermID
          , TermGPA )
     SELECT 5
          , 'Tara'
          , '1011Autumn'
          , 3.8
      WHERE NOT EXISTS
            (SELECT * FROM STUDENT-TERMS WHERE StudentID = 'Tara'
                                           AND StudentTermId = 5)

Answer 2

*Assumption database is in the 3NF *

SELECT st.StudentId, s.StudentName, t.TermId, t.TermName, st.TermGPA
FROM STUDENTS_TERMS st
INNER JOIN STUDENT s ON st.StudentID = s.StudentId
INNER JOIN TERMS t on st.TERMS = t.TermId
WHERE st.StudentID = 5

Answer 3

Based on dcp's answer but adjusted for Access

INSERT INTO STUDENT-TERMS
      ( StudentTermId
      , StudentId
      , TermID
      , TermGPA )
 SELECT DISTINCT 5
      , 'Tara'
      , '1011Autumn'
      , 3.8
  FROM STUDENT-TERMS
  WHERE NOT EXISTS
        (SELECT * FROM STUDENT-TERMS WHERE StudentID = 'Tara'
                                       AND StudentTermId = 5)

Answer 4

Create a unique index on the [student-terms] table, with the StudentID and StudentTermID fields in the index.

Then, when you run the append query Access will tell you "# records couldn't be added due to key violations", before asking you if you want to continue. If you hit Yes in that dialog box, then all the non-violating records will be appended, and the offending records will be saved into a different table.

There are other options available in the DoCmd.RunQuery action if you're doing this from VBA to automate this.