Update query will not work (1 Viewer)

[Jacqueline]

I need to pull data based on criteria from a read-only table into a table where data can be added. The read-only comes from our student management system. I built a query that pulls the data from the read table and then needs to update that data into another table that can be used to update data.

I have built an update query and as far as I can tell the syntax is correct. It will run but does not populate the data. (see attached image) I made sure the destination data types mated those in the read-table still no joy! :banghead:

If the solution is in coding visual basic I am not very literate in VB so lots of explanations would be appreciated. Hoping someone can tell me what I'm doing wrong.

 

[plog]

That makes syntatical sense, but not logical sense. Your query will only update the fields to values they alrealdy have. My guess is you haven't correctly linked your datasources in that query.

Perhaps you can demonstrate with data what you are hoping to accomplish. Can you post 2 sets?

A. Starting sample data from both your tables. Include table and field names and enough sample data to cover all cases.

B. Expected results of A. Show what you hope to end with when you use the data in A.

 

[Jacqueline]

I tried relating the query both ways, and neither worked. The data would look something like this:

yrq item SID
B564 4110 999476666
B564 4110 999476667
B564 4110 999476668
B564 4110 999476669
B564 4110 999476670

This would come from the student roster table on the SMS read-only table. The first query pulls the roster. (just the student SID for the quarter and course item number)

I need the update quarter to add these three fields to the Progress table where the instructor can then add a grade. These are students outside the normal systems in our ABE/ESL program where special grading takes place that helps the instructor plan the students progression through the various classes.

None of the three join options seem to fit my need. I've tried all. I get an error that says Must be an updatable query

Thanks for looking at this for me.

Jackie

 

[plog]

That's not enough data to help. I need to know what you are starting with (in both tables) and then what you hope to end with. No explanations, just data. You can post a spreadsheet or database with the data, or just use this format:

TableNameHere
Field1Name, Field2Name, Field3Name
Sally, 1/1/2013, 14
David, 2/3/2015, 32
Jim, 3/4/2001, 119

 

[apr pillai]

From the attached update query image it looks like you are trying to update the joined fields with the same data present in both table & query. If you are trying to update a field other than the joined fields then it make sense. But it is not shown, if it exists.

You are using Left Join to include all the records from the Query-Recordset and only those matching records in the table? If there are more than one record in the query matching the joined key only the last record value will overwrite all other records updated earlier.

 

[arnelgp]

show us the SQL query that was built from this query.