Table design and relationships to support calculation in query

  • Thread starter Thread starter Mara
  • Start date Start date
M

Mara

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.
 
----- Original Message -----
From: "Mara" <[email protected]>
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?
Click to expand...

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?
Click to expand...

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?
Click to expand...

That is a UI design issue. Not one of my strong points.
Thank you greatly for your help.
Click to expand...
You really should think out your design. Do you really need the estimate
detail table if you use my suggestion ?

Good luck!

Evan
 
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 said:
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.
Click to expand...
 
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 said:
----- Original Message -----
From: "Mara" <[email protected]>
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?
Click to expand...

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?
Click to expand...

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?
Click to expand...

That is a UI design issue. Not one of my strong points.
Thank you greatly for your help.
Click to expand...
You really should think out your design. Do you really need the estimate
detail table if you use my suggestion ?

Good luck!

Evan
Click to expand...
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
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
Click to expand...
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top