MS Access

Posted on 2014-12-24
Last Modified: 2014-12-24
I am having trouble visualizing an update query I wish to create.  I have a table called teacher resources which contains data from multiple sets of years.  As a new school year is approaching I want to “rollover” certain fields into the corresponding fields in the new year.  Example:

From the school year 2014-2015 I want to copy the fields FTE and enrollment to the records for school year 2015-2016.

Can you get me started? Thanks!
Question by:Sbovino
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40516407
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?

Author Comment

ID: 40516414
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.
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40516501
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:Append Query
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

LVL 35

Expert Comment

ID: 40516522
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

Open in new window


Author Comment

ID: 40516530
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;

Open 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"))

Open 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.
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40516569
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.


Author Comment

ID: 40516610

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,
LVL 47

Accepted Solution

Dale Fye (Access MVP) earned 500 total points
ID: 40516657
the Append query would look something like:
INSERT INTO TeacherResources (school_id, class_ID, schoolyear, TeacherFTE, enrollment)
SELECT school_id, class_id, "2015-2016", TeacherFTE, enrollment
FROM TeacherResources
WHERE schoolyear = "2014-2015"

Open in new window

I would copy your TeacherResourses table and use the copy to do this initially, to make sure it meets your needs.  Once you are satisfied with the query, I would create a form which allows you to do this automatically.  You would add a combo box to the form to display the schoolyear records you want to copy, then add a textbox for the new value for [schoolyear] field.  Then you could execute the query with the click of a command button.
INSERT INTO TeacherResources (school_id, class_ID, schoolyear, TeacherFTE, enrollment)
SELECT school_id, class_id, Forms!yourFormName.txtNewSchoolYear, TeacherFTE, enrollment
FROM TeacherResources
WHERE schoolyear = forms!yourFormName.cboCopySchoolYear

Open in new window

LVL 26

Expert Comment

ID: 40516659
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.


Author Closing Comment

ID: 40516717

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.

LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40516789
Glad I could help Steve.

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question