1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access SQL Insert Assistance

Discussion in 'Business Applications' started by MasterNe0, Mar 3, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. MasterNe0

    MasterNe0 Thread Starter

    Joined:
    Jun 23, 2003
    Messages:
    161
    I need to insert a value into a column labeled "List_Number" for table "MASTER_List"' for a range of rows.

    Right now the entire column values is BLANK. I need to insert the value of 1 to this column, rows 1 = 45000

    I need to do this second times with but different values and different rows in the same column so that rows 45001 to 90000 is 2, etc.

    How can I do this with a SQL statement in the access.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The first pass setting the value to 1 can be done using SQL using a simple "Update Query".
    To do the second pass to update to 2 would require the SQL to have a Key Index record to work with so that it only updates key records > 45000.
    The other way to do this would be to use VBA code.
     
  3. MasterNe0

    MasterNe0 Thread Starter

    Joined:
    Jun 23, 2003
    Messages:
    161
    I was attempting to use something like
    UPDATE MASTER_List SET Master_List.List_Number = 1 WHERE Master_List.List_Number > 1 AND Master_List.List_Number < 45300

    thinking that might do it.

    Is their any other way for me to insert 1 in the empty table from rows 1 - 45300 and then 2 for rows 45301 to etc, and so forth in my access table.
     
  4. RKoch95

    RKoch95

    Joined:
    Mar 21, 2011
    Messages:
    18
    MasterNeO

    I hope I understood your problem. Here is a VBA solution that should work. Please note that in the first loop I simply create some data to populate my test table called {MASTER_List}. That table contains two fields: [List_Number], which is blank, and [Sample_Data] which I populate (leaving [List_Number] blank) with a set of numbers from 1 to 150,000. I only used number so I could easily see that the case statements were working correctly.

    The important part is the second loop with the "CASE" statements and must include the rstMstList.MoveLast, rstMstList.MoveFirst and FinalCount = rstMstList.RecordCount process.

    After counting the records in the table, the code simply loops through the table and, based on the value of the loop counter 'x', assigns the number 1,2,3,4 or 5 depending on that value.


    Sub SortandNumber()

    Dim FinalCount As Long
    Dim x As Long
    Dim Sample_Data As String

    '//==== OPEN SOME RECORD SETS ====\\
    Set db = CurrentDb()
    Set rstMstList = db.OpenRecordset("MASTER_List", dbOpenDynaset)

    '//==== This just puts some fake data into the field [Sample_Data] in my example table ====\\
    For x = 1 To 150000
    With rstMstList
    .AddNew
    ![Sample_Data] = x
    .Update
    End With
    Next x

    '//==== Count the records in the table. Should be 150,000 - surprise! ====\\
    rstMstList.MoveLast
    rstMstList.MoveFirst
    FinalCount = rstMstList.RecordCount

    '//==== Loop through the table and select the value of 'x' to select case ====\\
    For x = 1 To FinalCount

    Select Case x
    Case Is <= 45000
    With rstMstList
    .Edit
    ![List_Number] = 1
    .Update
    End With

    Case 45001 To 90000
    With rstMstList
    .Edit
    ![List_Number] = 2
    .Update
    End With

    Case 90001 To 135000
    With rstMstList
    .Edit
    ![List_Number] = 3
    .Update
    End With
    Case 135001 To 180000
    With rstMstList
    .Edit
    ![List_Number] = 4
    .Update
    End With
    Case Else
    With rstMstList
    .Edit
    ![List_Number] = 5
    .Update
    End With
    End Select

    rstMstList.MoveNext
    Next x

    MsgBox "Done!!"


    End Sub


    Hope this helps
    RonK
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1144122

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice