Advice Needed for Planning

[Lori]

I am in the process of creating a new database to assist with the billing on
a maintenance project. this project will consist of dozens of different
subjobs that will be willed separately. The following is the base of this
database:

1. Each subjob will be treated as an individual project with its own
separate budget, separate timesheets etc.
2. No subjob will be allowed to go over budget.
3. Each subjob will have a specific start and end date.
4. Each subjob will have subcontractors working on it with specific contract
values so the balance of budget - subcontract value will be the budget
available for our employees to bill their time.
5. Timesheets will be generated over multiple subjobs but a query is being
used to separate out the costs for each individual bill.

Okay so here's my concerns:

1. If a project closes on 11/15, I need to allow users to bill their hours
for a specific project at anytime up until a subjob is invoiced BUT I do not
want them to be able to bill time after the closed date so in this case if a
project closes on 11/15, they can bill for time worked on 11/15 (but not
11/16) however the can enter the time into their timesheet until the Yes/No
Box is check to indicate that the subjob is "closed".

2. Since the subjobs are not allowed to go over budget help is desperately
needed to create a query that I can link to the timesheet forms (already
completed) that will calculate the value of the time they are trying to bill
(hrs * rate) and either allow them to bill the time (if the value they are
trying to bill keeps the subjob within the budget) or tells them to bill
their time elsewhere if their time would exceed the budget.

Any and all help would be greatly appreciated.

 

[tkelley via AccessMonster.com]

Hi Lori ... I'm not smart enough to articulate a good answer via a posting.
A couple of hours face-to-face with a whiteboard would be better. But one
thing I always do in the design phase is to flowchart any complex process.
If the flowchart is thorough and doesn't loop or break, then you can write
code that won't either while making sure each case is covered. Good luck.