Solved

MS Access

Posted on 2014-12-24
11
131 Views
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!
0
Comment
Question by:Sbovino
11 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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?
0
 

Author Comment

by:Sbovino
Comment Utility
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.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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

Open in new window

0
 

Author Comment

by:Sbovino
Comment Utility
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.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
0
 

Author Comment

by:Sbovino
Comment Utility
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,
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
Comment Utility
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

0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
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
0
 

Author Closing Comment

by:Sbovino
Comment Utility
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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Glad I could help Steve.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now