Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting up a Multi-User Environment (2007)

    I've made my project database (based on the MS project management template) do what I want it to do, which is great. Now i'm looking to add some functionality to it for other people by adding a field that lets you filter projects by who owns it and start to make projects a collaborative effort. So for instance, we have 3 project engineers here: John, Mark, & Ron. If i wanted to view my projects i would want to select "John" from the project engineer list and make form that displays my various projects. If Mark or Ron wanted to do the same they could do so by selecting their names from the list - which would allow our boss to also select one of use to view our projects at any time and see the progress being made on them. I also want an "all" selection to view it unfiltered.

    I think I want to make a new combobox field that says "Project Engineer" with a query based on who its assigned to. If i did the combobox w/ a new field for project engineer, what table would it be recommended to put the field "Project Engineer" in? would it go into all of them? The attached picture shows the relationships & tables i'm currently using. Does this make sense?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Setting up a Multi-User Environment (2007)

    Will each project have a single project engineer? If so, you can add the project engineer field to the projects table.
    If two (or more) project engineers could be working on the same project, you'd have a many-to-many relationship, requiring an intermediate table in which each project + project engineer combination is a separate record.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting up a Multi-User Environment (2007)

    Wow, fast response! there can be multiple project engineers on the same project. So i'm a bit confused @ the setup you suggested: I need a new table with each project + project engineer combination... this i don't understand. What fields do the many to many relationship connect? And the intermediate table would only have the 2 fields? Would this affect any other part of the database table structure i already have in place?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Setting up a Multi-User Environment (2007)

    One table would list all project engineers, with -say- EngineerID as primary key.
    Another table would list all projects, with -say- ProjectID as primary key (in your Projects table, it's the ID field).
    The intermediate table would have at least two fields: ProjectID and EngineerID, and possibly more if you need to store information specific to this project and engineer.
    The table would look like this:

    <table border=1><td align=center>ProjectID</td><td align=center>EngineerID</td><td align=center>1</td><td align=center>1</td><td align=center>1</td><td align=center>4</td><td align=center>2</td><td align=center>1</td><td align=center>2</td><td align=center>2</td><td align=center>3</td><td align=center>2</td><td align=center>4</td><td align=center>3</td><td align=center>4</td><td align=center>4</td><td align=center>4</td><td align=center>5</td></table>
    In this example, project 1 has two project engineers: 1 and 4. Engineer 1 works on two projects: 1 and 2.

    See <post#=364,203>post 364,203</post#> for a sample database demonstrating how to use such an intermediate table.

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting up a Multi-User Environment (2007)

    OK, i'm starting to get it now. So what if i wanted to use the Owner's field that you see in the Projects table in the picture from the orginal post? This way i keep the integrity of the employee information since those ID's are already established. It looks as though the database is already doing that with the ID <-1 to many-> Owner relationship of the employees & projects tables. Do i already have the intermediate table we are talking about?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Setting up a Multi-User Environment (2007)

    If the owner field represents the project engineer, you'll be able to assign only one engineer per project. If you want to be able to assign multiple "owners", do the following:
    - Create a table ProjectEngineers with 2 fields: ProjectID and EmployeeID, both Number (Long Integer).
    - Make the combination of the two fields the primary key in the ProjectEngineers table.
    - Set up relationships between ProjectID and ID in the Projects table and between EmployeeID and ID in the Employees table. Enforce referential integrity for both relationships.
    - Create an Append query based on the Projects table.
    - Set the new table as target for the append query.
    - Add the ID and Owner fields to the query grid, and specify ProjectID and EmployeeID as target fields for them.
    - Execute the query (Query | Run).
    - You can now delete the direct relationship between Employees and Projects, and delete the Owner field from Projects.

  7. #7
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting up a Multi-User Environment (2007)

    OK I've done all that. Now when i open up my projects form i get an "enter parameter value" msg box. obviously i need to change the source of the "Owner" value to something else. What is this something else? EmployeeID?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Setting up a Multi-User Environment (2007)

    Since there can now be multiple "owners" of a project, you cannot use a text box for it, so you should remove the "Owner" text box from the form.
    Instead, use a subform based on the ProjectEngineers table, with a combo box to select the engineer from. The subform could be placed on an existing tab page, or on a new tab page.
    The sample database I pointed you to shows how it's done.

  9. #9
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting up a Multi-User Environment (2007)

    I would think it would make the most sense to display that on the main tab - the project details tab. I'll try this out after lunch and, as i'm sure you already know <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>, i'll report back if i have any problems. Thanks a bunch, Hans!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  10. #10
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting up a Multi-User Environment (2007)

    You could use the new multi value control in Access 2007. See http://office.microsoft.com/en-us/ac...311171033.aspx
    I am not advocating this, just suggesting since you are using 2007 and this is supposed to be one of the improvements <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

  11. #11
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting up a Multi-User Environment (2007)

    I tried to mimic the example as best as i could copying all of the properties. the form's record source is from the table ProjectEngineers. The row source of the combo box is:

    SELECT <!t>[Employees Extended]<!/t>.<!t>[Employee Name]<!/t> FROM <!t>[Employees Extended]<!/t> ORDER BY <!t>[Employee Name]<!/t>;

    but whenever i click a name in 1 box, this is what i get:
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  12. #12
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting up a Multi-User Environment (2007)

    Note: The new lists take their data from another database component called a multivalued lookup field. You must have a multivalued lookup field in one of your database tables before you can create multivalued lists. Explaining how to create multivalued lookup fields is beyond the scope of this article.

    so apparently this is beyond the scope of my brain <img src=/S/hansv.gif border=0 alt=HansV width=27 height=26>

    but thanks for the info, maybe one day...
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Setting up a Multi-User Environment (2007)

    1) The combo box should be bound to the EmployeeID field. I assume this is a number field.
    2) The Row Source of the combo box should be something like

    SELECT ID, [Employee Name] FROM [Employees Extended] ORDER BY [Employee Name]

    Its Column Count should be 2 and its Column Widths property <code>0";1"</code>.
    3) The subform should be linked to the main form through its Link Master Fields and Link Child Fields properties; they should be set to ID and ProjectID, respectively.

  14. #14
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting up a Multi-User Environment (2007)

    It works! Thanks Hans! According to my post count, i owe you 670 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Setting up a Multi-User Environment (2007)

    * Hickup *

Page 1 of 2 12 LastLast

Posting Permissions

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