Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2009
    Posts
    67

    Unanswered: Query to determine if a list is in the table

    I'm working on an application and bottom line is if the list I'm providing the database doesn't exist, I need to give an error and show the items not present in the database.

    What I'm looking for is this:
    I have an array of stuff that is sourced from parsing a spreadsheet,but the data should be in the db:
    Code:
    A, B
    A, C
    A, D
    E, F
    E, G
    H, I
    J, K
    Everything in that array MUST be in the database before proceeding.

    Now, I could loop through the list and query it each time, but I need to limit the number of times I'm hitting the DB. So this won't work:

    Code:
    For x = 0 To UBound(ListArray,2)
        strSQL = "SELECT Field1, Field2 FROM Table WHERE Field1 = ListArray(0,x) AND Field2 = ListArray(1,x)"
        'EXEC strSQL
        If Not oRS.EOF Then
            ' Continue
        Else
            ' ERROR AND STOP, NO NEED TO CONTINUE
            x = UBound(ListArray,2)
        End If
    Next
    That results in a DB connection each time. BLAH!

    I'd like a single query to do all that:
    Code:
    SELECT COUNT(*) FROM Table WHERE Field1 = "A" AND Field2 = "B" ... AND Field1 = "J" AND Field2 = "K"
    
    ' (I know that's not a legit query)
    
    iCount = ' result count 
    
    If iCount = UBound(arrayList,2) Then
       ' SUCCESS & continue
    Else
       ' FAIL & error
    End If
    The thing that struck me I *could* do is to just select EVERYTHING from the DB & loop through the results comparing that to my array.

    Seems like I'm missing a query that might give me my result.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Your solution could work:
    Code:
        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        strSQL = "SELECT COUNT(*) AS Cnt FROM <Table> WHERE <Criteria>"
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        If rst!cnt = 0 Then
            ' No match.
        Else
            ' Matching item(s) in the table.
        End If
        rst.Close
        Set rst = Nothing
    However it would be easier to use a domain function:
    Code:
       If DCount("<Field>", "<Table>", "<Criteria>") = 0 Then
            ' No match.
        Else
            ' Matching item(s) in the table.
        End If
    Have a nice day!

  3. #3
    Join Date
    Oct 2009
    Posts
    67
    So does DCount work with a list?

    "Field", "Table", "Field1 = x, Field1 = y, Field1 = z" ?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not like that, but you can use the SQL IN (...) as criteria:
    Code:
    strCriteria = "Field1 IN ( 'x', 'y', 'z' )"
    Have a nice day!

  5. #5
    Join Date
    Oct 2009
    Posts
    67
    Quote Originally Posted by Sinndho View Post
    Not like that, but you can use the SQL IN (...) as criteria:
    Code:
    strCriteria = "Field1 IN ( 'x', 'y', 'z' )"
    But I need the combination of the two fields as the count because Field2 can have duplicates but a different Field1.

    Field1 = Vendor
    Field2 = Part Number

    Some parts ironically can have the same number from different vendors.

    So I need something like this:
    Code:
    (
        (Field1 = 'a' AND Field2 = 'b') 
        AND
        (Field1 = 'a' AND Field2 = 'c') 
        AND
        (Field1 = 'a' AND Field2 = 'd') 
        AND
        (Field1 = 'e' AND Field2 = 'f') 
        AND
        (Field1 = 'i' AND Field2 = 'i') 
    )
    etc. for criteria.

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Can you add your array to a temporary table and use a join to figure out if anything is missing?

  7. #7
    Join Date
    Oct 2009
    Posts
    67
    Quote Originally Posted by DCKunkle View Post
    Can you add your array to a temporary table and use a join to figure out if anything is missing?
    Hmmm, I've not done a lot of temp table stuff, but I'd imagine I could. I'm working on the array compares now. I have limited the size of the larger array by just selecting vendors that are in my list of parts.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    (Field1 = 'a' AND Field2 = 'b')
    AND
    (Field1 = 'a' AND Field2 = 'c')
    AND
    (Field1 = 'a' AND Field2 = 'd')
    AND
    (Field1 = 'e' AND Field2 = 'f')
    AND
    (Field1 = 'i' AND Field2 = 'i')
    This does not make any sense! This would mean that Field1 must be equal to 'a' and Field1 must also be equal to 'e' and that it also must be equal to 'i', while in the mean time Field2 must be equal to 'b' and to 'c' and to 'd' and to 'f' and to 'i'.

    See:
    Boolean Operators
    Boolean logic - Wikipedia, the free encyclopedia
    Using AND, OR, and NOT (Boolean Operators)
    Boolean Operators
    HowStuffWorks "How Boolean Logic Works"
    Have a nice day!

  9. #9
    Join Date
    Oct 2009
    Posts
    67
    Quote Originally Posted by Sinndho View Post
    This does not make any sense! This would mean that Field1 must be equal to 'a' and Field1 must also be equal to 'e' and that it also must be equal to 'i', while in the mean time Field2 must be equal to 'b' and to 'c' and to 'd' and to 'f' and to 'i'.

    See:
    Boolean Operators
    Boolean logic - Wikipedia, the free encyclopedia
    Using AND, OR, and NOT (Boolean Operators)
    Boolean Operators
    HowStuffWorks "How Boolean Logic Works"
    I understand the logic is flawed, that's why I'm trying to figure it out w/o having to run a loop with a query each time to find out if that combination exists.

    I think the temp table outer join is probably the most ideal situation, but I haven't messed with joins enough to have sufficiently grokked it to figure out what in my list doesn't exist in the DB.

    I think what it is is that I've just not thought of it in the right combination to shed the right light on it.

    And thinking about the join & temp table, that requires an insert for each Vendor/Part, which almost defeats the purpose of the issue to do this w/a query with the supplied data.

    I have a way to do it now by just querying once for all parts by each vendor from my Bill of Materials (BOM) and compare it to my shortlist of parts from the BOM. If the short list is NOT in the DB, throw the error.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If it's only to determine if the contents of Field1 is comprised in a set of values and the contents of Field2 is also comprised in a set of values (the same values, different values, or a mix of both), you can stil use the IN operator:
    Code:
    Field1 IN ( 'a', 'b', 'c', 'd', ... ) AND Field2 IN ( 'a', 'd', 'x', 'y', 'z', ... )
    If you want to work with tables containing the sets of allowed values you can do it this way:
    Code:
    SELECT COUNT(<some field>) FROM Table1 WHERE (Field1 IN (SELECT Value FROM Set1)) AND (Field2 IN (SELECT Value FROM Set2))
    Where Set1 and Set2 are the tables containing the sets of allowed values.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •