how to display and calculate totals of sick days and vacation time left

davetough
davetough used Ask the Experts™
on
Hello,
I am hoping someone might be able to give me a few thoughts about how to even start thinking about incorporating some things into my database.
I have a table, form, and report for each person. I am using user level security. so each person has own login.
the form is a time sheet that they calculate their hours and submit data weekly.
my problem:
I have been given how many hours each person has total left this year for sick time and vacation time. I need to someway in this form or creating another- display to the user how much time he has left each week.
any ideas to get started thinking about this?
having it automatically calculated
thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi... what you're talking about doesn't sound hard, but I need a little more info to offer a suggestion.
A couple of questions for you:
- when you say 'a form for each person' - do you really mean a separate form object for each person, or a single form on which the user enters a user ID and selects his/her records

 - regarding "display to the user how much time he has left", do you want that to recalculate based on any sick or vacation time included in the current time sheet?

 - what version of Access are you using?

 - Can you upload a sample of your data with a few dozen records, so we can be more specific as we talk about this?

Author

Commented:
yes when I say each person I mean each person has a form  and report and table.
User logs in and and only has access to his form and report. I use a union in sql statement to join all tables together and produce report.
the form is at work = so I will describe:
It has unbound textboxes to add data together into total boxes at bottom.
for the sake of example
I have fields idNumber, Name, Date, regularTimeTotal, VacationTimeTotal, SickTimeTotal.

I am using Access 2003- with user level security.
I have an excel sheet now with how much time each worker has of sickTime and Vacation right now.
Commented:
Does the idNumber field exist in the excel sheet? If so it's easy to link to it and then add that table to the union query.  How are you updating the accrued vacation and sick hours. Doing it through Access, or just getting a fresh excel sheet?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
yes idNumber is in excel sheet. idNumber, sickTime, and vacationTime.
Actually the excel sheet of data I just recieved is supposed to be total hours and total sicktime for this year- for each person. So I have to somehow display to users each week what they have left- subtracting each coming week any time that they use.

Author

Commented:
maybe somehow I will create report or query from table- have a total year number and subtract weekly total number - to display some way the current total

thank you

Commented:
Can you upload a sample of your database and excel file?  It will be a lot easier to talk about this if we're looking at the same info.

Author

Commented:
I have so much secure data in it - i started having to delete everything- to upload example-but as you responded to my questions- I started realizing I already had a command button on each form printing it out - so why could'nt I just open report up and have calculations on report- so you helped more than you know when you suggested it should not be hard- thank you

Commented:
To update the remaining totals for vacation and sick time after each person inputs his time, you could use an AfterUpdate event on the form to run an Update Query that would recalculate and write the new number back to the Vacation/Sick Time table.  That would raise an issue of how to present duplicate deductions if someone goes back in and edits a time sheet.  Another, perhaps better, option would be to keep that original table static as a starting figure, and then recalculate the total each time it's displayed (e.g. Starting Total minus the sum of all timesheet entries year-to-date).  That way there's no danger of duplicate deduction.

I understand what you're saying about too much secure data to upload.  If you're good to go, that's great.  If you're still stuck, sn alternative is always to print the table definitions to a PDF (via Tools>Analyze>Documenter).  THat shares the table structure without the table data.  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial