• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

ACCESS sort field sets then overwrite with new sort order

Greetings,

I have a table with sets of fields such as SP1,Val1;SP2,Val2;Sp3,Val3...8sets.  I need to have the sets sorted by the Val# field then copied back into the table with the high values in SP1,Val1, then Sp2,Val2 etc.

I could create an array, but I don't know how to sort it then overwrite the old values with the sorted values, or perhaps create a separate table that I fill in with the original values then sort and update the old values sequentially.

Any nice tricks to get this done efficiently?

Thanks,
Pat

Here is a first shot at creating an array using the control names to create the array.  The value I want to actually sort on is the third column like xSp1PCT.

Private Sub SortVegData_Click()
Dim TreeArray(8, 4) As Variant, MyElement As Variant
'fill in the array
TreeArray(1, 1) = cbosp1
TreeArray(1, 2) = txtsp1strat
TreeArray(1, 3) = xSp1PCT
TreeArray(1, 4) = txtsp1indicator
TreeArray(2, 1) = cbosp2
TreeArray(2, 2) = txtsp2strat
TreeArray(2, 3) = xSp2PCT
TreeArray(2, 4) = txtsp2indicator
TreeArray(3, 1) = cbosp3
TreeArray(3, 2) = txtsp3strat
TreeArray(3, 3) = xSp3PCT
TreeArray(3, 4) = txtsp3indicator
TreeArray(4, 1) = cbosp4
TreeArray(4, 2) = txtsp4strat
TreeArray(4, 3) = xSp4PCT
TreeArray(4, 4) = txtsp4indicator
TreeArray(5, 1) = cbosp5
TreeArray(5, 2) = txtsp5strat
TreeArray(5, 3) = xSp5PCT
TreeArray(5, 4) = txtsp5indicator
TreeArray(6, 1) = cbosp6
TreeArray(6, 2) = txtsp6strat
TreeArray(6, 3) = xSp6PCT
TreeArray(6, 4) = txtsp6indicator
TreeArray(7, 1) = cbosp7
TreeArray(7, 2) = txtsp7strat
TreeArray(7, 3) = xSp7PCT
TreeArray(7, 4) = txtsp7indicator
TreeArray(8, 1) = cbosp8
TreeArray(8, 2) = txtsp8strat
TreeArray(8, 3) = xSp8PCT
TreeArray(8, 4) = txtsp8indicator




End Sub
0
MurphyPH
Asked:
MurphyPH
  • 4
  • 2
1 Solution
 
Dale FyeCommented:
Your explanation is a bit confusing.  Can you provide a sample dataset with Before and After?
0
 
hnasrCommented:
As I understand,
for one record
1, 2/2,5/3,10/5,7/
You want it:
3,10/5,7/2,5/1,2/

Upload a sample database with the table with few records of test data.
0
 
MurphyPHAuthor Commented:
Yes, exactly.  i will set up the data now.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MurphyPHAuthor Commented:
Here is a simplified data table and form.  I actually want to execute this with a button on a different form that will only sort the current record.  The code I had in mind is using the control names instead of the field names so it only affects the current record.

There is one record in the table with the data in the reverse order, with the lowest values in the first sets and the higher values in the end sets.  The code needs to reverse the current order.

Sorry, I forgot to hit the attach button for the file.  The file should be here now.
TestTable.mdb
0
 
MurphyPHAuthor Commented:
Well, a VBA manual and some trial and error helped me figure it out.  Here is the code that worked.

Private Sub SortVegData_Click()
Dim x As Integer, y As Integer
Dim strTemp1 As String, strTemp2 As String, strTemp3 As String, strTemp4 As String

Dim TreeArray(8, 4) As Variant, MyElement As Variant

'fill in the array
TreeArray(1, 1) = cbosp1
TreeArray(1, 2) = txtsp1strat
TreeArray(1, 3) = xSp1PCT
TreeArray(1, 4) = txtsp1indicator
TreeArray(2, 1) = cbosp2
TreeArray(2, 2) = txtsp2strat
TreeArray(2, 3) = xSp2PCT
TreeArray(2, 4) = txtsp2indicator
TreeArray(3, 1) = cbosp3
TreeArray(3, 2) = txtsp3strat
TreeArray(3, 3) = xSp3PCT
TreeArray(3, 4) = txtsp3indicator
TreeArray(4, 1) = cbosp4
TreeArray(4, 2) = txtsp4strat
TreeArray(4, 3) = xSp4PCT
TreeArray(4, 4) = txtsp4indicator
TreeArray(5, 1) = cbosp5
TreeArray(5, 2) = txtsp5strat
TreeArray(5, 3) = xSp5PCT
TreeArray(5, 4) = txtsp5indicator
TreeArray(6, 1) = cbosp6
TreeArray(6, 2) = txtsp6strat
TreeArray(6, 3) = xSp6PCT
TreeArray(6, 4) = txtsp6indicator
TreeArray(7, 1) = cbosp7
TreeArray(7, 2) = txtsp7strat
TreeArray(7, 3) = xSp7PCT
TreeArray(7, 4) = txtsp7indicator
TreeArray(8, 1) = cbosp8
TreeArray(8, 2) = txtsp8strat
TreeArray(8, 3) = xSp8PCT
TreeArray(8, 4) = txtsp8indicator

'Standard Bubblesort
For x = LBound(TreeArray) To (UBound(TreeArray) - 1)
    For y = (x + 1) To UBound(TreeArray)
