Simple math

[BRIAN L via AccessMonster.com]

Hello All

Thanks in advance for help on my question. I'm new to Access.

I have some data I would like to enter daily ("units") corresponding to a
salesperson. I also have data (Hours worked) that I can only enter weekly,
also corresponding to a salesperson. How can I add up the daily units to
arrive at my ultimate answer: "units/hours worked"? Thanks again; let me
know if I need to share other info re: table design, etc.

Brian
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200510/1

[Chance]

If you want a more detailed answer, yes you will have to give much more
detail. But for now let me ask this: Where are you planning on
reading this "ultimate answer"?
1. On a form
2. On a report

3. (which is not an answer) is storing it in a table. Big no-no.

[Salad]

BRIAN L via AccessMonster.com wrote:

Hello All

Thanks in advance for help on my question. I'm new to Access.

I have some data I would like to enter daily ("units") corresponding to a
salesperson. I also have data (Hours worked) that I can only enter weekly,
also corresponding to a salesperson. How can I add up the daily units to
arrive at my ultimate answer: "units/hours worked"? Thanks again; let me
know if I need to share other info re: table design, etc.

Brian

You should have a from and to date range. If not, let's say you have a
week with the To Date = Friday, subtract 4 to get Monday, the beginning
of the week.

In a query (QUery/New/Design) add the table containing units. Drag down
the EmpID and the Units and SaleDate. Then you need to put the date
range for the criteria. Let's say the form is called MainForm. You
have 2 fields on that form called FromDate and ToDate. You could then etner

Between Forms!MainForm!FromDate And Forms!MainForm!ToDate

Now click on the menu bar for View/Totals. THere should be a GroupBy on
Employee, Sum for Units, and WHere for the SalesDate.

You can use Dlookup on this query to get the result. ANd then create a
calced field for the units/hours.

[BRIAN L via AccessMonster.com]

Thank you for your suggestions! I'm not sure how to make it all work, though.
My form ("Daily Billed Units") has fields for Employee ID, Date, Billed Units.
For FromDate and ToDate, would someone have to enter this data? Where would
this range be stored?

Salad wrote:

Hello All

[quoted text clipped - 7 lines]

Brian

You should have a from and to date range. If not, let's say you have a
week with the To Date = Friday, subtract 4 to get Monday, the beginning
of the week.

In a query (QUery/New/Design) add the table containing units. Drag down
the EmpID and the Units and SaleDate. Then you need to put the date
range for the criteria. Let's say the form is called MainForm. You
have 2 fields on that form called FromDate and ToDate. You could then etner

Between Forms!MainForm!FromDate And Forms!MainForm!ToDate

Now click on the menu bar for View/Totals. THere should be a GroupBy on
Employee, Sum for Units, and WHere for the SalesDate.

You can use Dlookup on this query to get the result. ANd then create a
calced field for the units/hours.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200510/1

[BRIAN L via AccessMonster.com]

On a report so I can track "Billed Units/Hour Worked" over time. What other
details are needed?

I now have a table and form for:
1. Employee Hours Worked, inc fields for Emp ID, Week Ending Date, Hours
2. Daily Billed Units, inc fields for Emp ID, Date, Billed Units
3. Employee first and last name, auto# ID

Chance wrote:

If you want a more detailed answer, yes you will have to give much more
detail. But for now let me ask this: Where are you planning on
reading this "ultimate answer"?
1. On a form
2. On a report

3. (which is not an answer) is storing it in a table. Big no-no.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200510/1

[Chance]

Try this:

1. Create the report.
2. Put the 2 fields that need to be included in the calculation on the
report.
3. Run the report and view the 2 fields.
4. Go back into design view
5. Click on one of the fields and copy/paste it.
6. Double-click on the new field
7. Your properties should be brought up for that object.
8. Name it
9. In the control source there is a little box all the way to the
right. It has 3 dots in it. Click on that button. Now you can build
a for a formula. Play around with the 3 boxes on the bottom.

For example. Let's say you want to add txtBox1 to txtBox2. Well, we
are lazy and don't want to memorize code, so we use this builder
function. We get to the properties of txtBox3 (as described above) and
we are now in the window in step 9. Double-click on "Reports". Find
the report we have open and double-click on it. Then find txtBox1 (in
the second box) and double-click it . The object is added to the
Expression box on the top. Now type +. Then find txtBox2 and
double-click it. Now they are added together.

Get it?

[Salad]

BRIAN L via AccessMonster.com wrote:

Thank you for your suggestions! I'm not sure how to make it all work, though.
My form ("Daily Billed Units") has fields for Employee ID, Date, Billed Units.
For FromDate and ToDate, would someone have to enter this data? Where would
this range be stored?
No, not really. But you should have the week ending date. If you have
the week ending, what is the week start? How many days do you need to
subtract to get the start date from the end date? If you can figure out
what the starting day is, you have a range to select from. See
DateAdd() function to determine how to add/subtract dates.

Next, read something in help in learning how to create a Totals query.
Either that, look at Dsum(), Dcount().


Salad wrote:

Hello All

[quoted text clipped - 7 lines]

Brian

You should have a from and to date range. If not, let's say you have a
week with the To Date = Friday, subtract 4 to get Monday, the beginning
of the week.

In a query (QUery/New/Design) add the table containing units. Drag down
the EmpID and the Units and SaleDate. Then you need to put the date
range for the criteria. Let's say the form is called MainForm. You
have 2 fields on that form called FromDate and ToDate. You could then etner

Between Forms!MainForm!FromDate And Forms!MainForm!ToDate

Now click on the menu bar for View/Totals. THere should be a GroupBy on
Employee, Sum for Units, and WHere for the SalesDate.

You can use Dlookup on this query to get the result. ANd then create a
calced field for the units/hours.