Go Premium for a chance to win a PS4. Enter to Win


MS Access

Posted on 2014-12-24
Medium Priority
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 49

Expert Comment

by:Dale Fye
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 34

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

LVL 40

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 49

Expert Comment

by:Dale Fye
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 49

Accepted Solution

Dale Fye earned 2000 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 49

Expert Comment

by:Dale Fye
ID: 40516789
Glad I could help Steve.

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

927 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