RelationShip Between Tables & Calculation

[braveali]

Hello,

I'm a novice user of MS-Access, I'm trying to create a very basic leave database and need some help in tables' relationship and sums.



I have created 3 tables:

1. Emp: to store all employees info
2. Total Leaves: to store total leaves in each leaves category.
3. Leave Request: Will show how many leaves taken by individual employee

Please see the attached .jpf file for details


Question: What relationship should I create between 3 tables so that I would use a query to calculate the total leaves available, taken, and in Balance of per employee?


I will appreciate your help please.


Thanks & regards

[Demerit]

Between and LeaveRequest, create a one to Many relationship. One employee can request for many leaves. So in the table LeaveRequest, add a field "EmpID" as a Foreign key on which you will create the relationship. For the TotalLeaves, you dont need a table to do that. Queries will do that for you.

Its true that Access 2010 permits calculations on tables but it is still preferable to carry on calculations using queries

[braveali]

Thanks for your reply and help, this gave me a very good idea to simplify my tables, I deleted the Total-Leave table, attached is the reform version of my tables with relationship which works fine:




Thanks!

[Demerit]

On your LeaveRequst Table, Set LeaveID to Primary Key because looking at the screenshot there is no PK in LeaveRequest Table.

The relationship is good.

[braveali]

Thanks, I set it


Cheers!

[ssanfu]

I would suggest a few modifications.

1) "EmpName" should be broke into first name and Last name

2) You have data as field names "AnnualLeave", "SickLeave",... Once you have the queries, forms and reports created, what happens if you have to add another leave type? Say Military leave. You will have to redesign the whole database.

I would suggest something like:

Attachment 14910


My $0.02.......