Results 1 to 1 of 1
  1. #1
    Join Date
    Jan 2014

    Unanswered: Many to Many relationship and queries

    Hi there.

    I am attempting to create a simple access database to store contact information of various contractors.

    Each contractor provides a different service/trade (carpentry, electrical etc.) and each service can be provided by more than one contractor. this is all easy enough.

    However some contractors provide multiple trades and this is where I'm having problems.

    I currently have it set up with one table containing the contractors infomation (name, phone etc.) and one table that contains a list of trades. there is a third junction table linking them together to make a many to many relationship.

    I need to be able to run queries returning contractors that supply a certain trade. the three sets of results will be
    1. contractors that supply trade x (which should return all trade x only contractors and all multicontractors that supply trade x)
    2. Contractors that supply trade y (same as above but with trade y)
    3 contractors that supply x and y (this should only return contractors who supply trades x AND y)

    the first 2 i've sorted but i'm having trouble with the third.

    EDIT: I'm also not sure on how i'd go about making a form for entering this. is it possible to have one record entry form that associates 2 trades with a newly entered contractor or would i have to create 2 entries, one for each trade?
    Last edited by james0192; 01-21-14 at 11:24. Reason: Thought of something else I can't do

Posting Permissions

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