Sum of days using multiple criteria (1 Viewer)

[ijaz196]

I have a Leave Record Table which contains following fields:

(a) Worker_Name
(b) Worker_ID
(c) Leave_From (Beginning date)
(d) Leave_To (End Date)
(e) Kind_of_Leave
(f) No_of_Days
(g) Year_of_Leave
(h) Total_Casual_Leave
(j) Total_Annual_Leave
(k) Remarks

I want to calculate Total Casual and Annual leave gained during the year by a worker. But I cannot get the required results. It is pertinent to mention that I am beginner in MS Access and have very limited knowledge about access. Any kind member can help me for said calculation.

I will thankful to you

 

[theDBguy]

Hi,


Just as FYI, in relational database development, calculated values is rarely stored in tables. Instead, the value is calculated whenever it is needed. This ensures data integrity, in case any of the values used for the calculation has been updated. To recalculate the value, it is typically done using a query with a calculated column.

 

[MajP]

There has been several similar posts recently. The easiest would be to go to excel and build a table of all dates for the required years. Maybe ten years out. Then you can import that table. Once you have that get rid of the totals in the table. You can then calculate those in a query. You can do a join which will return all the dates between the start and end of leave. Then you can use that to do all your totals.

 

[plog]

Your table is improperly structured--you are storing redundant/calculable data.

a - this is redundant data and should not be in your LeaveRecordTable. Instead you should have a WorkerTable and with the Worker_ID field you can JOIN to it to retrieve all the data about a worker.

f - this is calculable and should not be in your LeaveRecordTable. You can use simple subtraction from Leave_From and Leave_To to determine this value.

g - this is calculable and should not be in your LeaveRecordTable. You can use the Year(https://www.techonthenet.com/access/functions/date/year.php) function on either Leave_From or Leave_To to determine this.

h & j - these should not be in your LeaveRecordTable. If this is the sum of No_of_Days, then it is calcuable and not stored. If this is entered by a user and represents how much leave a worker has, then it belongs in another table. In either case, these fields shouldn't be here.



Once you remove those fields from this table, to generate the Totals you run this query:

SELECT Worker_ID, Kind_of_Leave, YEAR(Leave_From) AS Leave_Year, SUM(Leave_To - Leave_From) AS Total_Leave
FROM LeaveRecordTable
GROUP BY Worker_ID, Kind_of_Leave, YEAR(Leave_From)

 

[MajP]

Once you remove those fields from this table, to generate the Totals you run this query

Maybe it can be done that way, but often unlikely since there are usually weekends and holidays inside that time period that do not get charged as leave. Unless you are creating multiple start and stop periods. That is why often you need a table of dates especially if the company has specific required holiday dates.

 

[jdraw]

Seems there is uncertainty in the actual requirements as well as table design and query options.

 

[ijaz196]

Thank you for the for your kind response. I will try your suggestions.