Select records in threes, sequentially, that end in 5, 7, 9

**AccessPower** · 12-05-2016, 02:24 PM

So I am in the processing of fixing a database. The logic in the queries is no longer working, so I've had to adjust the criteria. The query is supposed to pull up three numbers: One ending in 5, one ending in 7, and one ending in 9. The numbers have to be sequential, so the query can only pull up the one ending in a 5 if there is an available 7 and 9. Example:

N0005
N0007
N0009

or

N1235
N1237
N1239

I am trying to figure out how to do this in a query. Is there a way to retrieve sets of three like this?

Thanks for any help in advance.

**AccessPower** · 12-05-2016, 03:37 PM

This is what I have so far:

Code:

SELECT GNumbers.Numbers, Right(Numbers,1) AS LastDigit, Val(Mid(Numbers,2)) AS JustNumbers, (SELECT Top 1 GNumbers.Numbers FROM GNumbers Numbers WHERE GNumbers.ID<GNumbers.ID ORDER BY GNumbers.ID DESC) AS Previous
FROM GNumbers LEFT JOIN switches ON GNumbers.Numbers = switches.numbers
WHERE (((Right([Numbers],1))="5" Or (Right([Numbers],1))="7" Or (Right([Numbers],1))="9"));

The only way that I can think of proceeding from here is to somehow access the previous record. I'm still working on it. Anyone else is welcome to chime in with advice. Records are ordered by primary key, autonumber.

**aytee111** · 12-05-2016, 04:30 PM

Long way around:
- query 1 - left(4) for all
- query 2 - left(4) + if ends in 5
- query 3 - left(4) + if ends in 7
- query 4 - left(4) + if ends in 9
- query 5 - join query 1 to each one of the others, only exact matches will be displayed

**aytee111** · 12-05-2016, 05:51 PM

Altho that's not really sequential, I guess I don't know what you mean by sequential. Added to the database? Nothing in between?

**AccessPower** · 12-06-2016, 07:44 AM

Removed to update latest reply.

**aytee111** · 12-06-2016, 08:13 AM

This doesn't say nothing in between - if JustNumbers + 2 exists then yes, but if JustNumbers + 1 exists then it should be a no? If I understand what you are saying then if there is an N0006 in between then you don't want the whole set to be included?

**AccessPower** · 12-06-2016, 09:32 AM

Removed to Update Latest Reply

**AccessPower** · 12-06-2016, 09:56 AM

Sorry about that. I scratched the idea because the logic doesn't work. I'm trying to get the result via a recordset. I have three different ideas. I'm going to edit my post to show all three. None are fully functional. One is close.

Here they are

Idea 1:

This one works for two, but produces an error on the third even though there is a match for it. "No Current Record" is the error. I get this before the second messagebox (the one with all three numbers). The error occurs on:
MiddleNumberstoMatch = RS5!Numbers_to_match.

Code:

Private Sub Command0_Click()
   Dim Number5 As String
   Dim Number7 As String


   Dim MiddleNumberstoMatch As Integer
   Dim MatchCheck As Integer
   Dim NumberofMatches As Integer


   Dim SQL5 As String
   Dim SQL7 As String


   Dim RS5 As Recordset
   Dim RS7 As Recordset




   SQL5 = "SELECT GNumbers.Numbers, Val(Mid(Numbers,2)) AS JustNumbers, Val(LEFT(JustNumbers, len(JustNumbers)-1)) AS Numbers_to_Match FROM GNumbers WHERE Right([Numbers],1)='5';"
   SQL7 = "SELECT GNumbers.Numbers, Val(Mid(Numbers,2)) AS JustNumbers, Val(LEFT(JustNumbers, len(JustNumbers)-1)) AS Numbers_to_Match FROM GNumbers WHERE Right([Numbers],1)='7';"
   SQL9 = "SELECT GNumbers.Numbers, Val(Mid(Numbers,2)) AS JustNumbers, Val(LEFT(JustNumbers, len(JustNumbers)-1)) AS Numbers_to_Match FROM GNumbers WHERE Right([Numbers],1)='9';"




   Set RS5 = CurrentDb.OpenRecordset(SQL5)
   Set RS7 = CurrentDb.OpenRecordset(SQL7)
   Set RS9 = CurrentDb.OpenRecordset(SQL9)
   
   If Not RS5.EOF Then
    RS5.MoveFirst


ResetLabel:
    MiddleNumberstoMatch = RS5!Numbers_to_match
    NumberofMatches = 1
    Number5 = RS5!Numbers




        If Not RS7.EOF Then
            RS7.MoveFirst
            MatchCheck = RS7!Numbers_to_match
            If MiddleNumberstoMatch = MatchCheck Then
                NumberofMatches = 2
                MatchCheck = 0
                Number7 = RS7!Numbers
                MsgBox "Your Numbers are: " + Number5 + " and " + Number7
                If Not RS9.EOF Then
                    RS9.MoveFirst
                    MatchCheck = RS9!Numbers_to_match
                    If MiddleNumberstoMatch = MatchCheck Then
                        NumberofMatches = 3
                        MatchCheck = 0
                        Number9 = RS9!Numbers
                        MsgBox "Your Numbers are: " + Number5 + " and " + Number7 + " and " + Number9
                    Else
                        NumberofMatches = 0
                        RS5.MoveNext
                        GoTo ResetLabel
                    End If
                Else
                        RS5.MoveNext
                        GoTo ResetLabel
                End If
            Else
                NumberofMatches = 0
                RS5.MoveNext
                GoTo ResetLabel
            End If
        Else
            NumberofMatches = 0
            RS5.MoveNext
            GoTo ResetLabel
        End If
    Else
    MsgBox "There are no matching records in the recordset that meet the criteria."
    End If
End Sub

Idea 2:

This does nothing. Not sure why. I was going to get it to return the number of matches, then iterate through them and grab the numbers. The starting code doesn't even work.

Code:

Private Sub Command0_Click()
   Dim SQL5 As String
   Dim RS5 As Recordset
   Dim SQLMatch As String
   Dim RSMatch As Recordset


   SQL5 = "SELECT GNumbers.Numbers, Val(Mid(Numbers,2)) AS JustNumbers, Val(LEFT(JustNumbers, len(JustNumbers)-1)) AS Numbers_to_Match FROM GNumbers WHERE Right([Numbers],1)='5';"
   Set RS5 = CurrentDb.OpenRecordset(SQL5)
   
If Not RS5.EOF Then
    RS5.MoveFirst
NextRecord:
    RS5.MoveNext
    MiddleNumberstoMatch = RS5!Numbers_to_match
    SQLMatch = "SELECT GNumbers.Numbers, Val(Mid(Numbers,2)) AS JustNumbers, Val(LEFT(JustNumbers, len(JustNumbers)-1)) AS Numbers_to_Match FROM GNumbers WHERE ((Right([Numbers],1)='5') OR (Right([Numbers],1)='7') OR (Right([Numbers],1)='9') AND Numbers_to_Match=" + MiddleNumberstoMatch + ");"
    Set RSMatch = CurrentDb.OpenRecordset(SQLMatch)
    RSMatch.MoveLast
    RecordCount = RSMatch.RecordCount
        If RecordCount = 3 Then
        MsgBox "Found 3 Matches!"
        Else
        MsgBox "Nope"
        GoTo NextRecord
        End If
Else
MsgBox "There are no numbers available that match the criteria."
End If
End Sub

**JoeM** · 12-06-2016, 10:18 AM

The following query will return all 4 character prefixes that have entries that end in 5, 7, and 9:

Code:

SELECT Left([Numbers],4) AS Prefix
FROM GNumbers
GROUP BY Left([Numbers],4)
HAVING (((Sum(IIf(Right([Numbers],1)=5,1,0)))>0) AND ((Sum(IIf(Right([Numbers],1)=7,1,0)))>0) AND ((Sum(IIf(Right([Numbers],1)=9,1,0)))>0));

**JoeM** · 12-06-2016, 10:22 AM

To return all the 5,7, and 9 numbers meeting that criteria, just use the code above in the WHERE clause of a Select query, i.e.

Code:

SELECT Numbers
FROM GNumbers
WHERE Left([Numbers],4) in
(SELECT Left([Numbers],4) AS Prefix
FROM GNumbers
GROUP BY Left([Numbers],4)
HAVING (((Sum(IIf(Right([Numbers],1)=5,1,0)))>0) AND ((Sum(IIf(Right([Numbers],1)=7,1,0)))>0) AND ((Sum(IIf(Right([Numbers],1)=9,1,0)))>0)))
AND Right([Numbers],1) in (5,7,9);

**aytee111** · 12-06-2016, 10:43 AM

I don't think you have "Option Explicit" at the top of your VBA modules, that might help get rid of some of your issues.

If you use the method in post #3 and add one more query for "4" and "6" and "8" to make sure those don't exist, would this help to keep it in queries, or am I still misunderstanding what you are trying to accomplish?

**AccessPower** · 12-06-2016, 10:54 AM

Thanks for the input. Currently I have the following that is working:

Code:

Private Sub Command0_Click()
   Dim Number5 As String
   Dim Number7 As String


   Dim MiddleNumberstoMatch As Integer
   Dim MatchCheck As Integer
   Dim i As Integer


   Dim SQL5 As String
   Dim SQL7 As String


   Dim RS5 As Recordset
   Dim RS7 As Recordset




   SQL5 = "SELECT GNumbers.Numbers, Val(Mid(Numbers,2)) AS JustNumbers, Val(LEFT(JustNumbers, len(JustNumbers)-1)) AS Numbers_to_Match FROM GNumbers WHERE Right([Numbers],1)='5';"
   SQL7 = "SELECT GNumbers.Numbers, Val(Mid(Numbers,2)) AS JustNumbers, Val(LEFT(JustNumbers, len(JustNumbers)-1)) AS Numbers_to_Match FROM GNumbers WHERE Right([Numbers],1)='7';"
   SQL9 = "SELECT GNumbers.Numbers, Val(Mid(Numbers,2)) AS JustNumbers, Val(LEFT(JustNumbers, len(JustNumbers)-1)) AS Numbers_to_Match FROM GNumbers WHERE Right([Numbers],1)='9';"




   Set RS5 = CurrentDb.OpenRecordset(SQL5)
   Set RS7 = CurrentDb.OpenRecordset(SQL7)
   Set RS9 = CurrentDb.OpenRecordset(SQL9)


   If Not RS5.EOF Then
   RS5.MoveFirst
