1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2007: Multiple values in a single field

Discussion in 'Business Applications' started by Omega_Shadow, Jul 24, 2014.

Thread Status:
Not open for further replies.
Advertisement
  1. Omega_Shadow

    Omega_Shadow Thread Starter

    Joined:
    Aug 2, 2004
    Messages:
    4,319
    I have a very simple DB set up. There are five columns: Project | Version | Link | Complete | Dependencies

    My issue is with the Dependencies column. The dependencies column will list projects (see first column) needed by the project in that record. My issue: many of my Projects have four or five Dependencies! How do I enter those multiple values?

    Endgame: I want to be able to print a report that shows me all the projects that are both A) Marked Complete B) Have all Dependencies marked Complete. Also, I want to print a list of complete projects that are being held up by dependencies.

    Is there a way to do this in Access or am I going to have to try with a different program?
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What you want to do is possible but not recommended as it goes against the design principles of Relational Databases.
    The "Sub Projects" should be in a sub table and entered/displayed/edited on a sub Form on your main form.
     
  3. Omega_Shadow

    Omega_Shadow Thread Starter

    Joined:
    Aug 2, 2004
    Messages:
    4,319
    ok. So my question to you is how would you suggest I do this cause I am out of ideas at this point.

    There are no "sub-projects", only projects. While one project may be a dependency for another, both of those projects are still developed at the same time yet interdependently of each other. And there are cases where a project may be the dependency for a project which in turn is a dependency for another project. My fear is if I use a sub forum as you suggest when a project manager decides to drop or add a dependency it is going to be hell to keep straight in the DB.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I would have a Sub table for "Dependent Projects", a Subform set to "Continuous Forms" mode which has a Combo box to select any of the Projects except the Current Record.
    If a dependent project gets dropped either set the record using a flag field to "no lnger valid" or "inactive" or just delete the record.
    I can help you with it if you like.
     
  5. Omega_Shadow

    Omega_Shadow Thread Starter

    Joined:
    Aug 2, 2004
    Messages:
    4,319
    Please. I am definitely not a DB person. But if I had a small POC I think I have enough knowledge to modify it to my needs
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Can you post a dummy zipped DB?
     
  7. Omega_Shadow

    Omega_Shadow Thread Starter

    Joined:
    Aug 2, 2004
    Messages:
    4,319
    ok, here is something I whipped up real quick. Just looking for a proof of concept so I can understand how to do this and then I will create a new database with what you show me. I expect this to expand a bit but I think I can handle it once I got the basics of what your trying to tell me down.

    Just thought of a "Discontinued" field for projects.
     

    Attached Files:

  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I will post it tomorrow.
     
  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, here is the updated database showing how I would approach it.
    Note that the Combo on the Dependent Projects sub form is Re-queried using some simple VBA code to ensure it doesn't list the Current Project record in the main projects form.
    Also have a look at the Table relationships as it shows how the 3 tables are related.
    The 3 ID fields on the Dependent Projects sub form and the ID fields on the main form and Status form do not need to be visible to the user, they are only showing to illustrate how the forms work.
     

    Attached Files:

  10. Omega_Shadow

    Omega_Shadow Thread Starter

    Joined:
    Aug 2, 2004
    Messages:
    4,319
    wow. Ok. Lot's more complicated then I thought. But I see where you are going. Thanks for your work on this. I will absorb what I can and see where to go from here. Thank you
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1130272

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice