How to find unused items

Posted on 2012-09-17
Last Modified: 2012-09-17
I have a main table (tblMaster) that contains the inventory items. I need to know which have never been used. I have other 4 tables (tblMov1, tblMov2 ...) that contain various types of movements. I thought of using the right join progressive.

I use Visual Basic 6 and ADODB connection.
I have write this code:

    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    Dim tblMaster As ADODB.Recordset
    Dim tblMov1 As ADODB.Recordset
    Dim tblMov2 As ADODB.Recordset
    Dim tblMov3 As ADODB.Recordset
    Dim tblMov4 As ADODB.Recordset
    Dim TblNotUsed As ADODB.Recordset
    conn.Open "DSN_ABACUS", "sa", "EdisoftwareSa09"
    Set tblMaster = New ADODB.Recordset
    Set tblMov1 = New ADODB.Recordset
    Set tblMov2 = New ADODB.Recordset
    Set tblMov3 = New ADODB.Recordset
    Set tblMov4 = New ADODB.Recordset
    Set TblNotUsed = New ADODB.Recordset

    TblNotUsed.CursorLocation = adUseClient
    TblNotUsed.Open "SELECT tblMaster.CodArt FROM tblMov1 RIGHT JOIN tblMaster ON tblMov1.CodArt=tblMaster.CodArt WHERE tblMov1.CodArt Is Null", conn, adOpenDynamic, adLockBatchOptimistic
    TblNotUsed.Open "SELECT TblNotUsed.CodArt FROM tblMov2 RIGHT JOIN TblNotUsed ON tblMov2.CodArt=TblNotUsed.CodArt WHERE tblMov2.CodArt Is Null", conn, adOpenDynamic, adLockBatchOptimistic

but does not work ...

thanks in advance.
Question by:marcozamperlin
    LVL 37

    Expert Comment

    your query looks fine
    what do you mean by - it does not work?

    Author Comment


    The second query should be based on the results of the first. And the third (that I have not reported) of the second and so on ... So that the end will know what are the items that have not been handled in the 4 tables tblMovx.
    LVL 68

    Accepted Solution

    NOT EXISTS should be more efficient in this case:

    SELECT tblMaster.CodArt
    FROM tblMaster m0
        NOT EXISTS (SELECT 1 FROM dbo.tblMov1 m1 WHERE m1.CodArt = m0.CodArt) AND
        NOT EXISTS (SELECT 1 FROM dbo.tblMov2 m2 WHERE m2.CodArt = m0.CodArt) AND
        NOT EXISTS (SELECT 1 FROM dbo.tblMov3 m3 WHERE m3.CodArt = m0.CodArt) AND
        NOT EXISTS (SELECT 1 FROM dbo.tblMov4 m4 WHERE m4.CodArt = m0.CodArt)
    LVL 68

    Expert Comment

    OOPS, just noticed, you may need to change "AND"s to "OR"s, depending on what you are trying to do.

    But the general principal is still the same.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now