help on expression for MS Access

[Helponaccess]

Hi All,



I've created a database to track and manage our leave application. I'm just a beginner and have limited knowledge in expression.
I have some problems that I'm unable to resolve and hope to receive some help from this forum.
The following are all the problems:

1) I need to have a formula that restrict the approver from approving someone for leave when he/she does not have sufficient leave. See Lucy's sample, the leave status is (-) which shouldn't be allowed.
2) Is there a way I can link up the leave available balance into the Leave application form so that the reviewer has an opportunity to review before deciding to approve or decline?
3) I need a report that display in calendar format and can do the total count of how many person will be on leave on the same time. Is this possible in access?

All advice will be very much appreciated and thanks in advance.
Hope someone can help me. Thanks

[orange]

Here is a data model showing the tables and relationships typically found in a employee leave application.

[Helponaccess]

Thanks Orange. I’ve managed to build the database. But need help on the expression that link to other query

[orange]

Hmmm? A few things before calculations.

Most on the forum will tell you to adopt a naming convention that does not allow embedded spaces. Spaces in names will come back to haunt you (syntax errors).
I don't follow your table names( eg ...Listing).
It seems there are:

Employees
LeaveEntitlement
LeaveTaken
LeaveType could include annual, training, FamilyRelated, Bereavement,medical short/long term...
LeaveRequest

For a given LeaveType an Employee has an Entitlement in a TimePeriod.(your org may be different)
For an EmployeeLeaveRequest you determine the Entitlement - LeaveUsed, and the LeaveRequest Amount and Type. If the current balance of Leave Amount for the requested type is greater or equal to the current request (barring other factors --others already of leave.....not enough staff available....), then the Request would be approved. If the current balance is less than the requested amount, then the request would be denied--with a proviso that leave up to the current balance for the leave type could be considered.

A lot of this is based on the rules of your business. And you or others know the business so should be able to design a logic diagram (a series of IF statements involving Employee, Entitlement, LeaveUsed, LeaveRequested, StaffRequirements.... leading to an Approve/Deny result).

Good luck with your project.

Update: Just notice this is cross posted
@ http://www.utteraccess.com/forum/ind...ic=2048148&hl=

When you cross post, tell the forum that you have done so. Include a link to the other post to prevent readers, who are all volunteers, from wasting time trying to solve an already solved issue.

Read this link for more info on why and how to cross post.