Queries :: DSUM - Calculating Monthly Tasks Given To Employee
Dec 6, 2013
I have a query, i need to get dsum of total task given to an employee.
I have grouped task_description and select count and it is now showing each employee as 1 which is correct.. i ma looking to get dsum based on this task count but with a criteria that i want to see these tasks sum month a have a month field which i get from assigned date the month field is showing like this december-2013.
Based on this i want to calculate dsum as first to see task count which i mentioned earlier, then employee id which is in query as ID and then month.. this sum will calculate monthly tasks given to each employee.
View Replies
ADVERTISEMENT
May 6, 2015
I need the ability to assign employees to a different task in each of four different timeslots on a daily basis. What I would like to do is as I assign an employee to a task in timeframe 1, I would like for them to become unavailable to assign to another task in the same timeframe.
For Example: Three employees to assign to three tasks at the 8 AM to 10 AM time slot.
Employee
Task
Time
Assigned
Emp 1, Emp 2, Emp 3
Task 1
8 AM to 10 AM
Emp 2-Task 1
Emp 1, Emp 3
Task 2
8 AM to 10 AM
Emp 3-Task 2
Emp1
Task 3
8 AM to 10 AM
Emp1-Task 3
It would be nice to set this up in a form where my person doing the scheduling can assign an individual to a task and that individual name disappears from the combo boxes, list boxes, or is "greyed-out" in a listing of employees available for the time period for the remaining assignments.
View 7 Replies
View Related
Apr 3, 2013
We are trying to find a way to have a query figure out when the hours of an employee reach 40 hours (Overtime) and figure the hours correctly. The Data Entry table contains the basic fields:
[Workdate].....The date the work was done
[WEDate].......The Week Ending Date. Always on Sunday, and calculated like this "[WorkDate]-Weekday([WorkDate]-1)+7"
[Employee].....Employee Name
[Qty].............# of hours worked that day
[RegHours].....Blank, to be updated by query
[OTHours]......Blank, to be updated by query
The problem we are running into is getting it to update the daily running totals (using the [WEDate] & [employee] as the "Group Bys"). We'd like use an update query to fill in the to blank fields with the correct calculations.
Example - Working 6 day week, at 9 Hours per day in the [Qty] field
Day.............[RegHours]......[OTHours]
Monday..............9..................0
Tuesday............18..................0
Wednesday........27..................0
Thursday...........36..................0
Friday...............40..................5
Saturday...........40.................14
Sunday.............40.................23
We have tried several things to get it to compute this field and haven't had much luck.
View 1 Replies
View Related
Dec 26, 2006
Greetings all!!!
I have a database containing a table of employees and their rank. I want to record monthly details regarding these people: hours, performance, etc. Their rank can change dynamically from month to month. They may be a Supervisor for a month, and I want to change this value on a monthly basis. But I want to generate a Table which shows all employees belonging to each of the eight departments, but which has empty fields enabling me to record these monthly values. I then want to be able to pull up that data at any time to see these values for any given month in the past. I also want to add the values of each field and total them for the various departments providing monthly and yearly values on a departmental and organizational (grand total) level.
The employee names and their respective departments rarely change. I don't want to have to select their names (about 125 of them) every month to create a new blank form in which to put the monthly data. I want to have perhaps a button which creates the records and puts the names in for (1) department, (2) year, and (3) month, but that adds the fixed fields ready to put the data in. This data is required every month and the field names will never change. I'm sure the solution is programmatically simple, but I seem to have a mental block over the issue.
I hope that someone casts some light on this.
Do I use a Create Table query? (The data can remain in one table.) I would like to do it programmatically. My VB is pretty good, but, it seems, not good enough.
:confused:
View 7 Replies
View Related
Oct 26, 2006
Hello my beautiful worldwide friends :D
I am trying to calculate employee Overtime hours from their recorded TimeIn and TimeOut if over 7.5 hours. So anyday they work past 7.5 hours should be calculated and totalled at the end of the week. And i can't seem to figure it out, maybe my maths is bad? Maybe it's my query? The section of my report? What could i be doing wrong here, i have spent hours and im getting no where.
I have attached my report.
Can you help me figure out why my daily total shows but the weekly total is not showing?
View 9 Replies
View Related
Jun 14, 2014
I would like to make an action list based on tasks. I try to make a query that does the following things:
-Taken.Priority=1
-show maxium 5 tasks sorted on make date(taken.ingevoerd)
-OR with a total duration of maxium 480 minutes (Taken.[Geschatte duur (min))
The last one is to hard for me to make. For example if the duration of 4 tasks is 420 minutes, the fifth task (100 min) should not be shown, because the criteria of maximum amout of minutes is reached.This is what I have made so far:
SELECT DISTINCT TOP 5 Taken.ID, Taken.Omschrijving, Taken.Ingevoerd, Taken.Deadline, Taken.Priority, Taken.[Geschatte duur (min)]
FROM Taken
GROUP BY Taken.ID, Taken.Omschrijving, Taken.Ingevoerd, Taken.Deadline, Taken.Priority, Taken.[Geschatte duur (min)]
HAVING (((Taken.Priority)=1))
ORDER BY Taken.Ingevoerd DESC;
View 2 Replies
View Related
Aug 14, 2013
I want to count the number of tasks by department by week. I need the time so my date the task was added is formated as a date/time.
I created a query and added the department (twice so that I can group and count), and transaction date. I clicked on totals and added the count function under the department. I added this criteria to the task date: between [start date] and [end date].
Problem is that it's grouping by day and each one is different because all times are different. How do I group these by day and not time?
View 7 Replies
View Related
Apr 22, 2015
I have a query for loan calculation, fields are;
Loanamount
loandate
monthlyinstalment
what i want is that query to start subtracting lmonthlyinstalment from loanamount on monthly basis
View 3 Replies
View Related
Aug 9, 2015
I currently have a table where we are logging a task number against a date and time for each employees to track activity.What I am looking to do is in a query have access lookup the previous reference number for the employee before the date/time of the individual record but on the same day
example
Staff 1 08/08/2015 12:00 Task Ref: 1234
Staff 1 09/08/2015 14:00 Task Ref: 3456
Staff 1 09/08/2015 15:00 Task Ref: 7891
to show something like
Staff 1 08/08/2015 12:00 Ref:1234 Previous Ref: Null
Staff 1 09/08/2015 14:00 Ref: 3456 Previous Ref: Null
Staff 1 09/08/2015 15:00 Ref: 7891 Previous Ref: 3456
Fields are
Employee
DTActStart - this is when task started
DTActEnd - this is when activity ended
Task ID - Reference for task (unique field)
I've tried seaching forums (Used DMax and Lookup) but doesnt find the most recent record before the in view record/I'm thinking a query is the best way so that when Print a report of tasks run of tasks will then show
View 3 Replies
View Related
Feb 22, 2015
i have
Movements_during_qry ------ movments to all employee during day
Movements_qry------- which including arrival in morning and departure in end day
How I combine two queries in one query to show data
example
In day 05/02/2015 show employee 1 show all movements and arrival (from 2 queries)
View 3 Replies
View Related
Nov 18, 2013
I am using DSum to total some records located in a subform. I have code to save the value returned by DSum to a table on a lost focus event. It works great except for one thing. The value saved is always one record behind. The save code is:
Dim dbs As dao.Database
Dim rst As dao.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblInvoice", dbOpenDynaset)
rst.FindFirst "ID = " & Forms!frmInvoice!txtID
With rst
.Edit
!Total = Forms!frmInvoice!txtTotal
.Update
End With
Exit Sub
txtTotal is the textbox containing the DSum value. It displays the proper value. If I insert a blank row the routine will finally pass the right value. I tried running the code from a button but it still copies the old total.
View 3 Replies
View Related
Nov 17, 2014
I'm attempting to do a DSUM in a query against a linked table that has a field name of "VOID". The field name shows up as a yes/no when looking at the structure and when looking at the data, it is either 0 or -1. I've tried just about everything I can think of but this is the structure I have:
Tons: DSum("Net","dbo_tblSALESTKT","JOB='" & Left([JobNumber],6) & "' AND Ticket_Date=#" & [forms]![frmForemanInfo]![CurrentDate] & "# AND [dbo_tblSALESTKT]![VOID]=0")
If I remove the VOID part, it will sum correctly but when adding in the VOID=0 or false or No or anything, the query doesn't show me a sum anymore. what I'm doing wrong.
View 2 Replies
View Related
Apr 16, 2014
I have a requirement to categorize a field in the employee database.
The requirement is: for any given user designation, if the total number is 10 or more, I need the actual designation itself as the output. However, if a particular designation in the organization is less than 10 in total, the output should be 'Misc - and the grade'.
Also, this will need to be done for each grade as well. For example, though the staff designation 'Software Engineer' count is 15, the designation is present across two grades A and B. So, in the output, for the staff with Grade A will show the actual designation as the count is 11, and for Grade B staff with the desgination 'Software Engineer', since the count is 4, it should display the output as 'Misc - Grade B'.
I would like to achieve this in MS Access 2010.
View 2 Replies
View Related
Jan 16, 2014
I have a query which includes a deposit field and a Transaction_Date field. I would like a cumulative deposit on each record (arranged according to date). The following is the expression I am using:
=DSum("[Deposit]","[myQuery]","[Transaction_Date]<=#" & [Transaction_Date] &"#")
I am getting completely nonsense values from the expression - some of the fields are blank (the first few fields) and then the values bear no relation to the deposits preceding them. I have tried all sorts of formats including using Format([Date], 'dd/mm/yyyy') on either side and then on both sides but with no effect.
View 4 Replies
View Related
May 15, 2014
I can't seem to get my head around an issue i'm having with a DSum, the statement is:
Code:
=DSum("[Amount Used]","[tbl_Rwk_Used]","[Cage Number]='" & [IBC_Number] & "'" And "[ID]=" & [ID])
I've tested both statements separately and they work ok, but as soon as I add the "AND" operator, the whole field is summed and the criteria thrown out of the window apparently.
[ID] Is the same in both tables and relates to a main record
[Cage Number] and [IBC_Number] are related in both tables, but are and are strings
View 2 Replies
View Related
Oct 19, 2013
I am having a query showing customer as client, paid amount as pamount, billingdate, payment date as pdate.
I want to get dsum of pamount specific for each client with date criteria for example if i open query through form mentioning start and end date then the sum must vary as per the date given. the date is "billingdate" as mentioned above.
View 10 Replies
View Related
Jul 31, 2013
I have a query which is compiling monthly transaction of fee, the students paying through voucher. To every month the query is building records for students (Grouped), my problem is i have to show the arrears in the same query which would be on minus/plus of paid amount from actual fee both are in this query i can populate the arrears by simply subtracting both fields but the thing is that i am looking for a arrears which would be the adding total of previous arrears as to date transaction.
View 5 Replies
View Related
Jul 23, 2013
Me having a db which is having a student table which is keeping all student info, second i have a Van table which is keeping van info. I wanted to know how it is possible to calculate monthly van fee for each student on monthly basis which db do it itself based on system date/time and calculate monthly required fee for each student. I also would like to know that if a student do not pay in a month then checking the next month or checking unpaid student through date criteria it displays all due amount may be in sum.
View 2 Replies
View Related
Mar 28, 2013
I currently have a database that has the following tables:
t_Employees
t_Cost_Centers
t_Changes
The [t_Changes] tracks what cost centers the employee is in with a start date and end date:
Change_ID
Employee_ID
Cost_Center_ID
Start_Date
End_Date
I want to run a query that will show a single line per employee, and different columns for each of the cost centers the employee was in. If the employee was in 3 different cost centers, the query would have 4 columns (1 for the employee, 1 for the first cost center, 1 for the second cost center, 3 for third cost center).
I saw another thread to linked to a concatenate example by Allen Browne, but that places multiple values in a single cell. I want different columns per cost center.
View 2 Replies
View Related
Jul 10, 2013
I need to do a DSum with criteria that picks up a previous month based on todays date, I have got the following but I don't know how to format the last part of the criteria.
If I take out the Month(DateAdd("m",-13,(Date()))) and replace it with 6 it works fine.
Code:
DSum("[invquan]","[qrySalesByStockCode]","[stcode] = '" & [stockcode] & "' and [Month] = Month(DateAdd("m",-13,(Date())))'")
View 1 Replies
View Related
Jul 9, 2013
I am trying to create a user-friendly database to enter invoice records (deductions and additions) for securities. I would like to be able to run queries with running totals from month to month inclusive (end total of one month = starting total of next month). I have already figured out how to create a running total; however, I have numerous "companies." Each record could be from any of 30 companies. When I add up the running total from say January 1 to February 28th, I get the additions and deductions of all the companies, not just the one I want. Inputting the companyID (example A110) in the query box only adjusts what is displayed, not the running totals that go by the total deductions from the records before that record (I assigned each record a "database ID" because dsum does not recognize dates in the criteria box, if I want to do a running sum.
I also was wondering if it would be possible to build forms with macros to create these queries in a user-friendly manner (so the company that I am interning for can create queries when they need them without me there).
View 12 Replies
View Related
Nov 9, 2014
I am trying to make a dsum for products coming in and coming out. find the example below
Products In
Apple 10
Mango 5
Products out
Apple 5
Now I want to show statement for sum as follows
Product. In. Out. Net in hand
Apple. 10. 5. 5
Mango. 5. 0. 5
But it is showing only
Product. In. Out. Net in hand
Apple. 10. 5. 5
what I understand is dsum cannot find the out field for mango as there is no field found for mango
View 14 Replies
View Related
Nov 14, 2013
I am currently working on a small database to track my own investment records.
I would like to create the function which enable to count the number of records that has exceeds the average value during that period.
Following is the SQL that I have managed to produce:
SELECT [ED 2013].Code, Count([ED 2013].Start) AS CountOfOpen1
FROM [ED 2013]
WHERE [ED 2013]![Date] Between #1/1/2013# And #2/1/2013# AND [ED 2013]![Start]>[ED 2013]![End]
GROUP BY [ED 2013].Symbol;
This SQL does work. However, when I try to add the Avg function. The Query stop working.
Meanwhile, I am wondering if there is any existing Ms Access template (free or commercial - but must be customisable ) available?
View 1 Replies
View Related
Jul 1, 2013
I am having trouble with this running total. Let's say I have a well. I am trying to create a running total that calculates the total Uptime (or hours operational) for each well every month.
I attached a PDF with an example of what I am working with.
View 10 Replies
View Related
Jun 23, 2013
i have 3 tables one for student information second for Van information and third is payment.
I want to know how to get the list of students not paid based on specific month which need to mentioned may b through a form.
View 1 Replies
View Related
Apr 17, 2014
I have a table that track monthly data for several thousand products.
The idea is to grab the first month and the first three months of sales for each product.
So I simply need to have a statement that scans through a predefined set of fields and seeks the first none zero value. This become the first month of sales (even if it is the 5th month of the year). It then needs to be able to grab the proceeding 2 months to create a sum of sales in the first 3 months. (however that would be for a second field, so presumably that is just a slightly more complex version of the same formula used to find the first month of sales).
View 2 Replies
View Related
