Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Posts
    23

    Unanswered: A "not found" query

    I am working on an inventory database that contains information about the workstations at our company. I am trying to write a query that tells me software that is NOT installed on a given computer, and use it to design a datasheet subform so I can see who does not have a given software.

    I am working with three tables:
    One contains data on the systems,
    One contains a list of software,
    And one has entries for each system/software combination.

    I have tried using the condition [tblSoftware].[ID]<>[tblSysSoft].[ID] and vice versa, but both give me "duplicate" results... ie:

    System SysSoftID SoftID
    1 3 1
    1 3 2
    1 3 4
    1 3 5
    1 4 1
    1 4 2
    1 4 3
    1 4 5

    Note here that System 1 has software #3 and #4, and both #3 and #4 show up in the query.


    Thanks,
    Rick
    A good cook doesn't use a smoke alarm for a timer.

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    right you cant do this on a M:M relationship as far as i can see (or a 1:M:1) you need to sceen it down to a 1:M this is done with a query

    SELECT systems.id, systems.name, syssoft.softid
    FROM systems INNER JOIN syssoft ON systems.id = syssoft.sysid
    WHERE (((systems.id)=1));

    and a secod that reads

    SELECT soft.id, soft.mane
    FROM Query1 RIGHT JOIN soft ON Query1.softid = soft.id
    WHERE (((Query1.id) Is Null));
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  3. #3
    Join Date
    Aug 2002
    Posts
    23
    Thanks, I'll try that.

    On the other hand, because this query is going to be the record set for a form, I may just set a condition where ID = Forms!MyForm!ID

    Either way, Thanks

    RR

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    that was the idea i was going to suggest but as i didn't now what you were ding with it, i didn't,
    btw if you don't screen it to 1 system the best you can do is what software isn't on any system
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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