Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ACCESS sort field sets then overwrite with new sort order

Posted on 2013-01-25
7
330 Views
Last Modified: 2013-01-30
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
Comment
Question by:MurphyPH
  • 4
  • 2
7 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38820334
Your explanation is a bit confusing.  Can you provide a sample dataset with Before and After?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38820355
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
 

Author Comment

by:MurphyPH
ID: 38820362
Yes, exactly.  i will set up the data now.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:MurphyPH
ID: 38820394
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
 

Accepted Solution

by:
MurphyPH earned 0 total points
ID: 38820894
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
 
LVL 30

Expert Comment

by:hnasr
ID: 38821282
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
 

Author Closing Comment

by:MurphyPH
ID: 38834574
Not elegant, but I figured it out myself.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question