Solved

ACCESS sort field sets then overwrite with new sort order

Posted on 2013-01-25
7
329 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

786 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