Not only am I new to this forum, I am also new to Access!
Firstly I am running Access 2003. I went on a course 2 years ago but this is the first time I have started creating a database so please bare with me.
I am creating a database for staff holidays/public holidays/maternity leave etc within a unit which has subdivisions.
I have created a table called Staff, that has staff details i.e. employment #, name, job title, subdivision name, holiday outstanding balance and public holiday outstanding balance.
I have created a second table which gives the employment #, hoiday year, holiday entitlement and public holiday entitlement. This table I've connected with a one to many relationship.
I have created a third table with employment #, subdivision name, holiday dates from and to, # of days and type of leave. I don't know where to connect this table too!
My ideal end result would be when data is entered for a holiday request you can cascade from employee details to dates requested and then cascade down to see who else is off within that subdivision at that time.
How would I get table 3 to calculate taking one date from another, knowing how many days have been requested and entering it into the 'No of Days' column? Then, depending on the 'Type of Leave' requested deduct the 'No of Days' from the yearly entitlement and enter the balance into the 'holiday outstanding balance' or 'public holiday oustanding balance' if it applies, in table 1? And, thereafter deduct the 'No of Days' from the outstanding balance in table 1 leaving an uptodate 'outstanding balance'?
I hope this doesn't sound too confusing. I must admit my brain is turning to mush trying to work it out I have a niggling doubt in the back of my mind telling me I'm trying to do this the wrong way round!
If anyone can point me in the right direction I would really appreciate it.
You can't have formulas or calculations in tables. They belong in queries, forms, reports or code.
Since you haven't figured out how to relate table 3 to other(s), you shouldn't really even be thinking of calculations until you get your table design right. For instance, you may not need number of days if you have a start date and an end date. Those balances might not be needed either; you may be able to recalculate the balance from other data.
Sorry, but I think you are trying to do too much too soon in tables.
Why did you wait 2 years to start!? That effectively means you have NO training You might like to consider repeating the course? It might save you a lot of stress in the long run
I do try to run before I can walk at times, thanks for keeping me on the straight and narrow
You're absolutely right I'll redo the course and then focus on getting my table design right.
I did my ECDL to help get me back to work after bringing up my family. I hadn't been working, office based, for 16 years and only got back to work last year, hence the delay in writing a database. However, when I did the course I thoroughly enjoyed it so I'm determined I'll get this database written and working well.
I would never have thought anyone would ever need a licence to use a computer... I mean there are some people who are dangerous with a computer, but extorting them to pay money for a license isn't going to help much.
You don't need the Licence to use the computer. It's a proficiency qualification in the use of the Microsoft Office programme. There are 7 modules to the ECDL and you have to pass each one before you are awarded the certificate.
A lot of employers over here are asking for this qualification if you apply for an office based job.
As you can tell, I've been the best advert for the Access Module
But what it does prove is - If you don't use it, you lose it!
Although my husband would say I never had it in the first place