• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

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.

  • 4
  • 3
1 Solution
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).
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now