Reservation Database

[barnstar]

I am currently working a annual leave reservation database & need help.



I want the user to complete a form selecting the date(s) &
time(s) of leave they require. The leave pot is in half hour slots for e.g.
8:00 - 8:30 & so on. There would be a % of leave given on each half hour
which is calculated against a staff in post figure. For each half hour slot there may be numerous spaces for leave so I would need each application to deduct off the total.

Once the user submits the form the db would automatically check the slots for
each half hourly slot for the time they requested off & return an immediate
response.

I would really appreciate any help with this.

Thanks in advance

[Rawb]

Can you give any more information on your problem? Are you having trouble with the Tables, Forms, Queries, Reports, or VBA code?

My crack at it though would go something like this:
Table: tblUsers

userID (long integer, auto increment, primary key)
user_name (text, 64 characters, index)
leave_available (long integer, not null, default 80)
leave_reset_date (short date)

You'll want to pre-populate the table with a list of all your users and the amount of leave time they have available. I'm running on the assumption that most of them will have 10 days (or 80 hours) available.

Table: tblReservations

reservation_date (short date)
reservation_time (fixed decimal, to 1 decimal place)
userID (long integer)

Each half-hour block will be it's own record, so you'll need to make a unique key with both the date and time.

Next make a Form that asks for the user and the date/times they want their leave. When they click the "Finish" Button, it will do the following checks:

(Leave already reserved) + (amount of time reserved) <= tblUsers.total_leave
All date/time blocks for leave not already filled

If all that evaluates to true, assign their leave and let the user know, otherwise pop up an error message.

I'd probably also make a couple of Queries/Reports. One that shows the reservations calendar style, and one that shows the amount of leave taken by user.

[barnstar]

Rawb

Thanks for your reply.

The part I am mostly stuck on is how I will query the date requested for the shift they are going to be working & if available remove a digit from the total.

I am beginning to think this can't even be done (although I know it must be possible).

Any help will be amazing

Barnstar

[Rawb]

You don't want to decrement the leave_available. That's a field that should always list the total amount of leave time they can use in a given year.

To tell if they have enough leave time remaining, just count the number of records in tblReservations and divide by two (since each record equals a half an hour).

The following Code assumes you have rstUser linked to tblUsers, rstReservations is linked to tblReservations, and you have a field called HoursRequested in your Form which calculates the total amount of leave the user is currently requesting.

strCriteria = "[user_name]='" & CurrentUser() & "'"

rstUsers.FindFirst strCriteria

If Not rstUsers.NoMatch Then
  nbrLeaveUsed = DCount("reservation_time", "tblReservations", "userID=''" & rstUsers("userID") & "'") ' doesn't matter which field you count!
  nbrLeaveUsed = nbrLeaveUsed / 2
Else
  nbrLeaveUsed = 0
End If

nbrLeaveUsed = nbrLeaveUsed + Me!HoursRequested

If nbrLeaveUsed >= rstUsers("leave_available") Then
  MsgBox "You've already used up all of your leave!"
  Exit Sub
End If

There's obviously a lot of coding that you'd still need to figure out (like taking into account the leave_reset_date field and actually adding records to tblReservations), but this should at least get you started.