Append Query problem -- producing WAY too many records.

[Richard Hollenbeck]

I have a temp table as a work-around because of the need to get data from
several unrelated tables. I don't want to change my relationships at this
time. I think I already have them right. I want to use this table to
insert grades into a classroom activity.

The studentScores table has a composite key of studentID and activityID and
one other field called score.

The temp table was build by an INSERT INTO query and multiple UPDATE queries
from various related tables. The temp table has nine fields: courseCode,
groupID, activityID, activityDescription, score, studentID (AutoNum),
lname, fname, studentNumber (user entered, not key).

The database is under construction, but I have one class entered for sample
data. That class has 20 students. I updated the scores in the temp table
from the studentScores table before I entered the grades which went into the
temp table. This is all done with queries and VBA. Now I have a temp table
with 20 students in it. Now I want to update/append the studentScores
table. If the records don't yet exist, they need to be appended. If grades
have already been entered in the past the records in studentScores need to
be updated. So I wrote the following query:

INSERT INTO studentScores ( studentID, activityID, score )
SELECT temp.studentID, temp.activityID, temp.score
FROM studentScores, temp;

My thinking is that the composite key won't be satisfied until a student is
matched with an activity so the record will be blank until adding a score to
an activity for a student. So I want to write these grades to the table.
In case a record exists I will need to update that data instead.

The problem with this query is that it offers to insert 2960 rows!!!!! The
studentScores table only has about 150 records!

I just got to thinking that I ought to make a composite key for the temp
table. The problem is that the components of that combined key comes from
different tables at different times (from different queries). Therefore, no
one of those queries will provide both halves of the key to make a new
record.

What is wrong with my query? I have a similar problem with the update
query; but when I solve this one, I may have also solved the other.

Thanks. Rich Hollenbeck

 

[Richard Hollenbeck]

Never mind and thank you. I figured it out. It was a stupid mistake:

INSERT INTO studentScores ( studentID, activityID, score )
SELECT temp.studentID, temp.activityID, temp.score
FROM studentScores, temp;

should have been:

INSERT INTO studentScores ( studentID, activityID, score )
SELECT temp.studentID, temp.activityID, temp.score
FROM temp;

The query wanted to recursively insert the new data to itself. I took out
the studentScores table from the FROM clause and then it worked correctly.

Richard Hollenbeck

 

[John Vinson]

INSERT INTO studentScores ( studentID, activityID, score )
SELECT temp.studentID, temp.activityID, temp.score
FROM studentScores, temp;

My thinking is that the composite key won't be satisfied until a student is
matched with an activity so the record will be blank until adding a score to
an activity for a student. So I want to write these grades to the table.
In case a record exists I will need to update that data instead.

The problem with this query is that it offers to insert 2960 rows!!!!! The
studentScores table only has about 150 records!

Since you do not have any JOIN clause in the query, Access will
combine every record in StudentScores with every record in Temp, using
every possible combination.

Just remove StudentScores from the FROM clause. An Append query
usually has one table in its INTO clause (the table INTO which you
wish to insert data) and a different table (or tables) in the FROM
clause - you don't need to have the target table in both places!

If you have a unique Index such as a Primary Key on the combination of
StudentID and ActivityID the append will fail for records which
already exist; to update existing records, don't use an APPEND query
but rather an UPDATE query:

UPDATE StudentScores
INNER JOIN Temp
ON StudentScores.StudentID = Temp.StudentID
AND StudentScores.ActivityID = Temp.ActivityID
SET StudentScores.Score = Temp.Score;

This will update only existing records and not add new ones - so you
can just run both queries.

 

[Richard Hollenbeck]

Thank you. I pasted your UPDATE query after my INSERT INTO (append) query
and ran them consecutively. It worked. Those records that already existed
weren't duplicated (no dups allowed) in the append query, but those same
records were updated in the second update query. I don't suppose there is a
way to combine them into one query; but even if I could,, why would I want
to? This works fine. It's certainly much clearer than one big complicated
query. I have two short simple queries that I can add comments to, etc.