displaying allowances available per day over a date range.

[abodi]

First let me say, this community has been very helpful anytime i've had a query and i'm very thankful.

Now usually I have a good idea whether something can be done, even if I don't know how. but i'm not sure if this is possible.
If it's complex i'm ok with that, any pointers you can provide would be amazing. if it's impossible, then I certainly want to hear that so i'm not wasting time and if impossible perhaps alternatives coudl be suggested.

Background:
I'm working on a leave database. There is a limit to the amount of people that can be on leave at any one time. That amount of leave available works on a percentage that changes weekly. It is captured in the follow table.

That percentage is applied to the number of staff working at the time. This figure also flucuates but not nearly as often and is one field that would be updated manually when required. Lets call that table tblSiteNumbers and the field NumberStaff.

The problem:

In order for people to easily see if leave is available we are looking at a query/report or a cluster of queries/reports that achieve an output something like this.



So then it needs to display daily, the amount of leave available based on a date range entered by the user. Individual dates are not saved, only the week commencing dates.

I've perhaps not provided enough information and if that is that case please let me know.

Any assistance/guidance would be greatly appreciated.
Abodi.

[June7]

Won't be easy - very complex. I expect will need lots of VBA code and maybe a temp table. Review this thread https://www.accessforums.net/databas...ses-18459.html for examples of report that displays daily data for a period when the records only give a start and/or end date. Another example database in https://www.accessforums.net/forms/g...tml#post239648

[abodi]

Thanks June7, i'll take a look at both those links.

[abodi]

Hi June7 and others,

Would it be any/much easier if the LeaveAvailability table was on a daily basis rather than weekly?

[June7]

Probably but the calculations still seem complicated.