Finding Consecutive Fields with Identical values 1

[ppkgoldengun]

Hi,

I have a table say tblBoxSlot

With the fields:box, tower, and slot1, slot2, ......slot81

Empty slots have a value of -1111 Filled slots have a value of
an integer greater than zero.

How can I find say the first 6 consecutive slots in a box record that are empty (-1111)?

I am having trouble. I have seen code examples and sql that can find consecutive slots of vertical record values but not finding a record and then the first slot that meets the criteria.

Any ideas?

Thanks.

 

[Golom]

Because your table is un-normalized, any SQL solution to this is pretty much out of the question. You would probably need to pull the table into a recordset and go through it record-by record, testing each one for six consecutive values of -1111.

The other alternative is to normalize the table in the form
[tt]
myTable
Box SlotNumber SlotValue
[/tt]

You can then run SQL of the form

Select Box , MIN([Empty Slot Range]) As [Slot Range]

From

(

SELECT  Box,  (a1.SlotNumber  & " to " & a1.SlotNumber+5) As [Empty Slot Range]
FROM aa AS a1

Where SlotValue=-1111
AND EXISTS (Select 1 From aa Where aa.Box = a1.Box AND aa.slotnumber = a1.slotnumber + 1 AND aa.SlotValue = -1111)
AND EXISTS (Select 1 From aa Where aa.Box = a1.Box AND aa.slotnumber = a1.slotnumber + 2 and aa.SlotValue = -1111)
AND EXISTS (Select 1 From aa Where aa.Box = a1.Box AND aa.slotnumber = a1.slotnumber + 3 and aa.SlotValue = -1111)
AND EXISTS (Select 1 From aa Where aa.Box = a1.Box AND aa.slotnumber = a1.slotnumber + 4 and aa.SlotValue = -1111)
AND EXISTS (Select 1 From aa Where aa.Box = a1.Box AND aa.slotnumber = a1.slotnumber + 5 and aa.SlotValue = -1111)
)

Group By Box

Order by 1

You can add the Tower field as a simple extension of this example.

While not exactly elegant, it's at least possible.

 

[MichaelRed]

Just as an exercise, I concocted the following:


Sample Procedure

Public Function basEmptySet(rsIn As String, strField As String, _
                            NSeq As Integer, Crta As String, _
                            ParamArray KeyVals() As Variant) As String

    'rsIn is the Rrecordset to check
    'strField is the field name (sans the index part) to search for the pattern
    'NSeq is the number of Ssequential matches to find in the record
    'Crta is the pattern to search for
    'KeyVals is the PAIRED set of field names and values which identify a Record

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strCriteria As String
    Dim strStartFld As String
    Dim FldName As String
    Dim Quo As String * 1
    Quo = Chr(34)

    Dim Idx As Integer
    Dim Jdx As Integer

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(rsIn, dbOpenDynaset)

    'Check that the keys are at least supplied in pairs (name & value)
    If (UBound(KeyVals) Mod 2) = 0 Then
        'Sample for error checking.  Early exit if the param array is not balanced
        Exit Function
    End If

    'Set up the criteria string to find A record to search
    While Idx <= UBound(KeyVals)
        strCriteria = strCriteria & "[" & KeyVals(Idx) & "] = " _
                    & Quo & KeyVals(Idx + 1) & Quo & " And "
        Idx = Idx + 2
    Wend
    strCriteria = Left(strCriteria, Len(strCriteria) - 5)
    rst.FindFirst strCriteria
    If (rst.NoMatch = True) Then
        'Record not found, exit - NOT gracefully
        Exit Function
    End If

    'Here ONLY if there is a record which matches _
     Only need to search the fields which match the field name pattern for _
     "sequential" fields which match the pattern
     Idx = 0
     While Idx < rst.Fields.Count
        FldName = rst.Fields(Idx).name
        If (InStr(FldName, strField) = 0) Then
            'No Match for the field name, so reset the match count
            Jdx = 0
            strStartFld = ""
         Else
            'At least the field name matches
            If (rst.Fields(Idx) = Crta) Then
                Jdx = Jdx + 1
                If (Jdx = 1) Then
                    'Assume the best, this one will be the first field in the sequence
                    strStartFld = rst.Fields(Idx).name
                End If
                If (Jdx = NSeq) Then
                    'We Have a WINNER - Go Tell it to the mountain!
                    basEmptySet = strStartFld
                    Exit Function
                End If
            End If
        End If
        Idx = Idx + 1
    Wend

End Function

