Data Calculations

[aaronbrowne]

Hi Guys,

I have seached and searched and still no anwser. I am trying to set up a leave tracking system within access. I would like to be able to keep track of how much leave one is allowed and sick leave and so on. At present my main form is a search form that will display the results in a sub form. I then click on one of the results and use a cmd button to display full details of a staff member. I then click on annual leave to add leave that has been taking by that member of staff. I would like to have this form do Calculations on the dates to see how much leave was taking and how much leave is left. I can do Calculations but I get a bit stuck when thinking of the design of it because of the fact that leave will be newed on a yearly basis.

Please help and advise me.

Regards

Aaron

 

[KeithWilliams]

Hi Aaron,

Here's one approach to consider.

Create a Leave table:
EmployeeId (Integer)
FirstDayOfLeave (Date)
LastDayOfLeave (Date)

Create a form to allow the user to add, delete and amend records.

Assuming the employees' leave entitlement is for a calendar year, you can write a global function (in a Module) to calculate the number of days leave taken in a given date range.

Function gf_WorkDays(dateStart as Date, dateEnd as Date, YearNum as Integer) as Integer.

In this function, you will need to tot up the number of working days (where Weekday(date) <> vbSunday and <> vbSaturday) between the two dates. You will need to take account of public holidays - you will probably need to create a system table of public holidays and populate that with public holidays observed by your employer. Public holidays falling between the start and end date would not count as a day of leave.

The YearNum allows you to specify which leave year you want to count leave for, and needs to be passed as an argument for leave periods that start in one year and finish in the next. Your function should only count days in the range that fall within that year.

Then you can create a query that returns the Sum of gf_workdays() for all the Leave records for an employee.

Hope that helps,
Keith.

 

[aaronbrowne]

Thanks for the reply but I am starting to cry at how something so simple turns out to be so complex. Would you do me a big favour and download the database that I am working on. Basicly I am tring to amend something that the fourm help me create about two years ago. the downlaod links is as follows www.disability-federation.ie/dfistaffdb.mdb. I would really appreciate it if you could take alook a tell me where I could improve.

I will start to try and implement your idea.

Thanks again.

Regards

Aaron

 

[KeithWilliams]

Hi Aaron,

Please don't get disheartened, you are just coming up against some design challenges, and if you work through it systematically rather than getting overwhelmed with the details, you will find its not only possible, but satisfying, and hey, maybe fun!

I ran your database but unfortunately when I shut it down it remained locked (with an .ldb file) and I can't re-open it, which makes me wary of exploring further. Looking at the database design, you have opted to store leave records in one-week blocks, with a boolean (Yes/No) value for each day of the week (Tuesday-Friday?). This is a perfectly legitimate design choice, especially if it reflects the way leave is typically taken in your organization. So don't go back to the drawing board unless you decide that another design will suit you better. You have included the first name and surname of the employee in the leave table, which is poor design. I'd recommend you read up on normalization.

If you have time, you would really benefit from reading a good book on database design, or an Access book that covers the topic. "Running Microsoft Access 2000" by Microsoft Press does this IIRC, but not terribly well. Can anyone else recommend a really good introductory textbook on Access and / or database design?

Going back to your original question. Given the design of your database, you could get the number of days leave in a record by totalling the number of ticks (True values) in Tuesday-Friday. For each record, you could add an expression to a query like "IIf(Tuesday,1,0) + IIf(Wednesday,1,0) + IIf(Thursday,1,0) + IIf(Friday,1,0)", which would equal 4 if all 4 checkboxes were ticked.

You can then extend this to "Sum(IIf(Tuesday,1,0) + IIf(Wednesday,1,0) + IIf(Thursday,1,0) + IIf(Friday,1,0))" in a query to get the total for an employee.

Then it gets fiddly, because you need to limit your query to those records that fall within the year you want to get leave totals for. If you can, keep it simple, let the leave be calculated for all the weeks whose date falls within the year. If someone else is making the rules, you will need to fudge the above formula to allow for weeks that straddle the year-end. I don't want to make it complex so I won't go into this! See how you get on with the above first.

Keith.

 

[aaronbrowne]

Hi Kieth,

Are you a teacher. Will if not you would make a good one. I am poorly lazy when it comes to programming or designing an app. I am network supporting server, Lan, WANS, office and the likes. So I find it very hard to speed time on designing database but hey you have spark an intrested that could turn out a benifit. So nice one. I am from Ireland.

Regards

Aaron

 

[KeithWilliams]

Hi Aaron,

No, I'm not a teacher, but thanks for the compliment! I'm quite new to Access but I've been in IT for a long time so I've been through the learning curve myself. I enjoy developing software, maybe you will too if you get the chance. If you are responsible for developing or maintaining software systems, maybe you could ask your boss to let you spend time on self-study or even go on some training courses.

Good luck,
Keith.

 

[yukoyu84]

checkboxes in microsoft access

halo..can somebody help me..on microsoft access 2000...i have difficulties in set the checkboxes so that it can be checked more than one checkboxes..iin the same form...so just like for example...when we fill the form..then in the form, it is asked to check the checkbox..and ofcourse...the checkboxes can be checked more than one..right..but i can not make the checkboxes to be checked more than one..and even when i checked the checkboxes..then it checks all the checkboxes...oh ya..my checkbox's value is all same mean that if it is checked then the value return is 1 otherwise 0.

is it possible because of all the checkbox's value are the same..so when we checked one then all checkboxes will be checked??

thanks...