PMahidharia
asked on
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.
Regards:
Prathmesh
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.
Regards:
Prathmesh
ASKER
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.
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).
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).
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is that clear as mud?
ASKER
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.
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!
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...
etc.....
Idea???