Sample Query
SELECT tblBoxSlot.Box, tblBoxSlot.Tower, basEmptySet("tblBoxSlot","Slot",4,"-1111","Box",[Box],"Tower",[Tower]) AS FirstEmpty
FROM tblBoxSlot
WITH OWNERACCESS OPTION;
[/code]


Sample Result

Box	Tower	FirstEmpty
Abc	Def	
Abc	FGH	Slot4
[Code]

Golem is corect re the de-normalized data structure, however we are not always at liberty to corect the previous decisions.  The procesure is ADMITTEDLY short on the niceties (error checking ... ), and is supplied only as a starting point / sample of an approach which avoids the restructuring of the existing recordset.

On the positive side, it can be used to select and number of "sequential" fields with any like value, subject to the limitation in the code (that the field names are the same except for the "index" value ... ).  A LARGE cutionary  note, would be that the "sequence" of the fields in a recordset (particularly TABLE TYPE recordsets) is often NOT the apparent order.  "Students" of the procedure should exercise EXTREME caution in this area and read the literature regarding the order of fields.  In general, fields in a recordsset are ordered by the order that they were added to the recordset, which may NOT be their ordinal position in the recordset deffinition as seen in the database definition.  e.g. if a field was added to the table and the fields were subsquently 'arranged' to suit an individuals' desired 'view', the actual order processed in the procedure would not change, thus it is possible that "Slot71" is processed between "Slot14" and "Slot15" ... generating a very messy "OOPS" incident.

Please PLEASE [b]PLEASE[/b] be careful of this!!!





MichaelRed

 

[ppkgoldengun]

Thanks to both of you. However, as MichaelRed points out, I inherited the table structure. I will take your comments on the table seriously. I may ask a question or to while I work through the code for understanding. Also, in the past I have used ordinal postitions in the table structure to run through a tableset. WOuld there still be the same problems? Assuming from your answer, using a Query as the basis for the record set would assure the correct sequence number?

Again, thanks.

 

[ppkgoldengun]

MichaelRed,

One more thing before my technical questions start (it may be a day or two before you get them as I am traveling on business tomorrow). May I have your permission (I will credit via embedded comment) to use the code. The code is being used to build out a gene/tissue sample tracking system at the University of California San Francisco Cancer Research Center. I am doing this gratis in the memory of my father who died last year from bladder cancer. I got involved as my daughter is a molecular biologist clincal researcher in the Pre-Clinical Core lab where they do this work. Written permission would be great.

 

[MichaelRed]

While I appreciat the 'honorable mention', code posted publically is, of course, avaialble for your use.

Using a query as the recordsource would resolve the ordinal position vs Field name question -at least as a far as I know.

Unfortunatly, this might require executing the query for each attempt to find such sequences.

There is much about the overall system which is not available, so many concerns / considerations are not resolveable. If, for instance, the database is a multiuser system, it is not pratical (at least in MS Access) to assure that a single record is reserved (locked) for an individuals' use. Thus, two individuals might be searching the same record(s) in overlapping time intervals, and find the same sequence. Attempting to fill all or some part of the sequence by both could easily result in corrupted data.

Other considerations can include the size of the recordset which the search is based on. Ms. Access ALlways TRANSFERS the entire Rescordset source to the requesting entity (local desktop) and all processing is accomplished there. Genome projects tems to have large datasets, so even transfering the recordset source(s) can easily bog down a network.

I'm sure that many other pitfalls await the implementation of the procedure posted, and can only adview caution and extensive testing before committing to it.




MichaelRed

 

[ppkgoldengun]

Michael or Anyone,

I worked with Michael's code and query example except my Criteria, Tower, and Box number are Integers. I tried numerous adjustments and can't quite it it to work. Being the amateur I was hoping that you could provide an adjustment to the code and query example which accounts for the differnt data types. The following is my latest attempt:

Public Function basEmptySet1(rsIn As String, strField As String, _
NSeq As Integer, Crta As Integer, _
ParamArray KeyVals() As Variant) As String


'rsIn is the Rrecordset to check
'strField is the field name (sans the index part) to search for the pattern
'NSeq is the number of Ssequential matches to find in the record
'Crta is the pattern to search for
'KeyVals is the PAIRED set of field names and values which identify a Record

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strCriteria As String
Dim strStartFld As String
Dim FldName As String
Dim Quo As String * 1
Quo = Chr(34)

Dim Idx As Integer
Dim Jdx As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(rsIn, dbOpenDynaset)

'Check that the keys are at least supplied in pairs (name & value)
If (UBound(KeyVals) Mod 2) = 0 Then
'Sample for error checking. Early exit if the param array is not balanced
Exit Function
End If

