Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Unanswered: Finding "unmatched" records with a twist ...

    Yes, another evil question from moi ...

    Scenario:

    I have 2 tables : Items and ItemsSuppliers

    There is a 1 to Many relationship and cascade delete from Items to ItemsSuppliers

    I have a need for getting a list of items NOT assigned to a specific supplier ... I've not had any luck so far with the "Find Unmatched Query" wizard ...

    The keys are ItemID and SupplierID and both are longs ...

    Heck, here's the QBE generated code too:

    SELECT ItemsSupplier.ItemID, ItemsSupplier.SupplierID
    FROM Items LEFT JOIN ItemsSupplier ON Items.ItemID = ItemsSupplier.ItemID
    WHERE ((((ItemsSupplier.ItemID) Is Null)) AND ( ItemsSupplier.SupplierID=242));

    Any insights???

    Thanks!
    Back to Access ... ADO is not the way to go for speed ...

  2. #2
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi "Oh Grand One" :-)

    Ive seen a similar problem to the one you described before! Problem is it was way back and me old grey cells have losta grop on it BUT!
    from what I remember the issue is always the first IS NULL - something tells me that we overcame the issue querying a query.

    We kept the Unmatched as you had to most degree but it was querying the 2nd query which was set to interigate the supplier (242) and all products attached it was this list that was used in the section you have as IS NULL

    Phew that was a head full.........

    do you get my meaning here ( just in from work and brain still in transit mode)


    regards

    gareth

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi M_Owen

    If you are using a left hand join from one table to the other and want to draw nulls from the second table, putting in the ItemsSupplier.SupplierID=242 criteria will prevent that surely? (I am presuming that the ItemsSupplier PK is a combined key of ItemID and ItemsSuppliersID).

    Anyway, perhaps the following is worth a shot:

    SELECT Items.ItemID
    FROM Items LEFT JOIN ItemsSuppliers ON Items.ItemID = ItemsSuppliers.ItemID
    WHERE (((ItemsSuppliers.ItemID) Is Null));

    Just read Gareth's post - thought it seemed a bit easy....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - only tried it with a few items of data - not sure how it will handle big tables because I think there is a bit of a cartesian product going on but:

    SELECT Items.ItemID AS ItemsNotSupplied, ItemsSuppliers.SupplierID
    FROM Items, ItemsSuppliers
    WHERE (((ItemsSuppliers.SupplierID)=242)) and itemssuppliers.itemid not in(items.itemid);

    seems to be ok

    Right - threw in more data - complete nonsense - ignore
    Last edited by pootle flump; 05-17-04 at 16:22.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This time please....

    SELECT Items.ItemID AS ItemsNotSupplied
    FROM Items
    WHERE (((Items.ItemID) Not In (select [itemssuppliers].[itemid] from [itemssuppliers] where [supplierid] = 242)));
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Hi M_Owen

    If you are using a left hand join from one table to the other and want to draw nulls from the second table, putting in the ItemsSupplier.SupplierID=242 criteria will prevent that surely? (I am presuming that the ItemsSupplier PK is a combined key of ItemID and ItemsSuppliersID).

    Anyway, perhaps the following is worth a shot:

    SELECT Items.ItemID
    FROM Items LEFT JOIN ItemsSuppliers ON Items.ItemID = ItemsSuppliers.ItemID
    WHERE (((ItemsSuppliers.ItemID) Is Null));

    Just read Gareth's post - thought it seemed a bit easy....
    Well I tried this one also ... Not quite good enough. It gives you the unmatched items period.

    I gotta make my request a bit clearer ... I'm looking for items that do not have a matching ItemsSupplier record BUT THEY MAY HAVE SAID RECORD WITH ANOTHER SUPPLIER... Like I said : "A twist"

    I did say evil didn't I????

    And I'll try Gareth's idea too ... That may be what I need to do ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    YEP!
    you said evil - strange but the simple in theory seem to be the ones that bite back .....

    gareth
    Last edited by garethfx; 05-17-04 at 16:41.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Unbelievable - didn't realise it at the time but your problem is in essence the flip side of the problem I've been grappling with for about three hours. Just think about it another way and before you know it it's sorted.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    I am not quite sure I am understanding whats going on but I figure I can give it a shot.

    You are wanting to find the ItemSuppliers that have no Items linked?

    I dont really understand the twist of this as I am sure you can tell by my question. If anyone could explain this to me I would be glad to see what I can come up with.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think I got it with third time lucky (post 5 seems to work and, extended to my problem, it works with about 10,000 records). As I understand it (finally), deduct all the items supplied by supplier 242 from the list of all items and see what is left (i.e. all the items not supplied by 242). Of course, may mean that I missed the barn door again.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    I think I got it with third time lucky (post 5 seems to work and, extended to my problem, it works with about 10,000 records). As I understand it (finally), deduct all the items supplied by supplier 242 from the list of all items and see what is left (i.e. all the items not supplied by 242). Of course, may mean that I missed the barn door again.
    You are quite correct. Your 5th posting was it... Here is my final finished result:

    Code:
    Function FillAvailableItems() As Integer
        On Error GoTo Err_FAI
    
        Dim FormsConnect As ADODB.Connection
        Dim TrgRecSet As ADODB.Recordset
        
        Set FormsConnect = New ADODB.Connection
        Set TrgRecSet = New ADODB.Recordset
        
        FormsConnect.CursorLocation = adUseClient
        FormsConnect.Open "DSN=Billing Forms;"
        
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
        SQLString = "DELETE FROM " & AvailableItemsTbl & ";"
        FormsConnect.Execute SQLString, , adCmdText
        
    'SELECT Items.ItemID, Items.Description FROM Items
    'WHERE (Items.ItemID Not In (SELECT ItemsSupplier.ItemID from ItemsSupplier WHERE (ItemsSupplier.SupplierID=864)));
        
        SQLString = "SELECT Items.ItemID, Items.Description FROM Items"
        SQLString = SQLString & " WHERE (Items.ItemID NOT IN (SELECT ItemsSupplier.ItemID FROM ItemsSupplier WHERE (ItemsSupplier.SupplierID=" & SupplierID & ")));"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SQLString = "SELECT * FROM " & AvailableItemsTbl & " WHERE (1=0);"
            TrgRecSet.Open SQLString, FormsConnect
            While MyRecSet.EOF = False
                TrgRecSet.AddNew
                ' Item ID
                TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value
                ' Description
                TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value & ""
                TrgRecSet.Update
                MyRecSet.MoveNext
            Wend
            TrgRecSet.Close
        End If
        MyRecSet.Close
    
        FormsConnect.Close
        Set FormsConnect = Nothing
        Set TrgRecSet = Nothing
        
        LoadingDelay
        
        FillAvailableItems = 0
    
    Exit_FAI:
        Exit Function
        
    Err_FAI:
        
        MsgBox Err.Number & ": " & Err.Description
        FillAvailableItems = 0
        Resume Exit_FAI
    End Function
    Thanks again!
    Back to Access ... ADO is not the way to go for speed ...

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The problem got me thinking though - there is a relational algebra operator called DIFFERNECE that would have been perfect for this problem. You use it like a UNION operator so the SQL solution could have been:

    SELECT ItemID from items
    DIFFERENCE
    SELECT
    itemID from ItemsSuppliers
    WHERE ItemsSupplier.SupplierID=864;

    so the result is all ItemIDs in Items minus any provided by 864. Problem is, Access SQL doesn't recognise the Difference operator and there doesn't appear to be an equivelent. Thought I'd mention it as other more SQL literate forum members might be able to chip in some sage advice on how this could be achieved in a SQL server view.
    Last edited by pootle flump; 05-18-04 at 09:33.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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