Filling a column with 'missing' values 1

[teach314]

My table has 3 fields, A, B, and C. A & B form a compound PK, and C is initially empty.
There are exactly 4 values of B for each A. These 4 distinct values of B in each group are
listed in increasing order, and are taken from the set 1, 2, 3, 4, 5, 6, 7, 8.

My task is to fill column C with the values NOT taken from the set, listed in ASC order.
Column C is shown in its completed form below.

  A     B      C
----------------
 1     1      2
 1     3      5
 1     4      6
 1     7      8

 2     2      1
 2     3      5
 2     4      6
 2     8      7

 3     4      1
 3     5      2
 3     7      3
 3     8      6
 ...

Any help with filling column C is greatly appreciated.

 

[dhookom]

I would think a recordset or two would be required. The first thing I would do is create a couple queries to get all of the values 1-8 that are not in B for each A. I have a table of numbers [tblNum] with a single numeric field [Num] and a whole mess of unique numbers from 1 to thousands.

I created table [tblTeach314a] (apparently my sandbox database already had a table [tblTeach314]).

Your unused numbers query would be:

SELECT tblTeach314a.A, tblNums.Num
FROM tblTeach314a, tblNums
WHERE Num NOT IN (SELECT B FROM tblTeach314a A where tblTeach314a.A = a.A)
GROUP BY tblTeach314a.A, tblNums.Num
HAVING (((tblNums.Num) Between 1 And 8));

Then you would create some DAO recordsets and loop through the table and query of unused values to update the C field.

Do you need assistance with the DAO code?


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[teach314]

hi Duane - that's very clear. I probably could use some assistance with the DAO. Thanks for the offer!
Teach314

 

[dhookom]

I tested this code and it seemed to work. I used the previous SQL for the qselUnusedNumbers.

Public Function FillC()
    Dim db As DAO.Database
    Dim rsUnused As DAO.Recordset
    Dim rsToFill As DAO.Recordset
    Dim intCurA As Integer
    
    Set db = CurrentDb
    Set rsToFill = db.OpenRecordset("SELECT A, B, C FROM tblTeach314a ORDER BY A,B")
    'rsUnused.MoveFirst
    With rsToFill
        .MoveFirst
        Do Until .EOF
            intCurA = .Fields("A")
            Set rsUnused = db.OpenRecordset("SELECT A, Num FROM qselUnusedNumbers where A=" & intCurA & " ORDER BY Num")
            rsUnused.MoveFirst
            Do Until .Fields("A") <> intCurA
                .Edit
                    .Fields("C") = rsUnused.Fields("Num")
                .Update
                rsUnused.MoveNext
                .MoveNext
                If .EOF Then
                    Exit Do
                End If
            Loop
            rsUnused.Close
        Loop
        .Close
    End With
    Set rsUnused = Nothing
    Set rsToFill = Nothing
    Set db = Nothing

End Function

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[teach314]

hey Duane. Nice response!

 

[dhookom]

Sorry there were no comments but I expect the naming should make it easy to follow.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016