We help IT Professionals succeed at work.

remove lowest 2 and highest 2 numbers

running32
running32 asked
on
178 Views
Last Modified: 2012-05-08
Below is the code I am using in access.  I need to convert this over to sql.    I have the queries(views) but I'm not sure how to loop in sql and get this work.

qselRNP1_01max is sorted desc by price
qselRNP-01 min

Thank you for your help.


Sub SetPrice()
'This function determines NDC drugs that have more than 5 prices
'Then deletes Max 2 and Min 2
 
Dim db      As Database
Dim rLn     As Recordset
Dim rMax    As Recordset
Dim rMin    As Recordset
 
 
Set db = CurrentDb
 
Set rLn = db.OpenRecordset("qsumRNP1_01")
Set rMax = db.OpenRecordset("qselRNP1_01Max")
Set rMin = db.OpenRecordset("qselRNP1_01Min")
 
DoCmd.SetWarnings False
 
Do Until rLn.EOF
    Debug.Print rLn.AbsolutePosition
    rMin.FindFirst "LN='" & rLn!LN & "'"
    rMax.FindFirst "LN='" & rLn!LN & "'"
    'Delete two lowest prices
     If rMin.NoMatch = False Then
        For x = 1 To 2
            DoCmd.RunSQL "DELETE FROM RNP1_01 WHERE NDC='" & rMin!NDC & "'"
            rMin.MoveNext
        Next x
    End If
    'Delete two highest prices
    If rMax.NoMatch = False Then
        For x = 1 To 2
            DoCmd.RunSQL "DELETE FROM RNP1_01 WHERE NDC='" & rMax!NDC & "'"
            rMax.MoveNext
        Next x
    End If
    rMin.MoveFirst
    rMax.MoveFirst
    
    rLn.MoveNext
Loop
 
DoCmd.SetWarnings True
 
End Sub

Open in new window

Comment
Watch Question

Commented:
It's not the most efficient, but you could convert them to NOT IN clauses and add them to the WHERE statement of your main query.


WHERE NDC NOT IN (YOUR_MAX_QUERY) AND NDC NOT IN (YOUR_MIN_QUERY)

Author

Commented:
What would be the most efficent way to do it please.

  I cannot use the not in because they will always be in both files.   Below is the query to get the max drug price as you can see I am selecting everything.  

SELECT RNDC11.LN, RNDC11.GNI, RNP1_01.NPT_PRICEX, RNDC11.NDC
FROM RNDC11 INNER JOIN RNP1_01 ON RNDC11.NDC = RNP1_01.NDC
WHERE (((RNDC11.GNI)="1" Or (RNDC11.GNI)="2") AND ((RNDC11.CL)="F"))
ORDER BY RNDC11.LN, RNDC11.GNI, RNP1_01.NPT_PRICEX DESC;

Commented:
What are your Min and Max queries? ("qselRNP1_01Max" and "qselRNP1_01Min")

Author

Commented:
Below, thanks



qselRNP_01Max

SELECT RNDC11.LN, RNDC11.GNI, RNP1_01.NPT_PRICEX, RNDC11.NDC
FROM RNDC11 INNER JOIN RNP1_01 ON RNDC11.NDC = RNP1_01.NDC
WHERE (((RNDC11.GNI)="1" Or (RNDC11.GNI)="2") AND ((RNDC11.CL)="F"))
ORDER BY RNDC11.LN, RNDC11.GNI, RNP1_01.NPT_PRICEX DESC;


qselRNP_01Min

SELECT RNDC11.LN, RNDC11.GNI, RNP1_01.NPT_PRICEX, RNDC11.NDC
FROM RNDC11 INNER JOIN RNP1_01 ON RNDC11.NDC = RNP1_01.NDC
WHERE (((RNDC11.GNI)="1" Or (RNDC11.GNI)="2") AND ((RNDC11.CL)="F"))
ORDER BY RNDC11.LN, RNDC11.GNI, RNP1_01.NPT_PRICEX;

Commented:
You mentioned "I need to convert this over to SQL".  Does that mean you are upgrading to something more powerful like MS SQL Server, or will this still be for use in MS Access?

Author

Commented:
It is running in on ms Sql.  Thanks
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.