Previous month's totals. 1

[MICKI0220]

I have a table set up that after a query has been run it totals up a person's bonus. Sometimes there is a negative bonus which must be deducted in the next month's bonus or added to if the person still recieve's a negative balance.
The table's field's are as follows

employee name
employee id
Month
bonus
negative carryover

How to do I get my report to calculate the previous month's totals and plug it in the table as the new negative carryover if they do have < 0 ?

 

[BSman]

You should run two queries. The first should look at any records for the previous month where there is a value in the negative bonus column and, if there is one, update the new month's negative bonus column.

Your second query would then calculate the bonus as the total of the new month's bonus less any figure in the negative bonus column (remember to use the nz() function to convert a null in the negative bonus column to 0) and post the result in either the new month's bonus column or the new month's negative bonus column (overwriting the value that you posted from the previous month.

Bob

 

[MICKI0220]

That is great, however I still am confused on what the syntax would be in my query to get the previous month's negative value. The fields are [month] and [negative carryover]...so if I was doing it today for April I would take the sum([allocated bonus]) - previous month's([negative carryover])...how to you code it in a query to look at this month being what ever it is and take the past month?

 

[BSman]

Use the DateAdd function specifying month with a -1 value to get the date of the previous month for your DLookup function that will look up the previous month's negative value. You make a query that looks up the previous month's negative value and then link that query with the query for the current month (link using your employee's number, for example).

Bob