If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table design and relationships to support calculation in query
I am developing a database for managing projects.
My main table is: tblProjects ProjectID (PK) Deliverable Priority etc. Here are the tables I am struggling with: tblEstimateDetail EstimateDetailID (PK) ProjectID (FK) RateID (FK) Hours (for number of work hours) tblEstimateDetail will allow user to input the number of estimated hours to complete a give task for the associated project. RateID in tblEstimateDetail is tied to tblRates: tblRates RateID (PK) CostType (e.g. Business Analyst, Project Manager, QA, & Development) HourlyRate tblRates will allow user to input the hourly rate for a specific type of project cost. Here are my qusetions: 1) Should I establish a one:many relationship between tblEstimateDetail and tblRates OR should I use a simple lookup feature when in design view for tblEstimateDetail? 2) When I design a Query to multiply the Hours [tblEstimateDetail] by HourlyRate [tblRates] should I use the ProjectID from tblProjects or from tblEstimateDetail? Does it matter? 3) Is this the best way to even go about giving the user the ability to enter in a task, define what type of work will be done (Business Analyst, etc.) and calculate what the associated cost will be depending on the number of hours and type of work? Thank you greatly for your help. |
#2
|
|||
|
|||
Table design and relationships to support calculation in query
----- Original Message ----- From: "Mara" Newsgroups: microsoft.public.access.tablesdbdesign Sent: Wednesday, March 26, 2008 2:18 PM Subject: Table design and relationships to support calculation in query I am developing a database for managing projects. My main table is: tblProjects ProjectID (PK) Deliverable Priority etc. Here are the tables I am struggling with: tblEstimateDetail EstimateDetailID (PK) ProjectID (FK) RateID (FK) Hours (for number of work hours) tblEstimateDetail will allow user to input the number of estimated hours to complete a give task for the associated project. RateID in tblEstimateDetail is tied to tblRates: tblRates RateID (PK) CostType (e.g. Business Analyst, Project Manager, QA, & Development) HourlyRate tblRates will allow user to input the hourly rate for a specific type of project cost. Here are my qusetions: 1) Should I establish a one:many relationship between tblEstimateDetail and tblRates OR should I use a simple lookup feature when in design view for tblEstimateDetail? Niether. Create a table called ProjectCosteEstimates with ProjectID and RateID as primary keys and an attribute called EstimatedHours. This will support the fact that a Project has MANY Rates and A Rate is involved in MANY Projects This will also let you add a Rate not associated with a Project. 2) When I design a Query to multiply the Hours [tblEstimateDetail] by HourlyRate [tblRates] should I use the ProjectID from tblProjects or from tblEstimateDetail? Does it matter? You will need to JOIN on ProjectID and RateID to do the calculation. 3) Is this the best way to even go about giving the user the ability to enter in a task, define what type of work will be done (Business Analyst, etc.) and calculate what the associated cost will be depending on the number of hours and type of work? That is a UI design issue. Not one of my strong points. Thank you greatly for your help. You really should think out your design. Do you really need the estimate detail table if you use my suggestion ? Good luck! Evan |
#3
|
|||
|
|||
Table design and relationships to support calculation in query
I can make suggestions about your first 2 questions.
Join in the Relationship window from the PKs in tblProjects and tblRates to the FKs in the tblEstimateDetail. For inputting, please don't use a lookup table until you've read posts in this newsgroup about the confusion they cause. You'll want the advantages of using a form eventually so you may as well start as you mean to go on. Create a Single Form based on tblProject only Create a query based on tblEstimateDetails. Add its PK, the FKs from this table to the query. Add tblRate to the query but don't add its RateID primary key field or the Costype field to the query, just the HourlyRate one.. Open the single form in design view. Slide your closed query from the Database window onto the grey grid in the Details sectionof the form. The wizard should activate and join this new-born subform to the main form by ProjectID (if not, it can be done without the wizard) Add a combo box based on tblRates with RateID (hidden) and CostType so that you can choose your Rate. If you want to do arithmetic, you can do this in this query on which this subform is based. You can decide if you want to view the results in your form or not. You will probably, depending on your circumstances, want to add a EstimateRate to your EstimateDetail table. This stores the rate you gave to that Estimate at that time. It can be done quite easily using code in the form you are going to use to input your data. We can talk you through this. If you don't do this and then you change the rate for say, a Project Manager in tblRates, all your past records will change too. The Rate in the Rates table will be used in your code so it is not superfluous. Evi "Mara" wrote in message ... I am developing a database for managing projects. My main table is: tblProjects ProjectID (PK) Deliverable Priority etc. Here are the tables I am struggling with: tblEstimateDetail EstimateDetailID (PK) ProjectID (FK) RateID (FK) Hours (for number of work hours) tblEstimateDetail will allow user to input the number of estimated hours to complete a give task for the associated project. RateID in tblEstimateDetail is tied to tblRates: tblRates RateID (PK) CostType (e.g. Business Analyst, Project Manager, QA, & Development) HourlyRate tblRates will allow user to input the hourly rate for a specific type of project cost. Here are my qusetions: 1) Should I establish a one:many relationship between tblEstimateDetail and tblRates OR should I use a simple lookup feature when in design view for tblEstimateDetail? 2) When I design a Query to multiply the Hours [tblEstimateDetail] by HourlyRate [tblRates] should I use the ProjectID from tblProjects or from tblEstimateDetail? Does it matter? 3) Is this the best way to even go about giving the user the ability to enter in a task, define what type of work will be done (Business Analyst, etc.) and calculate what the associated cost will be depending on the number of hours and type of work? Thank you greatly for your help. |
#4
|
|||
|
|||
Table design and relationships to support calculation in query
Hi Evan,
Thank you for your reply. I do not think I understand how tblProjectCostEstimate will function different than tblEstimateDetail. The fields you describe in tblProjectCostEstimate are the same that I have in tblEstimateDetail. Can I just assign a one to many relationship between ProjectID in tblProjects and tblEstimateDetail AND RateID in tblRates and tblEsimateDetail and accomplish the same thing? Thanks again. "Evan Keel" wrote: ----- Original Message ----- From: "Mara" Newsgroups: microsoft.public.access.tablesdbdesign Sent: Wednesday, March 26, 2008 2:18 PM Subject: Table design and relationships to support calculation in query I am developing a database for managing projects. My main table is: tblProjects ProjectID (PK) Deliverable Priority etc. Here are the tables I am struggling with: tblEstimateDetail EstimateDetailID (PK) ProjectID (FK) RateID (FK) Hours (for number of work hours) tblEstimateDetail will allow user to input the number of estimated hours to complete a give task for the associated project. RateID in tblEstimateDetail is tied to tblRates: tblRates RateID (PK) CostType (e.g. Business Analyst, Project Manager, QA, & Development) HourlyRate tblRates will allow user to input the hourly rate for a specific type of project cost. Here are my qusetions: 1) Should I establish a one:many relationship between tblEstimateDetail and tblRates OR should I use a simple lookup feature when in design view for tblEstimateDetail? Niether. Create a table called ProjectCosteEstimates with ProjectID and RateID as primary keys and an attribute called EstimatedHours. This will support the fact that a Project has MANY Rates and A Rate is involved in MANY Projects This will also let you add a Rate not associated with a Project. 2) When I design a Query to multiply the Hours [tblEstimateDetail] by HourlyRate [tblRates] should I use the ProjectID from tblProjects or from tblEstimateDetail? Does it matter? You will need to JOIN on ProjectID and RateID to do the calculation. 3) Is this the best way to even go about giving the user the ability to enter in a task, define what type of work will be done (Business Analyst, etc.) and calculate what the associated cost will be depending on the number of hours and type of work? That is a UI design issue. Not one of my strong points. Thank you greatly for your help. You really should think out your design. Do you really need the estimate detail table if you use my suggestion ? Good luck! Evan |
#5
|
|||
|
|||
Table design and relationships to support calculation in query
Hi Evi,
Thank you for your response. I was successful in following your instruction until I got to this point: "Add a combo box based on tblRates with RateID (hidden) and CostType so that you can choose your Rate. If you want to do arithmetic, you can do this in this query on which this subform is based. You can decide if you want to view the results in your form or not." I created the query, the subform, and dumped the subform into my main form. Am I still in Design View of this newly created subform when I am performing this task? Thanks a million. "Evi" wrote: I can make suggestions about your first 2 questions. Join in the Relationship window from the PKs in tblProjects and tblRates to the FKs in the tblEstimateDetail. For inputting, please don't use a lookup table until you've read posts in this newsgroup about the confusion they cause. You'll want the advantages of using a form eventually so you may as well start as you mean to go on. Create a Single Form based on tblProject only Create a query based on tblEstimateDetails. Add its PK, the FKs from this table to the query. Add tblRate to the query but don't add its RateID primary key field or the Costype field to the query, just the HourlyRate one.. Open the single form in design view. Slide your closed query from the Database window onto the grey grid in the Details sectionof the form. The wizard should activate and join this new-born subform to the main form by ProjectID (if not, it can be done without the wizard) Add a combo box based on tblRates with RateID (hidden) and CostType so that you can choose your Rate. If you want to do arithmetic, you can do this in this query on which this subform is based. You can decide if you want to view the results in your form or not. You will probably, depending on your circumstances, want to add a EstimateRate to your EstimateDetail table. This stores the rate you gave to that Estimate at that time. It can be done quite easily using code in the form you are going to use to input your data. We can talk you through this. If you don't do this and then you change the rate for say, a Project Manager in tblRates, all your past records will change too. The Rate in the Rates table will be used in your code so it is not superfluous. Evi "Mara" wrote in message ... I am developing a database for managing projects. My main table is: tblProjects ProjectID (PK) Deliverable Priority etc. Here are the tables I am struggling with: tblEstimateDetail EstimateDetailID (PK) ProjectID (FK) RateID (FK) Hours (for number of work hours) tblEstimateDetail will allow user to input the number of estimated hours to complete a give task for the associated project. RateID in tblEstimateDetail is tied to tblRates: tblRates RateID (PK) CostType (e.g. Business Analyst, Project Manager, QA, & Development) HourlyRate tblRates will allow user to input the hourly rate for a specific type of project cost. Here are my qusetions: 1) Should I establish a one:many relationship between tblEstimateDetail and tblRates OR should I use a simple lookup feature when in design view for tblEstimateDetail? 2) When I design a Query to multiply the Hours [tblEstimateDetail] by HourlyRate [tblRates] should I use the ProjectID from tblProjects or from tblEstimateDetail? Does it matter? 3) Is this the best way to even go about giving the user the ability to enter in a task, define what type of work will be done (Business Analyst, etc.) and calculate what the associated cost will be depending on the number of hours and type of work? Thank you greatly for your help. |
#6
|
|||
|
|||
Table design and relationships to support calculation in query
Did the wizard activate to let you link your Subform to the main form using
ProjectID? (this means that if you turn to a new Project in the main form, the subform will list only the rates for that project) If it hasn't (or you couldn't read the line in the wizard that lets you do this - it's often a bit narrow) click on your Subform. Click on the Properties button on the Toolbar. Click on the Data tab. click in the grey area to the right of to where it says Link Master Fields and Link Child Fields, choose ProjectID. To add a combo box to your form, open the subform by itself in Design View , drag a combo box control from the Toolbox toolbar to the Details section of your form. The Wizard should now activate Choose to 'base it on a table or query'. Choose your tblRates You may not need all the fields in tblRates, choose RateID and CostType The Wizard will probably hide the first column which contains the ID number, if not, drag the column closed in the Wizard window. Evi "Mara" wrote in message ... Hi Evi, Thank you for your response. I was successful in following your instruction until I got to this point: "Add a combo box based on tblRates with RateID (hidden) and CostType so that you can choose your Rate. If you want to do arithmetic, you can do this in this query on which this subform is based. You can decide if you want to view the results in your form or not." I created the query, the subform, and dumped the subform into my main form. Am I still in Design View of this newly created subform when I am performing this task? Thanks a million. "Evi" wrote: I can make suggestions about your first 2 questions. Join in the Relationship window from the PKs in tblProjects and tblRates to the FKs in the tblEstimateDetail. For inputting, please don't use a lookup table until you've read posts in this newsgroup about the confusion they cause. You'll want the advantages of using a form eventually so you may as well start as you mean to go on. Create a Single Form based on tblProject only Create a query based on tblEstimateDetails. Add its PK, the FKs from this table to the query. Add tblRate to the query but don't add its RateID primary key field or the Costype field to the query, just the HourlyRate one.. Open the single form in design view. Slide your closed query from the Database window onto the grey grid in the Details sectionof the form. The wizard should activate and join this new-born subform to the main form by ProjectID (if not, it can be done without the wizard) Add a combo box based on tblRates with RateID (hidden) and CostType so that you can choose your Rate. If you want to do arithmetic, you can do this in this query on which this subform is based. You can decide if you want to view the results in your form or not. You will probably, depending on your circumstances, want to add a EstimateRate to your EstimateDetail table. This stores the rate you gave to that Estimate at that time. It can be done quite easily using code in the form you are going to use to input your data. We can talk you through this. If you don't do this and then you change the rate for say, a Project Manager in tblRates, all your past records will change too. The Rate in the Rates table will be used in your code so it is not superfluous. Evi "Mara" wrote in message ... I am developing a database for managing projects. My main table is: tblProjects ProjectID (PK) Deliverable Priority etc. Here are the tables I am struggling with: tblEstimateDetail EstimateDetailID (PK) ProjectID (FK) RateID (FK) Hours (for number of work hours) tblEstimateDetail will allow user to input the number of estimated hours to complete a give task for the associated project. RateID in tblEstimateDetail is tied to tblRates: tblRates RateID (PK) CostType (e.g. Business Analyst, Project Manager, QA, & Development) HourlyRate tblRates will allow user to input the hourly rate for a specific type of project cost. Here are my qusetions: 1) Should I establish a one:many relationship between tblEstimateDetail and tblRates OR should I use a simple lookup feature when in design view for tblEstimateDetail? 2) When I design a Query to multiply the Hours [tblEstimateDetail] by HourlyRate [tblRates] should I use the ProjectID from tblProjects or from tblEstimateDetail? Does it matter? 3) Is this the best way to even go about giving the user the ability to enter in a task, define what type of work will be done (Business Analyst, etc.) and calculate what the associated cost will be depending on the number of hours and type of work? Thank you greatly for your help. |
Thread Tools | |
Display Modes | |
|
|