Access SQL Insert Assistance

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 

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.
 

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.
 
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top