How do I show records not in another table in a subform?

[CoreyReynolds]

Hello,

I am currently about to give up after scouring the internet for how to do this, it's probably a relatively novice answer, but I am a relatively novice user.

I have three tables

tblEquipment: (P) equipmentID, equipmentDescription
tblCompartment: compartmentCode, equipmentID, (P)compartmentAutoID
tblCompartmentList: (P)compartmentCode, compartmentName

The equipment table is joined one to many to tblCompartment, and tblCompartmentList is joined one to many on tblCompartment.

I have a Form where users choose from a drop box a piece of equipment. Below that I have a sub form. In that subform I want to display each compartmentCode and compartmentName that has not been entered for that piece of equipment into tblCompartmentID.

Heres my code that doesn't seem to exist for that subform:

SELECT 
    tblCompartmentList.compartmentCode, 
    tblCompartmentList.compartmentName, 
    tblCompartment.equipmentID
FROM 
    tblCompartmentList 
LEFT JOIN tblCompartment 
    ON tblCompartmentList.compartmentCode = tblCompartment.compartmentCode
WHERE 
    (((tblCompartment.compartmentCode) Is Null));

What am I doing wrong? Is there a better way?

[Atli]

Hey.

The simplest solution would probably be to use a Subquery in a NOT IN clause.
Something like:

SELECT 
    l.compartmentName,
    l.compartmentCode
FROM
    tblCompartmentList AS l
WHERE
    l.code NOT IN(
        SELECT
            ic.compartmentCode
        FROM
            tblCompartment AS ic
        WHERE
            ic.equipmentID = $searchID
    )

This is usually the best method to use when you need to find rows that don't match. JOINS are better for finding matching rows.

[CoreyReynolds]

Thanks a lot, makes a lot more sense, the different between all the joins is confusing the hell out of me. There is another part of my database I thought was similar but I guess I was mistaken, the bad code is follows:

SELECT tblCurrentIssues.sampleNumber, tblSample.sampleNumber, tblIssues.IssueDescription
FROM tblSample RIGHT JOIN (tblIssues INNER JOIN tblCurrentIssues ON tblIssues.issueID = tblCurrentIssues.IssueID) ON tblSample.sampleNumber = tblCurrentIssues.sampleNumber;

Basically There is a main form with a subform inside and the above is the recordsource of listbox inside the subform. It is supposed to populate the listbox with all of the issue ID's and descriptions of those issues that are linked to that sample.

The tables are tblSample -onetoMany- tblCurrentIssues - oneToMany - tblIssues


Nothing shows up, I tried changing the RIGHT join back to an INNER join but then all of the records in tblCurrentIssue get displayed for every single sample :/


Any ideas?

[CoreyReynolds]

And one more thing, I'm still having a little trouble with the origional code you gave me, I have:

SELECT tblCompartmentList.compartmentName, tblCompartmentList.compartmentCode
FROM tblCompartmentList
WHERE (((tblCompartmentList.compartmentCode) Not In (SELECT tblCompartment.compartmentCode FROM tblEquipment INNER JOIN tblCompartment ON tblEquipment.equipmentID = tblCompartment.equipmentID;      )));

But it's not showing any records at all?

[Atli]

@CoreyReynolds
I see two problems in your code.

#1 In your sub-query, you don't specify an equipment to search for. Therefore, the entire table will be returned, including all the codes being used by all equipment.
This query is meant to search for codes missing for a single piece of equipment, so you must specify a single equipment ID in your sub-query.

#2 There is a semi-colon (;) trailing the sub-query. This is not allowed, and will cause an error. That query should be giving you an error, rather than an empty result set.
How are you executing the query? As a part of a web-application, perhaps?

Your query should look more like:

SELECT 
    l.compartmentName, 
    l.compartmentCode
FROM 
    tblCompartmentList AS l
WHERE 
    l.compartmentCode Not In (
        SELECT c.compartmentCode 
        FROM tblEquipment AS eq
        INNER JOIN tblCompartment AS c
            -- Removed the ; from this line:
            ON eq.equipmentID = c.equipmentID
            -- Added this line to specify the equipment to be searched for:
            AND eq.equipmentID = <insert ID>
    );

Also, note the following changes:

• The formatting. It's a lot easier to read the query in this form, wouldn't you agree? It makes it a lot easier to spot problems when code is properly formatted.
• The AS clauses on the table names.
  If you intend to use a lot of JOINS or Sub-queries, giving long table names simple aliases will make the query easier to read, and make them shorter. (Every byte counts on high-traffic sites.)
• You had a bunch of unnecessary parentheses around your WHERE clause, which I removed. No need to make things more complicated than is needed ;-)

[CoreyReynolds]

Agreed agreed agreed,

It is working 100% now.

Sorry about not formatting everything properly (and not wrapping them properly for this forum, I didn't see your note to me about that until I already did it a second time like a moron).

Access puts the multiple parenthesis there though all on its own, I have no idea why.

I got to tell you Atli, I'm working up in the frozen dead north right now not anywhere near civilization and you've helped me more then you will ever know. Thanks again.