Cant update the Query I have created

[andy49]

I have a table containing Student_info including a Target field and Draft_tier.

I have another table Pap_info which contains fields Target and Tier.

I need to lookup the [Student_info].[target field] value in the Pap_info table and return the Tier value to be stored in the Draft_tier column (as a sort of default).

My initial efforts resulted in the following code which creates the datasheet I need but the data cant be edited.

strSQL = SQLSTR(Me.Combo_class.Value, Me.Yeargp.Value)
fieldtest = "[Mathematics_Group]"
group = Me.Combo_class.Value
Yr = Me.Yeargp.Value
For Each qdf In CurrentDb.QueryDefs
 If qdf.Name = "newquery" Then
     CurrentDb.QueryDefs.Delete "newquery"
      Exit For
   End If
Next
strSQL = "SELECT LOWER_SCHOOL_Students.[First name], LOWER_SCHOOL_Students.[Last name], LOWER_SCHOOL_Students.Mathematics_Group,   Nov_tier.exam_tier_dft " _
            & " FROM LOWER_SCHOOL_Students LEFT JOIN Nov_tier ON LOWER_SCHOOL_Students.[Mathematics_Target] = Nov_tier.grade" _
            & " WHERE (((LOWER_SCHOOL_Students.[Mathematics_Group])=" & Chr(34) & group & Chr(34) & "));"
Set qdf = CurrentDb.CreateQueryDef("Newquery", strSQL)
DoCmd.OpenQuery "NewQuery", 0, acEdit

all help gladly appreciated.

[aytee111]

Access is very picky about Update queries, only the most basic queries allow updating. You could put the LEFT JOIN into a subquery or else you need to make a temp table.

[CJ_London]

not tried, but so long as you are only editing data in the LOWER_SCHOOL_Students table, try changing the form recordset type to dynaset inconsistent updates - might work

you won't be able to edit nulls on the Nov_tier table because the record doesn't exist to edit

[andy49]

Thanks for this advice. Clearly whilst I'm grasping some general code stuff, im still learning what does and doesn't work. I love the idea of temporary tables so that's my next step.

[John_G]

When you want to be able to edit a multi-table query, at least one of the fields (preferably both) used in the join expression must be indexed.

Try putting indexes (they don't have to be unique) on Students.[Mathematics_Target] and nov_tier.grade.