Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Contruct of a filter or Query (2000)

    Hi, I am ring rusty to say the least having been out of any building for around 18 months.

    I have built a db with names that have many of three diffferent skills sets. So Say Darren could have many skills within set A, many within B and many within C. THe may have none in either many in one, only or a few or none in others, so as you can see a wide mix. I now need to build a query or a filter that allows users to select persons with certain skills but excluding them when they may have others. So for instance Darren could have skilll set A with skills 1, 2 and 3 and may have skillset B with X Y Z. Users may need to find people who have skill A 1 and A2 but exclude them if they also have B Z. Any support in how I could construct a query with multiple criteria would be appreciated.

    Thanks Darren.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Contruct of a filter or Query (2000)

    Can you tell us how the data have been set up, i.e. the design of the tables? A small sample database would be helpful.

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Contruct of a filter or Query (2000)

    There are four tables on the one side (name and details, one for each skill set and details and these feed into a many table.) Looks like this: Name table with ID and name, skill set A with Id and list of skill sets, duplicated for skill serts B and C. The many table takes the name ID and any number of entries from the skill sets appropriate for that individual. Attached, password is 1234.

    Thanks Darren.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Contruct of a filter or Query (2000)

    Your data design is not correct (if I understand the structure correctly). You should have a separate intermediate table for each many-to-many relationship.
    You can then create queries that select the NameIDs that have specific skills, and combine these in a query that selects the NameIDs that have certain skills but not others.
    See the attached database.
    Attached Files Attached Files

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Contruct of a filter or Query (2000)

    Of course it's structured wrong! I feel so stupid not having followed basic normalisation principles, more rusty than I thought. Thanks for pointing this out.

    Darren.

Posting Permissions

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