Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Filling a column with 'missing' values 1

Status
Not open for further replies.

teach314

Technical User
Jul 29, 2011
183
CA
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.

Code:
  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.
 
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:
SQL:
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
 
hi Duane - that's very clear. I probably could use some assistance with the DAO. Thanks for the offer!
Teach314
 
I tested this code and it seemed to work. I used the previous SQL for the qselUnusedNumbers.

Code:
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top