Solved

ACCESS sort field sets then overwrite with new sort order

Posted on 2013-01-25
7
327 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now