Results 1 to 15 of 15
  1. #1
    stewegg is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    26

    Multiple Companies, Multiple Independent Consultants and Multiple Projects using subforms

    Hi guys,

    I have a database with three tables that contain companies, independent consultants and projects.
    One project would only have one company and one consultant associated to it.
    However, one company could work with multiple consultants and have multiple projects.
    Same for the consultants: one consultant could work on multiple project and for multiple companies.



    Table Setup:
    Projects
    [ID][CompanyID][ConsultantID][ProjectInfo...]

    Companies
    [ID][CompanyInfo....]


    Consultants

    [ID][ConsultantInfo...]


    Now, two forms are created: one for company and another for consultant.
    In the company form, I need to show both the projects that are associated to it and consultants that work/worked for the company.
    In the consultant form, I need to show both the projects that it work/worked on and the companies that it worked for.

    Company form
    I am using two subforms (continues form) in each of the forms.
    However, I am having trouble linking the consultants to it.

    Is there way to send multiple IDs (ConsultantID) from the project subform to the consultant subform so it will show all the consultants that worked for the company?


    Thank you very much

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The table structure isn't conducive to easy reporting of data. There should be 3 master lists of data, one table for each set of data. Then a 4th table will combine the data and will contain the PK from each of the three master list tables: saying project ID 1 is for company ID 3 and is using consultant ID 7. That way you can mix and match reporting any way you please very easily.

  3. #3
    stewegg is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    26
    Hi Aytee111,

    Thx for the suggestion.
    Is there a easy/feature in Access that can generate the 4th table?

    After the 4th table is generated: how would the Company form populate?

    Thank you very much

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not sure what you mean by "generate" a table. To create the table go to Create>Table and add a table with the same names as the primary key on the other three tables (don't call fields by generic names, always use proper names, such as ProjectID, CompanyID, etc.)

    To add data to the table have a form bound to this table with three comboboxes (or listboxes) and the user will select one from each.

  5. #5
    stewegg is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    26
    Just to make sure I understand it correctly.

    For the Company form:
    the form is bound to 4th table (InfoTable)
    with three subforms that link to each of the IDs (ProjectID, CompanyID, ConsultantID) to show their each detail.
    What would happen if the user want to change Consultant #1 that worked on Project #1 for Company A to Consultant #2?
    Because changing the information in the consultant subform would only change the actual information of that particular consultant; not the linkage (from Consultant #1-Company A-Project #1 to Consultant #2-Company A-Project #1) or would it?

    Thank you

  6. #6
    stewegg is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    26
    I am still confused.
    How would the Company form with the 4th table (InfoTable) bound to it shows the associated consultants and projects related to it?

    for example:
    there are 3 different consultants worked for company B for 4 different projects.
    How can I build the Company form based on the InfoTable to show such information when I use....
    Code:
    DoCmd.OpenForm "frm_Company", , , "[CompanyID]='" & Me.[CompanyID] & "'"
    I am thinking of using continues subforms. How would I link them?

    What I want to show in the frm_Company are.....
    Company Info
    3 different consultants and their info
    4 different projects and their info

    Thank you very much

  7. #7
    stewegg is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    26
    So.....after a long night....
    I finally figured it out.

    Use the InfoTable to create a subform that link to to the Company form using ID (from Company form) as master and CompanyID (from InfoTable) as child link.

    Thank you very much for your help.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Sorry for the delay in responding - so everything is working correctly now?

  9. #9
    stewegg is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    26
    Yes. Every thing is working working correctly now. Thank you for your help.

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Well done for sorting it all out on your own. Very impressive!

  11. #11
    stewegg is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    26
    Thank you very much for your help! Without it, I would not have been able to solve this.

  12. #12
    stewegg is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    26
    Hi aytee111,

    I guess it's harder than I thought.
    More issues now.....
    I ran the "Compatibility Checker" so I can publish it to a SharePoint site.
    The 4th table (InfoTable) is giving me relationship errors.
    ACCWeb105016: Relationships that are not associated with a Web-compatible lookup are incompatible with the Web.

    Any thoughts and idea on how to fix this?

    Thank you

  13. #13
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Having never done anything on Sharepoint, sorry but I can't help. I googled that error, tho, and there seems to be a lot out there on it.

  14. #14
    stewegg is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    26
    Problem Solved!
    The reason for all these "Relationships" issues are because of the frontend and backend database.
    The
    InfoTable was created after the database was split with the other three tables.It was not created properly (it was created in the frontend).
    I now moved it to the backend and wala! Problem solved.
    You got to love the not so helping and total useless MS office support documentations.

  15. #15
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Well done! Yes, MS documentation leaves a lot to be desired. Thank goodness for forums!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multiple subforms using one query
    By thopp23c in forum Forms
    Replies: 29
    Last Post: 10-26-2015, 11:41 AM
  2. multiple subforms
    By slimjen in forum Forms
    Replies: 3
    Last Post: 06-24-2014, 07:56 AM
  3. Multiple Companies Same Invoice
    By mikajake in forum Database Design
    Replies: 3
    Last Post: 02-28-2013, 01:39 AM
  4. Tab index multiple subforms
    By TToc2u in forum Forms
    Replies: 3
    Last Post: 02-23-2013, 06:00 PM
  5. Replies: 2
    Last Post: 07-18-2012, 08:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums