How to calculate field

[bquan]

Hi everyone,
I have a database that is looking at employee productivity. Some of the fields are: month of the year, billed productivity (in percentage), etc.
My boss wants me to do a calculation to find the difference in percent from last month and this month.
So, he wants (March's billed productivity)-(February's billed productivity)
Now, when I run a query and a report I have:

employee name Billed Producivity
joe smith 59%

and to do this I have specified in my query not to show <>01/02 and <>02/02

Ok, so I am stuck in separating the billed productivity for each month and subtracting one from the other

so that I can have:

employee name billed productivity % change
joe smith +3%
jane smith -10%

this is the change compared to last month.

I hope this makes sense and someone can give me a hint on how to create this query so that I can do a report.

Thank you in advance!

 

[David R]

Is this data stored by month or by job and needs to be averaged for the month?

If it's already stored by the month, you should be able to use DLookup to get the two values and subtract them.
You could also build a query to gather last month's data, then build a query to bring that data together with the month previous and subtract them. I believe that would work faster from previous discussions, especially if you have a lot of records to cover.

If it's done the other way, I have a solution but I want to know you need it first as it's rather more complex.

HTH,
David R

 

[bquan]

Thank you for your reply!
I do store it by month. It is stored by month and by employee number (those are my primary keys)
Actually, thinking about it, I am not even sure if I have stored it by month...the way I look it up is by either "billed time" or "non billed time" of each employee and then by the month that each is stored by. I hope this makes sense.

So, what you are saying is that I should build a query that looks at february data and a separate query that looks at march data and a third query that subtracts one from the other?
I am pretty new at this and I am not sure exactly how to subtract one query from another.
Also, where do I find DLookup if I decide to do it that way?

Thanks again!



[This message has been edited by bquan (edited 04-11-2002).]

 

[David R]

I did it with three queries. The first two have Totals turned on (sigma button):
queryLastMonth: (based on your table)
[EmployeeNumber], Group By
[MonthYearField], Group By, Criteria: Format(DateAdd("m",-1,Date()),"mm/yy")
[Productivity], Sum

queryMonthBeforeLast:
same three fields, except the criteria is -2, not -1

queryMonthlyDifference: (based on the other two queries)
[EmployeeNumber] (from queryLastMonth)
LastMonth: [SumofProductivity] (from queryLastMonth)
PreviousMonth: [SumofProductivity] (from queryMonthBeforeLast)
Diff: [LastMonth]-[PreviousMonth]

I had to draw a line between the two [EmployeeNumber] fields in my query design and change Join Properties to include all records from queryLastMonth and only those...etc.

Run your query and see what you get.

Good luck,
David R

 

[bquan]

Thank you so much David,
I have not tried it yet and won't have time to for a few days. Unfortunately I will have to postpone this project until after my vacation and a couple of hectic days.
I appreciate your help so much!
I have saved your instructions and will try it as soon as I come back.
If it does not work or I run into trouble I hope to reply later.
Thanks again!!