Access Update Query with duplicate rows in matching field

[sjhime]

My apologies if this has been asked before, but I'm not even sure how to search for this.

I have 2 tables tbl_Classrooms and tbl_Codes_2018. Relevant fields in tbl_Classrooms are [School], [School_District], [survey_type], [Online_Code], [DistrictID_SchoolID], and [ClassroomID]. The tbl_Codes_2018 relevant fields are [DistrictID_SchoolID], [code], and [Assigned]. There might be multiple records in tbl_Classrooms for that have the same [DistrictID_SchoolID] and the records are not static. Meaning, data is entered into the tbl_Classrooms table on a regular basis. The records in tbl_Codes_2018 are static and don't change (except for the [Assigned] field which is a yes/no field. There are 50 records per [DistrictID_SchoolID] in the tbl_Codes_2018 table and each row contains a unique value in the [code] field. I don't have unique tying fields for each table. I do have PK [tbl_Classrooms].[ClassroomID] and PK [tbl_Codes_2018].[CodeID] but, obviously, these do not match. I know why when I create a query the records are duplicated when I join by [DistrictID_SchoolID] but I was hoping there was a way to update [tbl_Classrooms].[Online_Code] with [tbl_Codes_2018].[code] by selecting the 1st matching record in [tbl_Codes_2018] and updating the 1st matching record in [tbl_Classrooms] and then moving to the next record in [tbl_Codes_2018] and repeat until EOF in [tbl_Classrooms].



I've thought of assigning a matching unique field in [tbl_Codes_2018] when a record is created in [tbl_Classrooms] (like assigning [tbl_Classrooms].[ClassroomID] to a field in [tbl_Codes_2018] like [tbl_Codes_2018].[tbl_Classrooms_ClassroomID] but not sure how I would assign that by row when matching on [DistrictID_SchoolID]. My apologies if this is confusing and I'm happy to clarify further. Thanks for the help!

[June7]

Perhaps you need cascading (dependent) combobox for entering data into tbl_Classrooms.

Select DistrictID_SchoolID in one combobox then the [code] choices in another combobox are limited by the first combobox.

Pretty much anything can be done with enough code. Are you saying tbl_Classrooms has 50 records for each DistrictID_SchoolID to accommodate the 50 records for each DistrictID_SchoolID in tbl_Codes_2018?

Why is table name including year? You have a different table for each year? Should be one table for all years with another field for year identifier.

Advise not to have multiple fields with exact same name.

[sjhime]

I had considered a cascading combobox for entering data into tbl_Classrooms, but I don't want the data entry user to have access to the codes in tbl_Codes_2018. I want them to be able to press a button (or have some other automated VBA procedure) to update [Online_Codes] in tbl_Classrooms (from tbl_Codes_2018.code) when records are added to that table.

There are varying number of records in tbl_Classrooms. For example, 1 school ([DistrictID_SchoolID]) might have 10 records, another might have 1 record, and another might have 40 records. However, there are always 50 records in tbl_Codes_2018 for each [DistrictID_SchoolID]. There are different (deprecated) tables for tbl_Codes by year. I only keep it that way because no codes from previous years are ever used again.

And, for what it's worth, I don't consider a field as [tbl_Classrooms].[DistrictID_SchoolID] the same name as field [tbl_Codes_2018].[DistrictID_SchoolID]. That's more of a philosophical issue.

[June7]

How should VBA know which code(s) to assign to a record? What are the rules?

Yes, the table prefix makes the field references distinct but building queries can be simplified and less confusing if the field names are not the same. At least, that's my experience.

[sjhime]

How should VBA know which code(s) to assign to a record? What are the rules?

Yes, the table prefix makes the field references distinct but building queries can be simplified and less confusing if the field names are not the same. At least, that's my experience.

That's what I'm struggling with. I was imagining matching on [DistrictID_SchoolID] and then just using the code from row 1, then mark that as Assigned, then use the code in the next row, until EOF in tbl_Classrooms. Seems like that's possible, but I can't figure out the VBA.

[June7]

What criteria would you use to sort the records in [DistrictID_SchoolID]? Alphanumeric on [code] field? Is that what determines the 'next' code to use? Are the codes unique for each school - not the same code used for multiple schools?

Maybe VBA something like:

Dim rs AS DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT TOP 1 [code] FROM DistrictID_SchoolID WHERE SchoolID=" & Me.cbxSchool & " AND [code] NOT IN (SELECT Online_Codes FROM tbl_Classrooms) ORDER BY [code];", dbOpenDynaset, dbReadOnly)
Me!Online_Codes = rs!Code

[sjhime]

Codes are unique by classrooms. 50 codes per school. Sort order doesn't matter as I don't care what order they are assigned. I'll try your VBA. Thank you.

[June7]

Whoops, look at previous post, did an edit on the SQL statement.

[sjhime]

Whoops, look at previous post, did an edit on the SQL statement.

Ok. Here's what I did, in case others are interested. Thank you June7 for the advice!

Private Sub Form_BeforeUPdate(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String


If Me!Survey_Type = 2 And IsNull(Online_Code) Then
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT TOP 1 [code] FROM tbl_Codes_2018 WHERE DistrictID_SchoolID = '" & Me.DistrictID_SchoolID & "' AND" _
& " [tbl_Codes_2018].[Assigned] = False ORDER BY tbl_Codes_2018.[code];", dbOpenDynaset)
        If (rs.RecordCount = 0) Then
            MsgBox "No School in Code table", vbOKOnly, "No Such School"
            Exit Sub
        Else
        
            Me!Online_Code = rs!code
            
            'Turn warnings off so user isn't prompted by row update.
            DoCmd.SetWarnings False
            'Update Assigned field to True after code assigned.
            DoCmd.RunSQL ("UPDATE tbl_Codes_2018 SET tbl_Codes_2018.Assigned = TRUE WHERE Code = '" & Me.Online_Code & "'")
            'Update Date Assigned field with date after code assigned.
            DoCmd.RunSQL ("UPDATE tbl_Codes_2018 SET tbl_Codes_2018.[Date Assigned] = Date() WHERE Code = '" & Me.Online_Code & "'")
            DoCmd.SetWarnings True
            
            rs.Close
            
            Set rs = Nothing
            Set db = Nothing
        End If
    End If
End Sub