How to set up Optional Relationships

Posted on 2004-11-03
Last Modified: 2008-02-01
Hi All,
     I am making a Timesheets program for my company. The company works on different projects and Each employee will book time to different jobs/Projects, that he/she worked on during a particular week. I am basically making the Timesheet program as an extensions to an existing database system. It has two tables Proposal and Projects. Now employees may book time to either a proposal or a Project. This seems to be an optional relationship case i.e. a timesheet record may have a project entry or a proposal entry. The Employee should be given a chance to select either the Proposal No or the Project No for which he wishes to book time to in the timesheet application. Now my questions is how do I implement this optional relationship in MS Access? It's easy to do it on an relationship diagram, but how do I actually implement it a physical level? I would really appreciate some suggestions from all you Access Masters out there. Thanks for all your time and efforts, in advance.

Question by:PMahidharia
    LVL 34

    Expert Comment

    Sounds link many - many to me.

    Say you have a table tblemployee, with the PK being EmpID

    Then tblProjects, with the PK being ProjectID

    Create a 2nd table with both PK's being a FK, then link the 2 main tables (tblemployee & tblProjects) to this new table by one - many

    something like this

    tblemployee                   tblLink                          tblProjects
    EmpID    1-------many    EmpID                    |----1   ProjectID
    FirstName                     ProjectID   Many ----          ProjectName
    LastName                                                             Budget
    Phone                                                                  etc...


    Author Comment

    Hi Flavo,
       I am not sure if I understand you completely. There will be a timesheet table that will hold the timesheet entry record. So if in the timesheet, the user enters say 5 entries (i.e. 5 jobs worked on) for a particular week, each entry will be stored as a seperate reocrd in the timesheet table. I presume, your tblLink table refers to what I call my "timesheet" table. It is easy, however, my question is that I need to allow the user to book time for either a Proposal or a Project and the ProposalNo and ProjectNo come from two seperate tables. How do I set up the relationships so that the field "JobNo" in the timesheet table can accomodate either a ProjectNo or a ProposalNo? That is what I meant by Optional Relationship in my original question.
    LVL 16

    Expert Comment

    What are the relavant fields and indexes of your Timesheet table, PMahidharia?  

    It seems to me that you will need to have both ProposalNo and ProjectNo as seperate fields each with allow null indexing (and the Required property set to Null).  The complication is that this means that these fields cannot be part of your primary key (as that index option will not allow contained fields to have null values).

    Author Comment

    Hi Greyman,
        Well as a rough approximate my timesheet table would have the following structure:

    JobNo, EmpCode, HrsWorked, WeekEndingDate.

    (There may be other fields based on requirement but these are currently the main fields of interest).

    So now if I understand you correctly Greyman, what you are saying is, instead of having only the JobNo field, I should split it up into 2 fields viz. ProjectNo and ProposalNo and let's say if the employee books time to proposal the proposalNo field will be updated and if the time is booked to the Project, the ProjectNo field will be added. I understand exactly what you are saying, because that is what I initially had in mind, but is this the only way to set up an optional relationship in Access? Is it not possible that no matter if the employee selects a proposalno or a projectno, it will update the JobNo field. What I mean is can't the JobNo field in itself suffice or will I need to split it into the ProposalNo and ProjectNo fields?
    LVL 16

    Accepted Solution

    No, you cannot if you wish to maintain any form of referential integrity.  If you turn off the referential integrity checks you will be able to do it, but that means you can freely delete or update the key fields without either prohibiting or cascading changes to the foreign field.
    An alternative would be to link both Proposals and Projects tables to a third table, say JobNumbers, which has a Primary Key of JobNo and IsProject (the second field being a bit flag).  This then becomes the source of all job numbers used by the two tables (so before you create a new record in either you would need to create a new record in JobNumbers).

    Projects would have a primary key of ProjectNo and IsProject, but with an added constraint that IsProject must be True.  Conversely Proposals would have a primary key of ProposalNo and IsProject with the IsProject field constrained to only be False.  Both tables would be related back to JobNumbers through the key fields (preferably with cascade delete referential integrity).  
    Finally Timesheets' primary key would become JobNo, IsProject, EmpCode, WeekEndingDate, and the JobNo and IsProject fields would be related to Jobs rather than directly to the other two tables.


    Fields: JobNo Autonumber, IsProject Yes/No.
    Primary Key: PrimaryKey (JobNo, IsProject)

    Fields: ProjectNo Number, IsProject Yes/No, .....
    Primary Key: PrimaryKey (ProjectNo,IsProject)
    Validation on IsProject: =True

    Fields: ProposalNo Number, IsProject Yes/No, ....
    Primary Key: PrimaryKey (ProposalNo, IsProject)
    Validation on IsProject: =False

      JobNo Number (Long Integer),
      IsProject Yes/No,
      EmpCode Text (10) [or whatever],
      WeekEndingDate DateTime
      HrsWorked Number (Double) [or whatever]
    Primary Key: PrimaryKey (JobNo,IsProject,EmpCode,WeekEndingDate)

      Projects to JobNumbers (1:1, cascade delete)
        on JobNumbers.JobNo = Projects.ProjectNo
        and JobNumbers.IsProject = Projects.IsProject
     Proposals to JobNumbers to  (1:1, cascade delete)
        on JobNumbers.JobNo = Proposals.ProposalNo
        and JobNumbers.IsProject = Proposals.IsProject
     Timesheets to JobNumbers (1:N, cascade delete)
        on JobNumbers.JobNo = Timesheets.JobNo
        and JobNumbers.IsProject = Timesheets.IsProject
    LVL 16

    Expert Comment

    Is that clear as mud?

    Author Comment

    Hi GreyMan,
        Thanks for the answer. Actually I thought that out and was in the process of implementing the same thing that you have suggested in your answer, albeit a bit differently. However, your answer is quite helpful since you took time to provide an example with that and that makes things much more clear. Also, I had 1 or 2 doubts, which your example seems to have cleared it. Thanks for your time and efforts and valuable suggestions. Cheers.
    LVL 16

    Expert Comment

    Alright then!

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now