'run the sort in reverse order based on the third item in the array
        If TreeArray(x, 3) < TreeArray(y, 3) Then
            strTemp1 = TreeArray(x, 1)
            strTemp2 = TreeArray(x, 2)
            strTemp3 = TreeArray(x, 3)
            strTemp4 = TreeArray(x, 4)
            TreeArray(x, 1) = TreeArray(y, 1)
            TreeArray(x, 2) = TreeArray(y, 2)
            TreeArray(x, 3) = TreeArray(y, 3)
            TreeArray(x, 4) = TreeArray(y, 4)
            TreeArray(y, 1) = strTemp1
            TreeArray(y, 2) = strTemp2
            TreeArray(y, 3) = strTemp3
            TreeArray(y, 4) = strTemp4
            strTemp1 = ""
            strTemp2 = ""
            strTemp3 = ""
            strTemp4 = ""
        End If
    Next y
Next x

'Now fill the controls back up with the newly sorted Array
cbosp1 = TreeArray(1, 1)
txtsp1strat = TreeArray(1, 2)
xSp1PCT = TreeArray(1, 3)
txtsp1indicator = TreeArray(1, 4)
cbosp2 = TreeArray(2, 1)
txtsp2strat = TreeArray(2, 2)
xSp2PCT = TreeArray(2, 3)
txtsp2indicator = TreeArray(2, 4)
cbosp3 = TreeArray(3, 1)
txtsp3strat = TreeArray(3, 2)
xSp3PCT = TreeArray(3, 3)
txtsp3indicator = TreeArray(3, 4)
cbosp4 = TreeArray(4, 1)
txtsp4strat = TreeArray(4, 2)
xSp4PCT = TreeArray(4, 3)
txtsp4indicator = TreeArray(4, 4)
cbosp5 = TreeArray(5, 1)
txtsp1strat = TreeArray(5, 2)
xSp5PCT = TreeArray(5, 3)
txtsp5indicator = TreeArray(5, 4)
cbosp6 = TreeArray(6, 1)
txtsp6strat = TreeArray(6, 2)
xSp6PCT = TreeArray(6, 3)
txtsp6indicator = TreeArray(6, 4)
cbosp7 = TreeArray(7, 1)
txtsp7strat = TreeArray(7, 2)
xSp7PCT = TreeArray(7, 3)
txtsp7indicator = TreeArray(7, 4)
cbosp8 = TreeArray(8, 1)
txtsp8strat = TreeArray(8, 2)
xSp8PCT = TreeArray(8, 3)
txtsp8indicator = TreeArray(8, 4)


End Sub
0
 
hnasrCommented:
Try this:

Open form Wetveg
Click button Sort This Record

Private Sub Command1366_Click()
    Dim rs As DAO.Recordset
    DoCmd.SetWarnings False
    Set rs = CurrentDb.OpenRecordset("Select * from wetveg Where ID2=" & ID2)
    DoCmd.RunSQL "Select 1 As R, ID2, Sp1 As Spx, Sp1Strat As SpxStrat, Sp1Indicator As SpxIndicator, Sp1PCT As SpxPCT, Sp1Dom As SpxDom,Sp1 As SpInt Into tble from WetVeg  where false"
    s = ""
    For i = 1 To 8
        Spx = rs("Sp" & i)
        SpxStrat = rs("Sp" & i & "strat")
        SpxIndicator = rs("Sp" & i & "indicator")
        SpxPCT = rs("Sp" & i & "pct")
        SpxDom = rs("Sp" & i & "dom")
        DoCmd.RunSQL "Insert Into tble Values ( '" _
         & i & "','" _
         & ID2 & "','" _
         & Spx & "','" _
         & SpxStrat & "','" _
         & SpxIndicator & "','" _
         & SpxPCT & "'," _
         & SpxDom & ",'" _
         & "Sp" & i & "')"
    Next
    DoCmd.SetWarnings True
    rs.Close
    DoCmd.OpenForm "Form1"
End Sub

Open in new window


Create table: tble
Form1 opens: record source Query1
SELECT *
FROM (SELECT TOP 1 * FROM (SELECT TOP 1 * FROM (SELECT * FROM tble ORDER BY SpxPCT DESC)  AS tin)  AS tout ORDER BY SpxPCT)  AS s1
, (SELECT TOP 1 * FROM (SELECT TOP 2 * FROM (SELECT * FROM tble ORDER BY SpxPCT DESC)  AS tin)  AS tout ORDER BY SpxPCT)  AS s2
, (SELECT TOP 1 * FROM (SELECT TOP 3 * FROM (SELECT * FROM tble ORDER BY SpxPCT DESC)  AS tin)  AS tout ORDER BY SpxPCT)  AS s3
, (SELECT TOP 1 * FROM (SELECT TOP 4 * FROM (SELECT * FROM tble ORDER BY SpxPCT DESC)  AS tin)  AS tout ORDER BY SpxPCT)  AS s4
, (SELECT TOP 1 * FROM (SELECT TOP 5 * FROM (SELECT * FROM tble ORDER BY SpxPCT DESC)  AS tin)  AS tout ORDER BY SpxPCT)  AS s5
, (SELECT TOP 1 * FROM (SELECT TOP 6 * FROM (SELECT * FROM tble ORDER BY SpxPCT DESC)  AS tin)  AS tout ORDER BY SpxPCT)  AS s6
, (SELECT TOP 1 * FROM (SELECT TOP 7 * FROM (SELECT * FROM tble ORDER BY SpxPCT DESC)  AS tin)  AS tout ORDER BY SpxPCT)  AS s7
, (SELECT TOP 1 * FROM (SELECT TOP 8 * FROM (SELECT * FROM tble ORDER BY SpxPCT DESC)  AS tin)  AS tout ORDER BY SpxPCT)  AS s8;

Open in new window


Extra code can be written to overwrite the record's content with sorted entries.
TestTable-Q-28009252.mdb
0
 
MurphyPHAuthor Commented:
Not elegant, but I figured it out myself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now