Results 1 to 15 of 15

Thread: code to fill

  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: code to fill

    All, I am using Access 2003. I have a table that was imported from a text file. There are fields that need to be filled in from the previous record because it was left blank. For Example: If the first record consist of the fields: acctno, Name, Date Open and Date Close. If the next record is the same account number it is not repeated. Just the date open and date close or only whats different. the table consists of over 20000 records and I cant manually fill what is needed. Does anyone have query code that will fill in the previous value
    Thanks

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    I don't think a query is going to do what you want. You could open the table as a recordset in vb and loop through to fill in the empty data.

    Create variables for each field that could be empty, fill them with the values from the first record (assuming the first record will have data in all fileds). Test the next record for data in the suspect fields: no data - fill from variables, has data - update value of variables. Keep going until you reach the end of the recordset.

  3. #3
    Join Date
    Jul 2004
    Posts
    214
    I am not familiar with vb and looping. Can you give me an example of how to set this up please.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by slimjen
    I am not familiar with vb and looping. Can you give me an example of how to set this up please.

    you need to use either ADO or DAO recordsets
    find out the correct method / calls to define and open a recordset.
    dim rsData as recordset 'define our recordset placeholder

    open you recordset making certain that its in the correct order so you know which records follow which so you updtae the balnks with the correct values

    defiene the variables to be copied
    dim myvar1 as "whatever datatype"
    ..

    then iterate through the rcordset using somethign like
    while rsdata.eof = false
    do a test to see if this reord has valid data in if so copy it to the working variables myvar1.......
    if not copy edit the record
    assign the workign values back to the recordset
    rsdata.edit
    rsdata.mycolumn1=myvar1
    rsdata.update
    wend 'keep processing

    so have a look a the help system for opening recordsets, use either ADO OR dao, doesn't matter which but you can't use ADO properties and methods in a DAO recordset and vice versa
    have a look at the properties in either ADO/DAO, looking for .eof
    have a look at the methods in either ADO/DAO, looking for, edit, update amongst others

    make sure you do your update on a copy of the data, untill you have proved it works.

    consider what you are going to do if the first record is blank.

    HTH

  5. #5
    Join Date
    Jul 2004
    Posts
    214
    Ok. I took your suggestion and heres what I got to work:

    Code:
    Private Sub NullField_click()
    Dim i As String
    
    Set myDb = CurrentDb()
    Set MyRs = myDb.OpenRecordset("MissingValues") 
    MyRs.MoveFirst
    Do While Not MyRs.EOF
    MyRs.Edit
    For Each myfld In MyRs.Fields
    If myfld.Name = "Account" Then 
    If MyRs(myfld.Name).Value <> "" Then
    i = MyRs(myfld.Name).Value
    Else
    MyRs(myfld.Name).Value = i
    MyRs.Update
    End If
    End If
    
    Next myfld
    
    MyRs.MoveNext
    Loop
    
    End Sub
    The only thing is that this only works great with one table and field. I need it to work with three tables and their corresponding three or more fields. I tried nesting but I can get the correct format to work. The alternative is to repeat the code over and over. I don't want to do this unless it's no other way. I also tried select case but I couldn't get that to work either. Can you show me how to turn this into a nested loop.
    Thanks

  6. #6
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    I would set up a separate routine for each table and define a variable for each field that may be missing data rather than looping through the fields.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    just thinking about this... couldn't it be done with an update query

    first off select the appropriate right values.

    use a sub query or another query to encapsulate that
    say we call this query validaccountnumbers
    select distinct(Name, Date Open and Date Close), accountno from mytable where len(accountno)>1

    then use the output of that as the feed into the main update query
    update mytable set mytable.accountno=validaccountnumbers.accountno where validaccountnumbers.name=mytable.name and validaccountnumbers.dateopen=mytable.dateopen and validaccountnumbers.dateclosed = mytable.dateclosed


    just air code.... not tested, not really fully thought about it.

    it would be a heck of lot quicker and more reliable than a recordset iteration

    providing you have some common information you can always use SQL to manipulate data based on values elsewhere, often in the same table already.

  8. #8
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    An update query definately sounds easier, but the imperession that I got from the original post was that the 'key' fields were empty and the only way to determine what values belonged in the fields was based on the record order of the file.

  9. #9
    Join Date
    Jul 2004
    Posts
    214
    guys,
    This particular code works as I want but I need it to work with multiple fields. I was thinking I can use an array for the field names but not sure how to set it up. Not only do I need to reference ClientNo, i also need to reference AcctNo and DateClosed. How do I do this please?
    Thank you

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    best if you let us know what the tables look like
    AND
    what the data looks like
    AND
    what information you have that allows you to match up row A with Row B

    if you can supply a cut down version of your data, suitably masked so the suits don't get too upset, then that'll help.

  11. #11
    Join Date
    Jul 2004
    Posts
    214
    Please help. This code works like I need it too but it takes a long time, about 40-45mins to loop through a table with 150000 records or more
    Code:
    Private Sub CopyNullField(tblTableName As String, strSearchField As String)
    On Error GoTo Err_CopyNullField
    Dim i As String
    
    Set myDb = CurrentDb()
    Set MyRs = myDb.OpenRecordset(tblTableName)
    MyRs.MoveFirst
    Do While Not MyRs.EOF
    MyRs.Edit
    For Each myfld In MyRs.Fields
    If myfld.Name = strSearchField Then
    If MyRs(myfld.Name).Value <> "" Then
    i = MyRs(myfld.Name).Value
    Else
    MyRs(myfld.Name).Value = i
    MyRs.Update
    End If
    End If
    
    Next myfld
    
    MyRs.MoveNext
    Loop
    
    Exit_CopyNullField:
        Exit Sub
    
    Err_CopyNullField:
        MsgBox Err.Description
        Resume Exit_CopyNullField
        Resume
    End Sub
    Any suggestions? thanks

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well you're performing a nested loop (a loop within a loop!), so if your table had 10 fields in it and 150000 rows, you're looking at 1500000 recursions!

    There is a chance you can do this with a set based method (that means no loops!), but you're goign to have to provide full details of the table and some sample data.

    I had a very similar problem I posted today in fact (yes it was in SQL Server, but it may be useful to you); here's the link http://www.dbforums.com/showthread.php?t=1628959
    George
    Home | Blog

  13. #13
    Join Date
    Jul 2004
    Posts
    214
    Ok This is a sample of my table structure:

    Code:
    Acct#	Name				Balance		Flag  Reason
    
           338721	Davis, CAROL                       98,885.93
            49848 	Green, ROSEMARY                    908,064.93    22
                                                                     90
            5952	 MESS, ROG           		   809,541.78    22
                                                                      1 18
           12345678 HOuu, RICky                         796,528.43
           98765432 HOLLoo, REGgui	                    776,624.07
         5678545356 MALggg, JAmes                       708,605.32
          555464666 SMgr, JO                            635,881.57
        				                    629,402.92
           67676767 WRuu, DAN                           593,367.88
        44444444444 GRIF, JO                            556,769.42    22
                                                                      90
                                                                      90
                                                                      90
                                                                      90
         33333333 ADg, R Kiop                           550,493.73   102
                                                                       1 18
         8787878787 yongde, sarah                       534,970.64
         			                            532,611.81    90
          545454544 WILLyy, SA	                    528,277.72
         4567834564 CERn		                    528,035.88    90
                                                                      91
                                                                      91
                                                                      91
          222467887 Tony LIssssss J                     521,180.15
        
    156925 records listed
    I need to fill in the null fields with the data from the previous row in the Acct#, Name and Balance fields. The Acct# field's data type is text. It is imported into a table. I was trying to see whether a query would work for this. I hope this helps you help me. Thanks

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i don't see a set-based solution.
    here is the result of a test looping a recordset:

    Starting: 04/04/08 23:03:16
    Done: 04/04/08 23:03:16
    Records: 16391
    Fields: 4
    Edits: 39

    i.e. it went thru my test db with 16 kilorecords and 4 fields in not more than a second.
    it is difficult to extrapolate to your 150 kilorecords, but i guess it should beat your 45 minutes.
    sorry i don't have a big db in my hands at the moment to sacrifice to testing.

    my code:
    Code:
    Private Sub butGo_Click()
        Dim dabs As dao.Database
        Dim recs As dao.Recordset
        Dim rfld As dao.Field
        Dim stuff() As Variant
        Dim i As Integer
        Dim j As Integer
        Debug.Print "Starting: " & Now()
        Set dabs = CurrentDb
        Set recs = dabs.OpenRecordset("SELECT * FROM DummyClone")
        For Each rfld In recs.Fields
            i = i + 1
        Next
        ReDim stuff(0 To i - 1)
        With recs
            .MoveFirst
            'the first record must MUST ***MUST*** have all fields filled
            i = 0
            For Each rfld In .Fields
                stuff(i) = rfld.Value
                i = i + 1
            Next
            .MoveNext
            i = 0
            Do While Not .EOF
                i = 0
                For Each rfld In .Fields
                    If LenB(Nz(rfld.Value, vbNullString)) = 0 Then
                        j = j + 1
                        .Edit
                        rfld.Value = stuff(i)
                        .Update
                    Else
                        stuff(i) = rfld.Value
                    End If
                    i = i + 1
                Next
                .MoveNext
            Loop
        End With
        Debug.Print "Done: " & Now()
        Debug.Print "Records: " & recs.RecordCount
        Debug.Print "Fields: " & UBound(stuff) + 1
        Debug.Print "Edits: " & j
        recs.Close
        Set recs = Nothing
        Set dabs = Nothing
    End Sub
    no guarantees that it is doing what you want: it is supposed to carry down the last not(null/empty) field.

    if you give it a try, please let me know how long it takes (and if it works!!!)

    izy
    currently using SS 2008R2

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah- the complication for the set based solution is the "previous record" part. There's nothing in the data, other than the order of the data in the file, to indicate "previous". Anything set based would I suppose need to preserve order and use an autonumber or similar. I reckon let the OP test Izy's code. If the result is too slow then let battle of Iterative Vs Set Based programming commence!

    BTW - just noticed in the data - if an account number never occurs again later in the dataset then set based is trivial afterall.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •