Simple Sum

[wesley.allen]

I am a database beginner, so I am not very familiar with using code. I
try to stick mainly with the simple setup and building or tables and
queries.

However, I have a table that will be made up of employee hours worked.
I want to do several things with a query of this table.

1. I want to sum the hours of each level of employee to come up with a
total.
Staff, Manager, Executive.

2. I want to try to add in a formula that will calculate the billings
by each hourly rate level and give me a blended rate per hour.

Does anyone know how to do this without a great deal of SQL that I will
likely mess up?

Thanks.

 

[Guest]

First some questions so we can lay this query out correctly.
What is the name of the field where you carry the employee's level?

Is the hourly rate the same for all employees in the same level or do the
rates vary by employee?

How do you know how many hours an employee worked? Is it a field in the
table or will it have to be calculated? If it is calculated, please provide
the names of the fields and the formula for the calculation.

Are the sums to be by Level or by Rate or by Level and Rate or by Rate and
Level?

Please post all the field names involved.

 

[wesley.allen]

Thanks for helping.

Here is how the table is laid out.

I have a Job Year and Job as primary keys. We track hours per job,
which occur each at the same times each year. Therefore, the fields of
this table are:

Year
Job
Budgeted Staff Hours
Budget Manager Hours
Budget Executive Hours
Actual Staff Hours
Actual Manager Hours
Actual Executive Hours
Fees
Expenses
Total Bill Rate

All above fields are input manually.

I want to Total the Budgeted Hours, Total the Actual Hours, then
calculate Blended Bill rate. Blended bill rate comes from Fees minus
Expenses, divided by Total actual hours.

If possible, I would like to compute the discount, which comes from
this formula:

1-((Fees-Expenses)/Total Bill Rate)

Thanks again for any help you can provide.

 

[Guest]

I think we can do this with a Totals query.
Here is the Total Identifier for each column:
Year - Group By
Job - Group By
Budgeted Staff Hours - Sum
Budget Manager Hours - Sum
Budget Executive Hours - Sum
Actual Staff Hours - Sum
Actual Manager Hours - Sum
Actual Executive Hours - Sum
Fees - Sum
Expenses - Sum
Total Bill Rate - Sum
Total Budget: [Budgeted Staff Hours] + [Budgeted Manager Hours] + [Budgeted
Executive Hours] - Expression
Total Actual: [Actual Staff Hours] + [Actual Manager Hours] + [Actual
Executive Hours] - Expression
Blended Rate: ([Fees] - [Expenses]) / [Total Actual] - Expression
Discount: 1-(([Fees] - [Expenses]) / [Total Bill Rate]) - Expression

In some cases where I say Expression, it may have to be Sum. Sometimes I
have to play with the query builder until I get it right, but the above is
the main idea for this.

 

[wesley.allen]

I'm sorry. How do I create a totals query?

Thanks.

I think we can do this with a Totals query.
Here is the Total Identifier for each column:
Year - Group By
Job - Group By
Budgeted Staff Hours - Sum
Budget Manager Hours - Sum
Budget Executive Hours - Sum
Actual Staff Hours - Sum
Actual Manager Hours - Sum
Actual Executive Hours - Sum
Fees - Sum
Expenses - Sum
Total Bill Rate - Sum
Total Budget: [Budgeted Staff Hours] + [Budgeted Manager Hours] + [Budgeted
Executive Hours] - Expression
Total Actual: [Actual Staff Hours] + [Actual Manager Hours] + [Actual
Executive Hours] - Expression
Blended Rate: ([Fees] - [Expenses]) / [Total Actual] - Expression
Discount: 1-(([Fees] - [Expenses]) / [Total Bill Rate]) - Expression

In some cases where I say Expression, it may have to be Sum. Sometimes I
have to play with the query builder until I get it right, but the above is
the main idea for this.

Thanks for helping.

Here is how the table is laid out.

I have a Job Year and Job as primary keys. We track hours per job,
which occur each at the same times each year. Therefore, the fields of
this table are:

Year
Job
Budgeted Staff Hours
Budget Manager Hours
Budget Executive Hours
Actual Staff Hours
Actual Manager Hours
Actual Executive Hours
Fees
Expenses
Total Bill Rate

All above fields are input manually.

I want to Total the Budgeted Hours, Total the Actual Hours, then
calculate Blended Bill rate. Blended bill rate comes from Fees minus
Expenses, divided by Total actual hours.

If possible, I would like to compute the discount, which comes from
this formula:

1-((Fees-Expenses)/Total Bill Rate)

Thanks again for any help you can provide.

 

[Guest]

In the query builder, on the tool bar you will see a symbol that looks like
the Greek letter Gamma ( I think), any way, some Greek letter. Point at it,
it should say Totals in tool tips.
Click it.

I'm sorry. How do I create a totals query?

Thanks.

I think we can do this with a Totals query.
Here is the Total Identifier for each column:
Year - Group By
Job - Group By
Budgeted Staff Hours - Sum
Budget Manager Hours - Sum
Budget Executive Hours - Sum
Actual Staff Hours - Sum
Actual Manager Hours - Sum
Actual Executive Hours - Sum
Fees - Sum
Expenses - Sum
Total Bill Rate - Sum
Total Budget: [Budgeted Staff Hours] + [Budgeted Manager Hours] + [Budgeted
Executive Hours] - Expression
Total Actual: [Actual Staff Hours] + [Actual Manager Hours] + [Actual
Executive Hours] - Expression
Blended Rate: ([Fees] - [Expenses]) / [Total Actual] - Expression
Discount: 1-(([Fees] - [Expenses]) / [Total Bill Rate]) - Expression

In some cases where I say Expression, it may have to be Sum. Sometimes I
have to play with the query builder until I get it right, but the above is
the main idea for this.

Thanks for helping.

Here is how the table is laid out.

I have a Job Year and Job as primary keys. We track hours per job,
which occur each at the same times each year. Therefore, the fields of
this table are:

Year
Job
Budgeted Staff Hours
Budget Manager Hours
Budget Executive Hours
Actual Staff Hours
Actual Manager Hours
Actual Executive Hours
Fees
Expenses
Total Bill Rate

All above fields are input manually.

I want to Total the Budgeted Hours, Total the Actual Hours, then
calculate Blended Bill rate. Blended bill rate comes from Fees minus
Expenses, divided by Total actual hours.

If possible, I would like to compute the discount, which comes from
this formula:

1-((Fees-Expenses)/Total Bill Rate)

Thanks again for any help you can provide.



Klatuu wrote:
First some questions so we can lay this query out correctly.
What is the name of the field where you carry the employee's level?

Is the hourly rate the same for all employees in the same level or do the
rates vary by employee?

How do you know how many hours an employee worked? Is it a field in the
table or will it have to be calculated? If it is calculated, please provide
the names of the fields and the formula for the calculation.

Are the sums to be by Level or by Rate or by Level and Rate or by Rate and
Level?

Please post all the field names involved.

:

I am a database beginner, so I am not very familiar with using code. I
try to stick mainly with the simple setup and building or tables and
queries.

However, I have a table that will be made up of employee hours worked.
I want to do several things with a query of this table.

1. I want to sum the hours of each level of employee to come up with a
total.
Staff, Manager, Executive.

2. I want to try to add in a formula that will calculate the billings
by each hourly rate level and give me a blended rate per hour.

Does anyone know how to do this without a great deal of SQL that I will
likely mess up?

Thanks.

 

[wesley.allen]

Thanks.

I assume I needed to add new fields to my table so I could input the
formulas for Total Budget, Total Actual, etc. I did this, changed all
columns to Sum, then added this to the criteria of Total Budgeted Hours

[ProfitabilityMaster].[Total Budgeted
Hours]=[ProfitabilityMaster].[Budgeted Staff Hours]+[Budgeted Manager
Hours]+[Budgeted Executive Hours]-"Sum"

I also tried entering Expression in place of Sum.

When I try to run the query, I get the following Error.

"You tried to execute a query that does not include the specified
expression '[ProfitabilityMaster].[Total Budgeted
Hours]=[ProfitabilityMaster].[Budgeted Staff Hours]+[Budgeted Manager
Hours]+[Budgeted Executive Hours]-"Sum" as part of an aggregate
function.

Any idea what I have done wrong?

Thanks for being patient with me.

I think we can do this with a Totals query.
Here is the Total Identifier for each column:
Year - Group By
Job - Group By
Budgeted Staff Hours - Sum
Budget Manager Hours - Sum
Budget Executive Hours - Sum
Actual Staff Hours - Sum
Actual Manager Hours - Sum
Actual Executive Hours - Sum
Fees - Sum
Expenses - Sum
Total Bill Rate - Sum
Total Budget: [Budgeted Staff Hours] + [Budgeted Manager Hours] + [Budgeted
Executive Hours] - Expression
Total Actual: [Actual Staff Hours] + [Actual Manager Hours] + [Actual
Executive Hours] - Expression
Blended Rate: ([Fees] - [Expenses]) / [Total Actual] - Expression
Discount: 1-(([Fees] - [Expenses]) / [Total Bill Rate]) - Expression

In some cases where I say Expression, it may have to be Sum. Sometimes I
have to play with the query builder until I get it right, but the above is
the main idea for this.

Thanks for helping.

Here is how the table is laid out.

I have a Job Year and Job as primary keys. We track hours per job,
which occur each at the same times each year. Therefore, the fields of
this table are:

Year
Job
Budgeted Staff Hours
Budget Manager Hours
Budget Executive Hours
Actual Staff Hours
Actual Manager Hours
Actual Executive Hours
Fees
Expenses
Total Bill Rate

All above fields are input manually.

I want to Total the Budgeted Hours, Total the Actual Hours, then
calculate Blended Bill rate. Blended bill rate comes from Fees minus
Expenses, divided by Total actual hours.

If possible, I would like to compute the discount, which comes from
this formula:

1-((Fees-Expenses)/Total Bill Rate)

Thanks again for any help you can provide.

 

[Guest]

You don't need to add fields to your table. In fact, you should not because
these are calculated values which should not be in tables. In a query, you
can create an "alias" field that is used for calculations. That is what
columns like:
Total Budget: [Budgeted Staff Hours] + [Budgeted Manager Hours] + [Budgeted
Executive Hours] - Expression
are for. the : tells the query builder that Total Budget will be the field
name and it will be made up of the formula that follows.
What you need to do to correct the error you got is to put the Total
functions is as I described in my previous post. In a total query you will
see a row the says Total. There are serveral function you can select from in
a drop down for each column. Use the ones I suggested.

Thanks.

I assume I needed to add new fields to my table so I could input the
formulas for Total Budget, Total Actual, etc. I did this, changed all
columns to Sum, then added this to the criteria of Total Budgeted Hours

[ProfitabilityMaster].[Total Budgeted
Hours]=[ProfitabilityMaster].[Budgeted Staff Hours]+[Budgeted Manager
Hours]+[Budgeted Executive Hours]-"Sum"

I also tried entering Expression in place of Sum.

When I try to run the query, I get the following Error.

"You tried to execute a query that does not include the specified
expression '[ProfitabilityMaster].[Total Budgeted
Hours]=[ProfitabilityMaster].[Budgeted Staff Hours]+[Budgeted Manager
Hours]+[Budgeted Executive Hours]-"Sum" as part of an aggregate
function.

Any idea what I have done wrong?

Thanks for being patient with me.

I think we can do this with a Totals query.
Here is the Total Identifier for each column:
Year - Group By
Job - Group By
Budgeted Staff Hours - Sum
Budget Manager Hours - Sum
Budget Executive Hours - Sum
Actual Staff Hours - Sum
Actual Manager Hours - Sum
Actual Executive Hours - Sum
Fees - Sum
Expenses - Sum
Total Bill Rate - Sum
Total Budget: [Budgeted Staff Hours] + [Budgeted Manager Hours] + [Budgeted
Executive Hours] - Expression
Total Actual: [Actual Staff Hours] + [Actual Manager Hours] + [Actual
Executive Hours] - Expression
Blended Rate: ([Fees] - [Expenses]) / [Total Actual] - Expression
Discount: 1-(([Fees] - [Expenses]) / [Total Bill Rate]) - Expression

In some cases where I say Expression, it may have to be Sum. Sometimes I
have to play with the query builder until I get it right, but the above is
the main idea for this.

Thanks for helping.

Here is how the table is laid out.

I have a Job Year and Job as primary keys. We track hours per job,
which occur each at the same times each year. Therefore, the fields of
this table are:

Year
Job
Budgeted Staff Hours
Budget Manager Hours
Budget Executive Hours
Actual Staff Hours
Actual Manager Hours
Actual Executive Hours
Fees
Expenses
Total Bill Rate

All above fields are input manually.

I want to Total the Budgeted Hours, Total the Actual Hours, then
calculate Blended Bill rate. Blended bill rate comes from Fees minus
Expenses, divided by Total actual hours.

If possible, I would like to compute the discount, which comes from
this formula:

1-((Fees-Expenses)/Total Bill Rate)

Thanks again for any help you can provide.



Klatuu wrote:
First some questions so we can lay this query out correctly.
What is the name of the field where you carry the employee's level?

Is the hourly rate the same for all employees in the same level or do the
rates vary by employee?

How do you know how many hours an employee worked? Is it a field in the
table or will it have to be calculated? If it is calculated, please provide
the names of the fields and the formula for the calculation.

Are the sums to be by Level or by Rate or by Level and Rate or by Rate and
Level?

Please post all the field names involved.

:

I am a database beginner, so I am not very familiar with using code. I
try to stick mainly with the simple setup and building or tables and
queries.

However, I have a table that will be made up of employee hours worked.
I want to do several things with a query of this table.

1. I want to sum the hours of each level of employee to come up with a
total.
Staff, Manager, Executive.

2. I want to try to add in a formula that will calculate the billings
by each hourly rate level and give me a blended rate per hour.

Does anyone know how to do this without a great deal of SQL that I will
likely mess up?

Thanks.

 

[wesley.allen]

Got it.

Thank you very much.

You don't need to add fields to your table. In fact, you should not because
these are calculated values which should not be in tables. In a query, you
can create an "alias" field that is used for calculations. That is what
columns like:
Total Budget: [Budgeted Staff Hours] + [Budgeted Manager Hours] + [Budgeted
Executive Hours] - Expression
are for. the : tells the query builder that Total Budget will be the field
name and it will be made up of the formula that follows.
What you need to do to correct the error you got is to put the Total
functions is as I described in my previous post. In a total query you will
see a row the says Total. There are serveral function you can select from in
a drop down for each column. Use the ones I suggested.

Thanks.

I assume I needed to add new fields to my table so I could input the
formulas for Total Budget, Total Actual, etc. I did this, changed all
columns to Sum, then added this to the criteria of Total Budgeted Hours

[ProfitabilityMaster].[Total Budgeted
Hours]=[ProfitabilityMaster].[Budgeted Staff Hours]+[Budgeted Manager
Hours]+[Budgeted Executive Hours]-"Sum"

I also tried entering Expression in place of Sum.

When I try to run the query, I get the following Error.

"You tried to execute a query that does not include the specified
expression '[ProfitabilityMaster].[Total Budgeted
Hours]=[ProfitabilityMaster].[Budgeted Staff Hours]+[Budgeted Manager
Hours]+[Budgeted Executive Hours]-"Sum" as part of an aggregate
function.

Any idea what I have done wrong?

Thanks for being patient with me.

I think we can do this with a Totals query.
Here is the Total Identifier for each column:
Year - Group By
Job - Group By
Budgeted Staff Hours - Sum
Budget Manager Hours - Sum
Budget Executive Hours - Sum
Actual Staff Hours - Sum
Actual Manager Hours - Sum
Actual Executive Hours - Sum
Fees - Sum
Expenses - Sum
Total Bill Rate - Sum
Total Budget: [Budgeted Staff Hours] + [Budgeted Manager Hours] + [Budgeted
Executive Hours] - Expression
Total Actual: [Actual Staff Hours] + [Actual Manager Hours] + [Actual
Executive Hours] - Expression
Blended Rate: ([Fees] - [Expenses]) / [Total Actual] - Expression
Discount: 1-(([Fees] - [Expenses]) / [Total Bill Rate]) - Expression

In some cases where I say Expression, it may have to be Sum. Sometimes I
have to play with the query builder until I get it right, but the above is
the main idea for this.

:

Thanks for helping.

Here is how the table is laid out.

I have a Job Year and Job as primary keys. We track hours per job,
which occur each at the same times each year. Therefore, the fields of
this table are:

Year
Job
Budgeted Staff Hours
Budget Manager Hours
Budget Executive Hours
Actual Staff Hours
Actual Manager Hours
Actual Executive Hours
Fees
Expenses
Total Bill Rate

All above fields are input manually.

I want to Total the Budgeted Hours, Total the Actual Hours, then
calculate Blended Bill rate. Blended bill rate comes from Fees minus
Expenses, divided by Total actual hours.

If possible, I would like to compute the discount, which comes from
this formula:

1-((Fees-Expenses)/Total Bill Rate)

Thanks again for any help you can provide.



Klatuu wrote:
First some questions so we can lay this query out correctly.
What is the name of the field where you carry the employee's level?

Is the hourly rate the same for all employees in the same level or do the
rates vary by employee?

How do you know how many hours an employee worked? Is it a field in the
table or will it have to be calculated? If it is calculated, please provide
the names of the fields and the formula for the calculation.

Are the sums to be by Level or by Rate or by Level and Rate or by Rate and
Level?

Please post all the field names involved.

:

I am a database beginner, so I am not very familiar with using code. I
try to stick mainly with the simple setup and building or tables and
queries.

However, I have a table that will be made up of employee hours worked.
I want to do several things with a query of this table.

1. I want to sum the hours of each level of employee to come up with a
total.
Staff, Manager, Executive.

2. I want to try to add in a formula that will calculate the billings
by each hourly rate level and give me a blended rate per hour.

Does anyone know how to do this without a great deal of SQL that I will
likely mess up?

Thanks.