年假数据库存在的一些问题 [英] Some problems with annual leave database

问题描述

我需要为公司创建年假数据库。我承认我是一个完整的新手。但是我被抛弃了，我必须这样做。所以这就是我到目前为止所得到的，但是我已经陷入了几天困难，想知道该怎么做。对不起，我想不出更好的标题。


我有一个tblMainProfile表，它存储有关员工详情的所有信息。 ID，姓名，NIRC等等。


接下来，我有tblLeaveEntitlement表，它存储每个员工有权获得的每种类型的叶子的天数。请注意，同一名员工每年可获得不同数量的有权离职。所以我有，索引（auto-number，pkey），EmployeeID（文本，可以混合使用字母和数字），年份（数字，以区分每年的权利），LeaveType（文本，不同类型的叶子，像病假，假期等），DaysEntitled（数字，包含叶子的天数）。


然后我有tblLeaveApply，它存储休假申请的数据。这里我有很多字段，但是那些与这个问题有关的字段 - 我有EmployeeID，LeaveType（组合框，来自tblLeaveEntitlement.LeaveType），Days（包含每个休假申请的天数）。


这是我现在拥有的。员工输入employeeID并点击按钮。弹出一个主窗体，其中包含来自tblMainProfile的数据，1个子窗体，用于显示基于employeeID的当前用户的休假权利，以及来自应用程序本身的tblLeaveApply的另一个表单数据源。现在，当员工申请休假时，在他们选择日期后，将自动计算字段天数（此处没有问题）。然后他们选择他们的LeaveType等等。


我需要做的是显示特定假期类型的总天数，以及剩余的天数（也就是离开平衡）。我不确定我是否应该在TblLeaveApply中为DaysEntitled设置一个字段，或者在tblLeaveEntitlement中有DaysEntitled吗？我尝试使用查询来计算总天数和剩余天数，但它只显示了我的tblLeaveEntitlement中的所有内容。我的假数据是假期 - 2天。但它最终向我展示了所有类型的假期，即使我没有这样的数据也需要2天。当我在查询中同时使用tblLeaveApply和tblLeaveEntitlement表时会发生这种情况。每当我只使用一个表时，它工作正常。除了我不能只使用一个表，因为tblLeaveApply没有字段DaysEntitled。

查询中的计算字段如下：

展开 | 选择 | Wrap | 行号

解决方案

我会根据tblLeaveEntitlement和tblLeaveApply进行查询。

这两个表应该使用EmployeeID和Year连接，并且应该加入，以便查询显示所有EmployeeID和相应的年份来自tblLeaveEntitlement，并且只有那些来自tblLeaveApply的字段Emp loyeeID和Year是平等的。


然后根据该查询创建一个子表单并将其放在通过EmployeeID连接它的MainProfile表单上。

真的很感激你的回复。所以你的意思是我应该在tblLeaveApply中有一个Year字段，用户必须在应用休假时在当前年份手动输入？有没有更好的方法在这里输入年份字段的值？我想把它放在Open Form事件下，所以它会用当前年份自动填充它。它会工作吗？

展开 | 选择 < span class =codeDivider> | Wrap | 行号

真的很感谢您的回复。所以你的意思是我应该在tblLeaveApply中有一个Year字段，用户必须在应用休假时在当前年份手动输入？有没有更好的方法在这里输入年份字段的值？我想把它放在Open Form事件下，所以它会用当前年份自动填充它。它会起作用吗？

我不会使用Open form事件在Year字段中输入值。

这样你打开时就会改变那个字段表格（即使是明年）

最好在组合框列出LeaveType或类似的东西的AfterUpdate事件中这样做...

展开 | 选择 | Wrap | 行号

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:

Expand|Select|Wrap|Line Numbers

解决方案

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.

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?

Expand|Select|Wrap|Line Numbers

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...

Expand|Select|Wrap|Line Numbers

这篇关于年假数据库存在的一些问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！