Some problems with annual leave database

[jjkeeper]

Hi, I need to create an annual leave Database for the company. I admit I was a complete novice at this. But I got thrown the job and I have to do it. So here's what I get so far, but I got pretty much stuck for some days figuring out what to do. Sorry I can't think of a better title.

I have a tblMainProfile table, which stores everything about employee's particulars. ID, name, NIRC, etc etc.

Next, I have tblLeaveEntitlement table, which stores how many days of each type of leaves does each employee gets entitled to. Note that the same employee may get different number of entitled leaves each year. So I have, Index(auto-number, pkey), EmployeeID(text, can have a mix of letters and numbers), Year(number, to differentiate between each year's entitlement), LeaveType(Text, the different types of leaves, like Sick Leave, Vacation, etc), DaysEntitled(number, contains the number of days of leaves entitled).

Then I have tblLeaveApply, which stores data for leave application. Here I have many fields, but those that are related to this problem - I have EmployeeID, LeaveType(combobox, get from tblLeaveEntitlement.LeaveType), Days(contains how many days is each leave application).

Here is what I currently have. Employee input employeeID and hit a button. A main form pops up, which contains data from tblMainProfile, 1 sub-form to show the leave entitlements belong to current user based on employeeID, and another form data source from tblLeaveApply for the application itself. Now when an employee applies for a leave, after they select their dates, the field Days will be auto calculated (no problem here). Then they select their LeaveType and so on.

What I need to do is to show the total number of days taken for that particular leave type, and how many days are left (aka Leave balance). I'm not sure if I should have a field in tblLeaveApply for DaysEntitled, or is having DaysEntitled in tblLeaveEntitlement okay? I tried using a query to calculate total number of days taken and days left, but it just shows me everything in my tblLeaveEntitlement. The dummy data I had was Vacation - 2 days. But it ended up showing me ALL types of leave with 2 days taken even though I don't have such data. This happens when I used both tblLeaveApply and tblLeaveEntitlement tables in the query. Whenever I use just one table, it works fine. Except that I can't just use one table because tblLeaveApply doesn't have the field DaysEntitled.
Calculative fields in query is as follows:

TotalLeaveTaken: Days
Sum

DaysLeft: DaysEntitled-TotalLeaveTaken

The possible solutions I can think of is (1) create a DaysEntitled field in tblLeaveApply so my query can just use tblLeaveApply only. But I can't make the form autofill up the value from tblLeaveEntitled, based on whatever leave type the user chooses from combo box. I tried DLookup but it just tells me either data type mismatch, or no automated object. (2) somehow make the query works using 2 tables. I'm not really sure how to get this going.

tblLeaveApply and tblLeaveEntitlement are both linked using EmployeeID, none are Pkey. tblMainProfile and tblLeaveApply are linked using EmployeeID too, with tblMainProfile's employeeID being Pkey.

Thanks in advance. I been trying to make it work with much frustration. Any more data needed I would gladly provide. But as this is my first time, I'm not sure what information would be useful oand what would be not.

[hjozinovic]

I would make a query based on tblLeaveEntitlement and tblLeaveApply.
These two tables should be joined using EmployeeID and Year, and should be joined in order that query shows ALL EmployeeIDs and corresponding Years from tblLeaveEntitlement and only those from tblLeaveApply where the fields EmployeeID and Year are equial.

Then make a subform based on that query and put it on MainProfile form connecting it via EmployeeID.

[jjkeeper]

Really appreciate your reply. So you mean I should have a Year field in tblLeaveApply which the user have to manually input in the current Year upon application of the leave? Is there a better way to do enter the value for the Year field here? I'm thinking of putting this under the Open Form event so it auto fills it with current year. Will it work?

Me.Year.Setfocus
Me.Year.Value=Year(Date())

Because right now I only have Year field in tblLeaveEntitlement. I admit I didn't think of that. I was just thinking of EmployeeID and LeaveType.

Another big problem is actually the calculation of the TotalDaysTaken and DaysLeft. I will post the SQL codes of my current query tomorrow when I'm at work (I don't have the database at home). Btw, it's 11.30pm now, just so you can estimate the time if you want to. But I get weird results whenever I try to create a query using two tables.

[hjozinovic]

Really appreciate your reply. So you mean I should have a Year field in tblLeaveApply which the user have to manually input in the current Year upon application of the leave? Is there a better way to do enter the value for the Year field here? I'm thinking of putting this under the Open Form event so it auto fills it with current year. Will it work?

I would not use Open form event for entering value into the Year field.
That way you would be changing that field whenever you open the form (even next year)
It would be better to do that in the AfterUpdate event of combo box listing LeaveType or something like that...

Me!Year=Year(Date())

To ansver other part of the question...yes, you should have Year field in your tblLeaveApply table in order to distinguish which Leave reffers to which year.

[hjozinovic]

After exmining this better, I would recommend you the following:

tblMainProfile
ProfileID (pk)
EmployeeID
Name
...


tblLeaveEntitlement
LeaveID(pk)
LeaveType

tblLeaveApply
LaYear
LaLeaveID(fk)
LaProfileID(fk)
LaDaysEntitled
LaDaysUsed

These three tables are related using pk/fk

Now let's get to the business...
A) Create Query1 using all fields from tblMainProfile and tblLeaveEntitlement
Do not join these two in order to get cartesian product (all leave types for every employee)
B) Create Query2 using tblLeaveApply and Query1
Join them using ProfileID and LeaveID
Note: for each connection right-click the link between fields and select type that says something like "Select all ProfileID from Query1 and only those from tblLeaveApply where join fields are equial"
Do the same for LeaveID link.
Make a new field in Query2 like DaysLeft: Nz(LaDaysEntitled,0)-Nz(LaDaysUsed,0)
C) Make a subform based on Query2 and put it on MainProfile form connecting it using ProfileID

[jjkeeper]

Oh wow, thanks very much for putting in the effort and time! I'm in the office now, I'll go and try it out and post back later.

Edit: Just one question, currently my tblMainProfile's Pkey is EmployeeID, and I link the different tables together using EmployeeID too, with tblMainProfile's being Pkey and the other tables' EmployeeID being Fkey with respective index field being Pkey (LeaveID, etc). Is there something wrong with the way I did it? Because from what you said, I should be creating a ProfileID auto-number field to be Pkey, and linking using that instead of EmployeeID.

[jjkeeper]

okay, here's my query SQL codes. Here's what I need to get from the query. I need to show the LeaveType, DaysEntitled, EarnedLeave, TotalLeaveTaken and DaysLeft. I don't have DaysLeft in the query yet because I can't get TotalLeaveTaken to work.

In layman term, I need to show all Leave Entitled to EmployeeA in Year1, including the DaysEntitled, EarnedLeave,DaysLeft for each particular leave type. Whenever I include fields from tblLeaveApply, I get weird results like having multiple same records in the table.

SELECT tblLeaveEntitlement.LegacyID, tblLeaveEntitlement.Year, tblLeaveEntitlement.LeaveType, tblLeaveEntitlement.DaysEntitled, Int(([DaysEntitled]/12)*Month(Date())) AS EarnedLeave, Sum(tblLeaveApply.Days) AS TotalLeaveTaken
FROM tblLeaveEntitlement LEFT JOIN tblLeaveApply ON (tblLeaveEntitlement.LegacyID = tblLeaveApply.LegacyID) AND (tblLeaveEntitlement.Year = tblLeaveApply.LaYear)
GROUP BY tblLeaveEntitlement.LegacyID, tblLeaveEntitlement.Year, tblLeaveEntitlement.LeaveType, tblLeaveEntitlement.DaysEntitled
HAVING (((tblLeaveEntitlement.LegacyID)=[Forms]![frmLeaveApply]![tboLegacyID]) AND ((tblLeaveEntitlement.Year)=Year(Date())));
 

EDIT: I figured it's probably due to the way I defined my LeaveType field in tblLeaveApply. Currently, I use a Lookup wizard to get the list of LeaveType from tblLeaveEntitlement. The wizard creates this link

SELECT [tblLeaveEntitlement].[Index], [tblLeaveEntitlement].[LeaveType] FROM tblLeaveEntitlement; 

, auto renamed my LeaveType to Index and change the field type to Number. So I rename it back to LeaveType, change it back to Text. And change the codes to this instead

SELECT [tblLeaveEntitlement].[LeaveType] FROM tblLeaveEntitlement;

it works fine, I can select the correct list of LeaveTypes in the combo box, but when I try to make the query such that (with leaveType linked) show only those records that have the same data in linked field (just for testing), there're no records that are the same. Although upon manual comparison, there are "Vacation" in LeaveType fields in both tbl. So I'm thinking it's due to the way the LeaveType field is defined in tblLeaveApply. I want it to read from LeaveType field in tblLeaveEntitlement. What's the right way to do it?

[hjozinovic]

Here is a sample database attached with what you want.
I believe design of your tables in the first place was not good.

Try looking at this and tell us if this is what you want

[jjkeeper]

Here is a sample database attached with what you want.
I believe design of your tables in the first place was not good.

Try looking at this and tell us if this is what you want

Many thanks. I'll take a look later, currently rushing a job to get it done before due. Thanks for the time and effort !!

[jjkeeper]

Yes, the idea is there, but for mine, i put the DaysEntitled into tblLeaveEntitlement instead of tblLeaveApply. I managed to get it to display now though. There were some problems with the way my LeaveType field in tblLeaveApply was defined. Mostly due to the wizard, and then my own editing. Now my LeaveType fields in both tblLeaveApply and tblLeaveEntitlement can properly sync and show me matching results.

So now i have this query.

shows all entitled leave if year is current, and status of leave application is not rejected. This query also calculates the TotalLeaveTaken and DaysLeft. This query is then used as a subform, linking with main form using EmployeeID.

The slight problem now is that, yes, it correctly shows all LeaveEntitlement based on year and employeeID. But, how do I make the query only calculate those days where the leave status is approved BUT show all records belong to the employee and current year? If I put in criteria for status to be "Approved", it only shows those records that have approved leave, and not all the different leave types for that employee and year.

[hjozinovic]

DaysLeft: Nz(LaDaysEntitled,0)-iif([Approved]=True,Nz(LaDaysUsed,0),0)

This will calculate your DaysLeft field.

I assumed Approved field is Yes/No type.
You can use this "IIf()" function with other field types and criterias.
I'm sure in access help there is alot on this function.

[jjkeeper]

Yes, that works wonderfully. Many thanks once again.