Contruct of a filter or Query (2000)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote