The purpose of the database is to assist in an animal day care facility with Client/Animal information, booking and billing.
1. Record Client & Animal information. A single client to one or many animal relationship will exist. Accessc 2010 Tbls and Forms for these have already been “draft” created as such can be used as a guide.
2. Calendar booking form/ system to record bookings. Booking will be on a full day or half day duration. Booking will be made in the "animal" name and a "charge" should be assigned to the Client. Note: Maximum number of animals per day booking is in place but this should have the potential to be easily changed. As such once the daily limit is reached a warning should be given and no further bookings be allowed for that day. An example of the type of calendar view preferred can be supplied.
3. Billing - Clients will pay either upfront and purchase "bulk" passes or single day pass . Pass charges will vary and individual Pass charges apply depending on the type of pass purchased. E.g. Discount for new client for first 20 purchased then the rate varies upon the number purchased. As such bookings made in Animal name need to be charged backed to Client and these charges taken off the running “pass” credit they hold.
4. Staff rota linked to the days booking. A certain staff to animal ratio will exist as such dependent on the number of bookings for a day. Again the ratio may change so this should be easily amended. Staff rota should be booked at hourly intervals and as such a staff cost / billing report option would be required.
5. Queries / Reports required:
1. Client Information
2. Animal information
3. Booking – Option to report on overall booking from Day to Day / Month or just a specified from to period timeframe, or based on Client or Animal name over specified From / To period.
4. Client Billing – Option to report by Client “Pass” total as of specified date. “Pass” level within a specified from / to range (to allow follow up call to purchase more), Pass total no longer in credit. Reports should be able to run on Booking in system as of the date specified or to include all bookings even if in the future.
5. Staff – Report to produce the staff rota for a given specified From / to period. Plus Staff costs based on hours worked.
All reports should be printable.
6. I want the interface of the database to be simple, so anyone can easily Add/Update Client /Animal information. Make a booking and record pass credits and allocate these against bookings to provide statements to clients.
If the database could be set up with User id / Password controls with a potential for 2 levels of access. Client & Booking and Client, Booking and Staff.
This started out as an offer to create a simple Client/ Animal db to record information which at the time I said I would try and do myself. The requirements are now a little beyond the scope of my knowledge as such require the assistance of a more experienced Access developer. I have no idea if what I am after is possible and how complex or time consuming this would be.
I would welcome feedback on not only if you are willing to take on the project but if you could comment on timescales and complexity as if only certain items can be achieved within budget so be it.
Full details and copies of the Access tables and Forms that I have created are available.
The budget is tight as I want to present this database as a gift to my friend as such the db will be as good as I can be achieved with the money available.I am more than happy to have a working db created if such things as input forms and reports etc are basic layout as I should be able to take it back and work on those things myself. In essence I am stuck on the booking / Billing/Invoicing side of things. I am willing to wait 4 weeks for completion. And looking for the db to be in created in Microsoft access2010