Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    56

    Unanswered: Query That Excludes Another Query's Values

    Access 2007

    I have the following tables

    tblEquipment
    -EquipmentID
    -EquipmentTypeID
    -Mfr
    -Mdl

    tblEquipmentTypes
    -EquipmentTypeID
    -EquipmentType

    tblMaintChecks
    -MaintCheckID
    -EquipmentTypeID
    -Description
    -AddCheck

    tblEquipmentMaintChecks
    -ID
    -EquipmentID
    -MaintCheckID


    I have main form (frmEquipment) and continuous subform (sfrmMaintChecks) that are not linked. The main form contains the equipment information. The subform is filtered to show records from [tblMaintChecks] where [tblMaintChecks].[EquipmentTypeID] = [frmEquipment].[EquipmentTypeID]. But I want to exclude records where [tblMaintChecks].[MaintCheckID] = [tblEquipmentMaintChecks].[MaintCheckID] where [tblEquipmentMaintChecks].[EquipmentID]=[frmEquipment].[EquipmentID].

    I am basically using the form to suggest, via [EquipmentTypeID], what [MaintCheckID]'s to add to each equipment. The form allows me to add multiple [MaintCheckID]'s per [EquipmentID]'s to [tblEquipmentMaintChecks]. But I don't want it to suggest [MaintCheckID]'s that I have already added. Any ideas?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    drop the ID column for tblEquipmentMaintChecks
    and make the PK a composite of EquipmentID & MaintCheckID
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2011
    Posts
    56
    The tables are actually sharepoint lists. I don't think I can create composite pk's.

  4. #4
    Join Date
    Aug 2011
    Posts
    56
    I found a solution that works. I used Access's query builder to make an unmatched data query that compares data between my two queries.

Posting Permissions

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