Copy rows based on field

Question

Hi

I have table that contains rows of sampleID  with a volume. ie

SampleID, Volume,  Cons., 260/280, 260/230
K5050, 7500, 100, 2,3

I wich that volume is not >4500 per row
So I want Access to update table so that the volume is not >4500. Ie

SampleID, Volum,  Cons., 260/280, 260/230
K5050, 4500, 100, 2,3
K5050, 2000, 100, 2,3

Is this possible how can this be done?
Regarde Geir Arne

Answer 1

You could run a VBA procedure:

Sub SplitRecords()
    Const lngMax = 4500
    Const strTable = "MyTable"
    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Set dbs = CurrentDb
    strSQL1 = "SELECT * FROM [" & strTable & "] WHERE Volume>" & lngMax
    Set rst = dbs.OpenRecordset(strSQL1, dbOpenDynaset)
    Do While Not rst.EOF
        Do While rst!Volume > lngMax
            strSQL2 = "INSERT INTO [" & strTable & _
                "] (SampleID, Volume, [Cons.], [260/280], [260/230]) " & _
                "VALUES (" & Chr(34) & rst!SampleID & Chr(34) & ", " & _
                lngMax & ", " & rst![Cons.] & ", " & rst![260/280] & ", " & _
                rst![260/230] & ")"
            dbs.Execute strSQL2, dbFailOnError
            rst.Edit
            rst!Volume = rst!Volume - lngMax
            rst.Update
        Loop
        rst.MoveNext
    Loop
    rst.Close
End Sub

Change the table name in the constant at the beginning.

Warning: SampleID should not be the primary key since the code will create duplicates!

---

Regards, Hans Vogelaar

Answer 2

Hi Hans,

This code would certainly work, but why not use rst.AddNew rather than executing an Insert statement? Perhaps you were concerned about where the recordpointer would be, but the help file says "The record that was current before you used AddNew remains current."

---

-Tom. Microsoft Access MVP

Answer 3

Yes, that was my concern, but you are entirely correct - the record pointer doesn't move. So the following is easier and faster:

Sub SplitRecords()
    Const lngMax = 4500
    Const strTable = "MyTable"
    Dim strSQL As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSampleID As String
    Dim lngCons As Long
    Dim lng260280 As Long
    Dim lng260230 As Long
    Set dbs = CurrentDb
    strSQL = "SELECT * FROM [" & strTable & "] WHERE Volume>" & lngMax
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    Do While Not rst.EOF
        strSampleID = rst!SampleID
        lngCons = rst![Cons.]
        lng260280 = rst![260/280]
        lng260230 = rst![260/230]
        Do While rst!Volume > lngMax
            rst.AddNew
            rst!SampleID = strSampleID
            rst!Volume = lngMax
            rst![Cons.] = lngCons
            rst![260/280] = lng260280
            rst![260/230] = lng260230
            rst.Update
            rst.Edit
            rst!Volume = rst!Volume - lngMax
            rst.Update
        Loop
        rst.MoveNext
    Loop
    rst.Close
End Sub

If fields such as Cons. or 260/280 are Single or Double, change the data type of the corresponding variable accordingly.

Thanks, Tom!

---

Regards, Hans Vogelaar

Answer 4

The code works very well and do it properly.

But I would like also that the sample with the most volume will be the first of the samples
and the rest of the samples followed by the decreasing volume. How can it be done?

Regards, Geir Arne

Answer 5

When you query the table, apply the proper sort order with an Order By clause. See the Sort row of the query designer.

---

-Tom. Microsoft Access MVP

Answer 6

It will not work because I want the same SampleID should be below one another. ie

K5050, 4500

K5050, 2000

K5051, 3500

etc

Answer 7

Try this:

SELECT *
FROM YourTable
ORDER BY
DMAX("Volume", "YourTable", "SampleID = " & [SampleID]) DESC,
Volume DESC;

---

Ken Sheridan, Stafford, England

Answer 8

The qury works as it should

Thanks for your help everyone :-)

Regard Geir Arne