Check if data is included at least once in a row 1

[BMKanun]

Hi,
can someone suggest how to check if data from one row is included at least once in another row. e.g
Control row: 1,3,5,7,9
Row 1: 1,3,5,3,7,9 OK! (doubling 3 is acceptable)
Row 2: 1,3,5,3,7 NOT OK! (9 is missing!)
Thanks,
Bole

 

[MajP]

Is this an Excel question or an Access question? This structure does not look like something you would see in a database so I assume Excel or some other application. If this is a database can you explain the table structure? Also explain how you would want the user to mark or identify the control record ("row"). Where is this check done: when the user enters data in a form or just running a check on a query? Can you explain in general what the database does and the purpose of the check.

 

[BMKanun]

Hi & thanks for your interest.
It is an Access database table with the following structure:
IDATE:TIME:INSPECTORCHECKEDRODUCT: etc..
the numbers 1,3,5,7,9, represent the InspectorID checking particular product and is saved in the INSPECTORCHECKED field.
Every inspection is recorded in one record of the table.
The idea is to query if every product is checked by all the needed inspectors e.g.
productID = 1
INSPECTORCHECKED = 1,3,5,7,9
reports Full check performed

productID = 2
INSPECTORCHECKED = 1,3,5,7
reports Full check is not performed

I hope you got the picture,
Thanks,
B

 

[MajP]

To verify. You have a field "inspectorchecked" that has values seperated by commas.
1) An important question is if this is an Access 2007/20010 multivalued field or is this just a regular field? This is a big question because if it is a multivalued field things are treated completely different.
2)If this is not a multivalued field this is a poor data structure and the field should be changed to a related table where each inspector is a record. Is this something that you can modify?
3)This can be done in code, but it is real inefficient and need the answers on 1 and 2 first.
4)What version of Access?

 

[MajP]

Again this is not the preferrable method, but it can be coded.

Public Function isFullCheck(strInspectors As Variant, strRequired As Variant) As Boolean
  Dim aRequired() As String
  Dim req As Variant
  isFullCheck = True
  If Not IsNull(strInspectors) And Not IsNull(strRequired) Then
    aRequired = Split(strRequired, ",")
    For Each req In aRequired
      If Nz(InStr(strInspectors, req), 0) = 0 Then
        isFullCheck = False
        Exit Function
      End If
    Next req
  End If
End Function

So from a query something like

Select field1, field2,INSPECTORCHECKED.., isFullCheck([INSPECTORCHECKED],"1,3,5,7,9") as FullChecked from someTable

 

[PHV]

Nz(InStr(strInspectors, req), 0)
I'am not aware that InStr could return a Null value and thus I wonder why to use the Nz function.

 

[MajP]

AFAIK, there are two cases where it can return a null

string1 is zero-length 0
string1 is Null Null
string2 is zero-length start
string2 is Null Null
string2 is not found 0
string2 is found within string1 Position at which match is found
start > string2 0

However that is out of habit. I have already ensured that neither string 1 or 2 can be null so it is redundant.

 

[MajP]

However this line is out of place
isFullCheck = True
Needs to be moved into the check

Public Function isFullCheck(strInspectors As Variant, strRequired As Variant) As Boolean
  Dim aRequired() As String
  Dim req As Variant
  If Not IsNull(strInspectors) And Not IsNull(strRequired) Then
    isFullCheck = True
    aRequired = Split(strRequired, ",")
    For Each req In aRequired
      If Nz(InStr(strInspectors, req), 0) = 0 Then
        isFullCheck = False
        Exit Function
      End If
    Next req
  End If
End Function

 

[MajP]

AFAIK

?instr(Null,"1,3,5,7,9")
Null
?instr("1,3,5",null)
Null

 

[PHV]

Sorry MajP, you're right.

 

[BMKanun]

Thanks once again,
while working the things become more clear what is needed to be done for me too.
It seems that there should be a another table where it will be defined which inspectors will check which products (according to speciality). Not all the products will be checked by all the inspectors.
So it would be row generated with query checking this conditions first, and later compare if the result from the checking row is contained in the inspection condition row (or group of data) Sorry for changing the things all the time.
Something like this:
Query Needed inspection results with:
ProductID Inspector
1 1
1 3
1 5
1 7
1 9
2 1
2 3
2 5
2 7
2 9
3 1
3 2

Table Performed Inspection
ProductID INSPECTORCHECKED DAte time etc.,,,
1 1
1 3
1 5
1 7
1 9
2 1
2 3
2 5
2 7
3 1
3 2

The result should be:
ProductID =1 Full check performed
ProductID =2 Full check NOT performed
ProductID =3 Full check performed

The inspection status leter fill be writen as true/false condition in a table.
It would be Access 2007.

THANKS A LOT
B

 

[MajP]

Yes that is the preferred solution. A table that holds the required inspections for that product and a table that holds the actual inspections



1)Determine which ones are not full checked
qryFullCheckNotPerfored

SELECT DISTINCT 
 tblRequiredInspection.productID, 
 "Full Check Not Performed" AS Status
FROM 
 tblRequiredInspection 
LEFT JOIN 
 tblPerformedInspections ON (tblRequiredInspection.inspector = tblPerformedInspections.INSPECTORCHECKED) AND (tblRequiredInspection.productID = tblPerformedInspections.productID)
WHERE 
 tblPerformedInspections.INSPECTORCHECKED Is Null

2)Return all productIDs except for the ones that are not full checked.
qryFullCheckPerformed

SELECT DISTINCT 
 tblRequiredInspection.productID, 
 "Full Check Performed" AS Status
FROM 
 tblRequiredInspection
WHERE 
 tblRequiredInspection.productID Not In (select ProductID from qryFullCheckNotPerformed)

3)Tie it all together
qryStatus

SELECT 
 productID, 
 Status
FROM 
 qryFullCheckPerformed
UNION SELECT 
 productID, 
 Status 
from 
 qryFullCheckNotPerformed;

 

[BMKanun]

Thanks a lot!
I'll apply it in the database.
thanks once again, and have a nice day!