Stuck

[Patrickv]

Hello all. new user here, not new to the computing world though
I have made a small access database for work. Actually it's a Personnel management system. I have created tables/relationships and all is good.

Here's the Problem.
The db has a field name [Days] and one name [Taken]. This is based on how to apply for leave (holidays).
I made an unbound control and subtracted [Taken] from [Days] and got my results [Days left] which is all good. The value [Days Left] is not stored though.

However, every year, we are entitled 27 more days leave.
How do I add that 27 on my [Days] ?
Note that I have to store the values in [Days Left] also.

many thanks any help!!

cheers

Patrick

PS :: If you want the database to actually understand what I'm talking about please let me know.

 

[namliam]

Sorry to say but this is a design issue

Instead of storing a total of "allowed days" and "taken days" you should be storing the mutations of these days in a seperate table.
+27 << New year
-5 << week leave
-15 << Summer holiday
etc...

Then a simple sum will give you your (calculated) value of days left...

On the matter of

Note that I have to store the values in [Days Left] also.

NO NO NO NO NO storing calculated values is a BIG NO NO !!
You should not You will not
YOU WILL NOT do this!

Edit:
Welcome to AWF

Edit2:
Good luck on the redesign

 

[Patrickv]

Instead of storing a total of "allowed days" and "taken days" you should be storing the mutations of these days in a seperate table.
+27 << New year
-5 << week leave
-15 << Summer holiday
etc...
Then a simple sum will give you your (calculated) value of days left...

I don't mind redesigning that's ok but I don't understand what you said above

Edit:
Welcome to AWF

Edit2:
Good luck on the redesign

lol thanks

 

[namliam]

Well you want to store the totals, but you need to store the changes and calculate the totals...

the example given is the example of the changes which need to be stored per employee.

 

[Patrickv]

Well you want to store the totals, but you need to store the changes and calculate the totals...

the example given is the example of the changes which need to be stored per employee.

namliam i cannot understand
can you tell me how to go about doing this ?
am no access expert, I can give you all the details you want though

 

[namliam]

Instead of storing totals which you need to change store the changes... I dont know how else to put it...

Instead of having "Days allowed", "Days taken" and "Days left" each for say 27, 15, 12 for a given person...

You would make a seperate table which would store...
Mutation Days / reason / Start date / end date (or something alike)
+27 / New year / 01-01-2009 / null
-10 / Summer holiday / 10-08-2009 / 28-08-2009
-5 / week leave / 12-10-2009 / 16-10-2009

Then in above table simply sum all for the total left ( +27 - 10 -5 = 12 )
sum everythign <0 for the total taken (15)
sum everythnig >0 for the total allowed.

 

[Patrickv]

I've been trying to understand how to do this thing.
Please check the link below

http://www.nsb.gov.sc/db.zip

I've placed my sample database there.
Please note that my primary key is NIN

cheers

edit : sorry about the above, it's suppose to be 21 days not 27 like said

thanks

 

[namliam]

How would you then relate this leave data back to your main table? there is no NIN in the leave table to link/join it.

 

[Patrickv]

How would you then relate this leave data back to your main table? there is no NIN in the leave table to link/join it.

ok check the link again, i've fixed it. I've put NIN on the leave table and link it with relationship

 

[namliam]

Yes, that may work...

Now I have another question, why have an autonumber field if that is not your PK?

all you need is a query that sums the seperate numbers...

 

[Patrickv]

Yes, that may work...

Now I have another question, why have an autonumber field if that is not your PK?

all you need is a query that sums the seperate numbers...

ah yes you're referring to ID on Main Table right ? yeah sorry bout that didn't mean to leave it there, it shouldn't. Don't worry about it though.
So you say a query, how does the query link to the leave table ? ::

 

[namliam]

The query itself doesnt the NIN in the table does like any normal related table...

 

[Patrickv]

Stuck. Don't know how to do this. if anybody is willing to point me in any direction I'd gladly accept the help.

thanks

 

[namliam]

You have to know how to "make a query" ??
You have to know how to "join" tables??
You have to know how Primary vs Foreign keys work??

 

[Patrickv]

You have to know how to "make a query" ??
You have to know how to "join" tables??
You have to know how Primary vs Foreign keys work??

I know how to make a query
I know how to Join tables
I understand PK and FK.

But despite the above I don't know what to do extactly..

 

[namliam]

Step 1
Make a query to sum up the "tally" of the days recieved and days taken.
+27
-5
-15

Sum : 7 left

2)
Join your main table in a query to the first step's query and use that as a base for your form.