How to set up Optional Relationships

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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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...

PMahidhariaAuthor Commented:
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.
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).
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PMahidhariaAuthor Commented:
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?
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Is that clear as mud?
PMahidhariaAuthor Commented:
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.
Alright then!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.