ResetLabel:
    MiddleNumberstoMatch = RS5!Numbers_to_match
    NumberofMatches = 1
    Number5 = RS5!Numbers
        If Not RS7.EOF Then
            RS7.MoveFirst
ResetLabel7:
            MatchCheck = RS7!Numbers_to_match
            If MatchCheck = MiddleNumberstoMatch Then
                NumberofMatches = 2
                Number7 = RS7!Numbers
                MsgBox "Your Numbers are: " + Number5 + " and " + Number7
                If Not RS9.EOF Then
                    RS9.MoveFirst
ResetLabel9:
                    MatchCheck = RS9!Numbers_to_match
                    MsgBox MatchCheck
                    If MatchCheck = MiddleNumberstoMatch Then
                        Number9 = RS9!Numbers
                        MsgBox "Your Numbers are: " + Number5 + " and " + Number9
                    Else
                        RS9.MoveNext
                        GoTo ResetLabel9
                    End If
                Else
                    RS5.MoveNext
                    GoTo ResetLabel
                End If
            Else
                RS7.MoveNext
                GoTo ResetLabel7
            End If
        Else
            RS5.MoveNext
            GoTo ResetLabel
        End If
    Else
    MsgBox "There are no matching records in the recordset that meet the criteria."
    End If
End Sub

I realized that I wasn't looping through each recordset. I was just looping through rs5 when the first record in the others failed. Overlooking the obvious. I would still like to pursue the query option since Joe is very close. Thank you again.

**AccessPower** · 12-06-2016, 11:09 AM

Originally Posted by JoeM

To return all the 5,7, and 9 numbers meeting that criteria, just use the code above in the WHERE clause of a Select query, i.e.

Code:

SELECT Numbers
FROM GNumbers
WHERE Left([Numbers],4) in
(SELECT Left([Numbers],4) AS Prefix
FROM GNumbers
GROUP BY Left([Numbers],4)
HAVING (((Sum(IIf(Right([Numbers],1)=5,1,0)))>0) AND ((Sum(IIf(Right([Numbers],1)=7,1,0)))>0) AND ((Sum(IIf(Right([Numbers],1)=9,1,0)))>0)))
AND Right([Numbers],1) in (5,7,9);

Thi actually works really well, except it pulls up N13337 along with N1337. Any way to fix this?

**JoeM** · 12-06-2016, 11:23 AM

Sorry. I didn't realize that these numbers could be different lengths.

Use this version then:

Code:

SELECT GNumbers.Numbers
FROM GNumbers
WHERE (((Left([Numbers],Len([Numbers])-1)) In (SELECT Left([Numbers],Len([Numbers])-1) AS Prefix
FROM GNumbers
GROUP BY Left([Numbers],Len([Numbers])-1)
HAVING (((Sum(IIf(Right([Numbers],1)=5,1,0)))>0) AND ((Sum(IIf(Right([Numbers],1)=7,1,0)))>0) AND ((Sum(IIf(Right([Numbers],1)=9,1,0)))>0)))) AND ((Right([Numbers],1)) In (5,7,9)));

**AccessPower** · 12-06-2016, 12:05 PM

Originally Posted by JoeM

Sorry. I didn't realize that these numbers could be different lengths.

Use this version then:

Code:

SELECT GNumbers.Numbers
FROM GNumbers
WHERE (((Left([Numbers],Len([Numbers])-1)) In (SELECT Left([Numbers],Len([Numbers])-1) AS Prefix
FROM GNumbers
GROUP BY Left([Numbers],Len([Numbers])-1)
HAVING (((Sum(IIf(Right([Numbers],1)=5,1,0)))>0) AND ((Sum(IIf(Right([Numbers],1)=7,1,0)))>0) AND ((Sum(IIf(Right([Numbers],1)=9,1,0)))>0)))) AND ((Right([Numbers],1)) In (5,7,9)));

This is working. Thank you so much. I was actually able to do it both ways, but this method saves quite a bit of time.

Select records in threes, sequentially, that end in 5, 7, 9

Thread Tools

Select records in threes, sequentially, that end in 5, 7, 9

Similar Threads

Need to print thousands of sequentially barcoded report pages

Sequentially Numbered Column

Adding sequentially

Making Access display records sequentially

Sequentially number records in a table field

Posting Permissions