TerryKoehn
New member
- Local time
- Today, 12:36
- Joined
- Oct 10, 2014
- Messages
- 2
- We have multiple Clients
- Each of those Clients have multiple Employees
- The Wage and Wage cost for the hours worked by the Employees for the Client are reimbursed by a 3rd party, up to certain limits.
- The limits are different for each client. Some clients have monthly limits, and others have weekly limits.
- Pay-Periods for all Employees are semi-monthly.
- We enter the sum of hours per day of each employee into a table in our system through a form. The form and underlying table has fields named [1], [2], [3].... through 31. In the fields we enter the number of hours worked each of the 31 days (Each employee would have two records per month. The first record in the month would fill days [1] through [15], and the next record in the month would fill fields [16] through [31]. upon the click of a command the form calculates fields [wk1total], [wk2total], [wk3total].... through [wk6total]. We added another field called [lastweektotal] because some months have 5 weeks and some have 6 weeks. so the later field holds same data as whatever week is the last week in that particular month.
- Now I need to take that data and be able to evaluate to ensure that the client has not exceeded the weekly limits of the 3rd party contract. So I've created a form with a subform. Form is [client] and subform is a datasheet based on a sum query. The datasheet gives sums for each week for each given month (wk 1 and the last week are generally always "part-weeks".
- As we are evaluating weeks to ensure they don't exceed the max. For weeks 2, 3, and 4 we have a customize format that highlights if the client exceeded the weekly limit. But we have to manually view add the [lastweektotal] from the record that represents January, and add it to the February record [wk1total] because both are "part-weeks" and so-on for each month. We do not get a "red-flag" if they exceed on weeks that split between months.
- I would like to be able to pull the [lastweektotal] from January and bring it into the February record and add it to [wk1total] then use the same customize format to highlight if it exceeds the Max allowed.
I am fairly comfortable with Access, and have some experience with VBA also. But mostly self-taught on the fly as needed. Our database is substantial and has grown over time as needed. I don't have time to start over with it's over-all structure at this time. But really do need to get over this hump.
I've attached an example of what I have so far.
Attachments
-
ContFormProblemWeeks.jpg