'Set up the criteria string to find A record to search
While Idx <= UBound(KeyVals)
strCriteria = strCriteria & "[" & KeyVals(Idx) & "]" = KeyVals(Idx + 1) & " And " & Idx = Idx + 2
Wend
'strCriteria = Left(strCriteria, Len(strCriteria) - 5)
rst.FindFirst strCriteria
If (rst.NoMatch = True) Then
'Record not found, exit - NOT gracefully
Exit Function
End If

'Here ONLY if there is a record which matches _
Only need to search the fields which match the field name pattern for _
"sequential" fields which match the pattern
Idx = 0
While Idx < rst.Fields.Count
FldName = rst.Fields(Idx).Name
If (InStr(FldName, strField) = 0) Then
'No Match for the field name, so reset the match count
Jdx = 0
strStartFld = ""
Else
'At least the field name matches
If (rst.Fields(Idx) = Crta) Then
Jdx = Jdx + 1
If (Jdx = 1) Then
'Assume the best, this one will be the first field in the sequence
strStartFld = rst.Fields(Idx).Name
End If
If (Jdx = NSeq) Then
'We Have a WINNER - Go Tell it to the mountain!
basEmptySet1 = strStartFld
Exit Function
End If
End If
End If
Idx = Idx + 1
Wend

End Function


My query:

SELECT tblBoxSlot.BoxNum AS Expr1, tblBoxSlot.Tower, basEmptySet1("tblBoxSlot","Slot",4,-1111,"BoxNum",[BoxNum],"Tower",[Tower]) AS FirstEmpty
FROM tblBoxSlot
WITH OWNERACCESS OPTION;

 

[MichaelRed]

please provide some sample data in the specific format. It does not need to represent ANY actual information in your data base, and certainly does not need to have anywhere near the total number of fields as the actual structure, and could be only a few records, but all data should be desiginated as a specific TYPE (perhaps seperatly, as in the tools->documentor format.




MichaelRed

 

[Golom]

There's a problem where you are setting up the criteria string.

[COLOR=green]'Set up the criteria string to find A record to search[/color]
Idx = 0
Do While Idx <= UBound(KeyVals)
    strCriteria = strCriteria & _
    "[" & KeyVals(Idx) & "] = " & KeyVals(Idx + 1) & " And "
    Idx = Idx + 2
Loop
strCriteria = Left(Trim$(strCriteria), Len(Trim$(strCriteria)) - 4)

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[ppkgoldengun]

I tried Golom code however it retrieved the first empty shot in a record not the first empty slot that is the beginning of a minimum of 4 empty slots.

My data is as follows (sample):

BoxRecNum BoxNum BoxDesc Tower Slot1 Slot 2 Slot3 Slot4 Slot5 Slot6 Slot7 Slot9.........

1 1 TG11M 1 -1111 -1111 23 -1111
-1111 -1111 -1111 56
2 2 FG32M 1 -1111 -1111 -1111 56
-1111 56 -1111 32

BoxRecNum Autonumber
BoxNum Number (Integer)
BoxDesc Text (String)
Tower Number (Integer)
Slot1 Number (Integer)

What I am trying to do is find the first empty sequence that would have in this case 4 consecutive empty slots. In this sample it would be tower 1 box 1 slot 4. Even though there may be other records in the tblboxslot that have the appropriate space or even in the same record lets say Tower 1 Box 1 could have 9 empty slots (-1111) starting at position Slot 21. The intent is to find the first so that the boxes can be packed densely but the new samples must be consecutive......

Record 2 in this sample would not have any (at least in the data shown). It could be that you might have to find tower 3 Box 9 Slot 42 before you could find four consecutive.....

Hope this helps..... Again thanks to both of you.

 

[Golom]

I hadn't looked at that last loop before but I think you need

[COLOR=green]'At least the field name matches[/color]
If (rst.Fields(Idx) = Crta) Then
    Jdx = Jdx + 1
    If (Jdx = 1) Then
        [COLOR=green]'Assume the best, this one will be the first field in the sequence[/color]
        strStartFld = rst.Fields(Idx).Name
    End If
    If (Jdx = NSeq) Then
        [COLOR=green]'We Have a WINNER - Go Tell it to the mountain![/color]
        basEmptySet1 = strStartFld
        Exit Function
    End If[COLOR=red]
Else
    Jdx = 0[/color]
End If

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[ppkgoldengun]

Thanks Golom that was it. By combining an order by clause and a Top 1 parameter to the select it returns the first.....

A mention to you as well in the code for the research center.