MS Access

This sounds more like an append query than an update query, or do you have separate columns for the school year?  If you do, then you are using your database like a spreadsheet, and should rethink your table structure.  

Can you take a screen shot of the table in design view, so we can see the field names and data types?

I basically recreate a set of distinct records for every school year.  I filter on the years fro reporting purposes.  The following fields uniquely identify records:

schoolid, classid and schoolyear.  In my table there will be records for each school year which are created with an append.  I have appended for 2015-2016 school year

So I am trying to update the 2015-2016 set of data with the fields above from the 2014-2015 set of data as part of a rollover process.

Q1: Do you maintain data for 2014-2015, 2016-2016, etc. in the same table?

When you say you append, this seems to suggest you have data for 2014-2015 and appended 2015-2016 to it. Which means now you will have difficulty distinguishing what 2014-2015 data and what is 2015-2016 because they all are labeled 2014-2015.

this is what you need to do:
1. Start a new query from your table and include all fields. in the design view, select "View/SQL View" on the menu bar under "File". Then copy and paste the SQL Script for us to see. This way you provide us critical info we need without exposing your data.

2. Open your append query you write about in design view, and paste its SQL script also.

My guess is the append query you have will work as with minor modification to it as described below:

Append query, adds some data from table1, for example, to table2, or from table1 onto itself. Here what seems you are trying to do copy the exact records onto the same table but change 2014-2015 to 2015-2016 in the process (sort of append/update effect). If this is what you are trying to do, in the design view of you append query you have:

As Dale said, this is an APPEND query not an UPDATE query.  It is important to learn the terminology.  Update queries, modify values in existing rows.  Append queries add new rows.  Delete queries delete rows.

Start with the QBE.  Select the table that contains the data you want to copy.  Select the columns you want to copy.  Add criteria to select the set of records you want to copy.  Test the query to make sure it is selecting what you want.  Then go back to design view and change the query to an append query.  You will need to add a variable to supply the foreign key value for the new student.  Since you didn't post your schema, I'll post a query from one of my apps.  It is a survey type set of data.  When a survey is started for a client, the app copies the survey questions and appends them to the answer table and includes the ID from the form that is needed to link the answers to the client.

INSERT INTO tbl30DayRevAnswers ( RevQuesID, Rev30DayID )
SELECT tblReviewQuestions.RevQuesID, [forms]![frmClients]![sfrm30DayReviewList].[Form]![Rev30DayID] AS Expr1
FROM tblReviewQuestions
WHERE (((tblReviewQuestions.ActiveFlg)=True));

This example handles the appends one at a time.  It sounds like you want to do the appends as a batch.  That makes it a little more complicated.  You will need a code loop to read through the students and select those that will be added to the next school year and for each student run the append query.  So in the above example, each time the query runs, the Rev30DayID field would have a new value because you passed it in.

Dim db as DAO.Database
Dim qd as DAO.QueryDef
Dim rs as DAO.Recordset
Dim strSQL as String

    Set db = CurrentDB()
    set td = db.Tabledefs!TargetTableName
    Set qd = db.Querydefs!SelectStudentsQuery
    Set rs = qd.OpenRecordset
    Do Until rs.EOF
        strSQL = "Insert Into yourtable(fld1, fld2, ...., FK)
        strSQL = "Select fld1, fld2, ...., " & rs!FK & "
        strSQL = "From yourtable Where ......"
        db.Execute strSQL
        rs.MoveNext
    Loop

Select allOpen in new window

Q1: Do you maintain data for 2014-2015, 2016-2016, etc. in the same table?  Yes

The table has 300 records for each school year.  I have one of my SQL update queries below.  So for each year there will be about 300 records which are identified by year.  I am trying to take the teacherfte column associated with the year 2014-2015 and update the teacherfte column associated with the year 2015-2016.  I have already run the append operation and the 2015-2016 records are in the table.  The structure is such that the records I need to update can be identified by the year and uniquely by  trschool_id, grade_subjectid.

 I tried using a make table query with the teacherfte, trschool_id, grade_subjectid and tryear for the year 2014-2015.  The table created but any syntax I tried to update the rollover_table with data from the teacherresources table failed.  Also below.  Thanks for any insight.

INSERT INTO TeacherResources ( tryear, trschool_id, grade_subjectid, TeacherFTE, Strudent_enroll, displayorder )
SELECT =[Forms]![system_setup]![text22] AS tryear, schools.school_id, gradelevels.grade_subjectID, 0 AS teacherfte, 0 AS strudent_enroll, gradelevels.displayorder
FROM gradelevels, schools
WHERE (((schools.school_id) Between 1 And 8) AND ((gradelevels.grade_subjectID)<>12) AND ((gradelevels.level_school)="1"))
ORDER BY gradelevels.displayorder;

Select allOpen in new window

rollover_table Update SQL produced error "operation must use an updateable query

UPDATE TeacherResources SET TeacherResources.TeacherFTE = (Select TEacherFTE from rollover_table WHERE (((TeacherResources.grade_subjectid)=[rollover_table].[grade_subjectid]) AND ((TeacherResources.trschool_id)=[rollover_table].[trschool_id]) AND ((TeacherResources.tryear)="2015-2016")))
WHERE (((TeacherResources.tryear)="2014-2015"))

Select allOpen in new window

This query showed the right number of rows and data in datasheet view but failed on execution as noted above.  At this point I would be happy being able to update the teacherresources table from the rollover_table table.

At what point did you add the SY15-16 data?  That would have been the best time to do this, as you would have been able to create an append query with all of the appropriate values from SY14-15, instead of inserting just schoolid, classid and schoolyear, you could have added the FTE and enrollment columns into that query.

If you have not already added values to other fields for SY15-16, then I would just delete those records and append a new set of records which include those columns.

Dale

Dale:

I can easily delete the set of records.  When I do the append it leaves the teacherFTE column blank.  How do I as part of the append, update teacherFTE with the value from the 2014-2015 school year.  This is really the crux of the matter.  Thanks,

I have an article that applies to this type of situation here
What you are looking to do is pull existing data into a place where you can work with it, confirm it, edit it, and then append it to a permanent location.  That's EXACTLY what this article is all about.
Have a look at it, and if it is an approach you'd like to pursue, post back here again and I'll give you a hand with any specifics that you need help in tackling.

Nick67

Dale:

Actually I had a form with the date.  Once I edited your query it worked very nicely!  The data is now rolled over for the next year.  Thank you.

Steve

Glad I could help Steve.