I am trying to build a query to show the total sum of hours a staff member has done in a certain department and the amount of sleep ins they have done. Each staff member has a unique ID. In the query I can get all the information but it shows on two lines dependent on department. I want this to show on a single line. Each employee will never have more than two departments. The query is called "QryForForum".
View RepliesI have a table of hours that have been worked by employees for each day of the week
[moh] (Monday's hours),[tuh],[weh],[thh],[frh],[sah],[suh]
data eg (this is how I would like it to be inputted into the table)
7:24:00,7:24:00,7:24:00,7:24:00,7:22:00,0:00:00,0: 00:00
This equates to 36:58:00
I have tried
Total Hours: [moh]+[tuh]+[weh]+[thh]+[frh]+[sah]+[suh]
but I am struggling to get what I want in the right format.
How to record the initial data or a formula to format the end result.
Excel just does it !!!!!
Access Query. I am creating a time sheet / pay roll database and I want to be able to get a total of the daily hours in a query.
For example I have 'Mon Start' and 'Mon Finish' for Mondays in/out times and I have a 'Mon Total' which gives me the total hours worked for Monday.
The problem I have is that Mon Total only works if the hours are say between 07:00 and 17:00, anything after midnight (00:00) like 21:00 to 07:00 and 'Mon Total' goes crazy !!
At the moment 'Mon Total' is the result of CDate 'Mon Finish' - 'Mon Start' (bit rough I know).
I created this Vehicle Maintenance and Parts database. In the 'Vehicle Parts Maintenance' button in the frmMenu form, you are supposed to choose the vehicle on the left side then with the drop down on the top right pick what maintenance type you want. Right now when you pick 250 hours it shows all parts attached to the vehicles not only the parts that are supposed to be changed at that maintenance cycle.
The whole database is attached.
I've just made this query at work, it brings up the results from a database of telephone surveys. about going all gestapo on the table names and codes, I'm unsure as to how told off I could get for placing identifiable images on the internet
query.png
basically I would like to extend this query so it shows the sum of the "CountOfQ1 22" column and also shows what percentage of that total each entry in the "Q1 22" column is.
This will enable me to have results for the day sent to me at home every night at close of business as the person supervising the call centre at the time can run the query and email me the results.
I want to subtract 8 hours of work of the Result of employee work hours/
There is attachment...
Hi ther,
I wonder if someone could help me with what I am sure is simply code, I just can't seem to get the response I want. :confused:
I am working out each member of staffs working hours for the day.
[Start Time]
[Finish Time]
minus a 30 min lunch.
I would like to display the total hours worked for the day in an hour:minute format.
I've tried a few diferent things and still not got the answer I was after.
Your help with this is greatly appreciated.
Hi All,
me again, now that I've got over the working hours hurdle I'd like to sum by week.
I'm quite new to access so please excuse my simple questions.
My fields are:
LogID {EmployeeID&Now()}
EmployeeName
StartTime {date&time}
FinishTime {date&time}
I have created a query to give me the total hours worked per day. Is there anyway I can total this by week?:confused:
Once again your help is greatly appreciated
I need a report that show Total ordered for week by Fish for a single customer. I have customer table and OrderDetals table with order date. Shipping date Monday to Friday. When preview the report the it shows the current week Total order quantity for each fish for single customer. How do I get this.
View 2 Replies View RelatedHi,
I am trying to show the time difference between 2 times but the calculation is not working correctly.
First I work out the totaltime on mins between 2 dates using datediff, then I am trying to convert to hours and minutes like :
Total_Time: Format([Sumoftotaltime_mins]/60,"00") & ":" & Format([SumofTotaltime_mins] Mod 60,"00")
This is working until I have something like :
03:00
15:45
25:20
this should work out as 44 hours and 5 mins but for some reason it is showing as 43 hours and 5 mins.
why would this be ?
Hello All,
I need to create a derived attributes (total hours left, total hours used).
In order for the calculation I have days_allocated in the employee table (this is converted into hours by multiplying this by 24).
How can I create a field in a query that works out the amount of hours used and left?
Here is a typical holiday record (screenshot of my query that I am trying to do)
http://img3.freeimagehosting.net/uploads/th.8af9f3cd25.jpg (http://img3.freeimagehosting.net/image.php?8af9f3cd25.jpg)
Can someone please help me create a formula.
Thanks
I have a table wherein the time worked by 25 employees are recorded. This table has the start time, endtime, break time and late times. The Late Time is the number of minutes that the employee is late to work. I am required to do a query of the team's (all 25 staff) late times per month. I have done a query which shows the late times of the individual on the dates that they were late to work and created a crosstab query for that.
I am going around in circles. How can I have a total of the team's late times in a query? Please, could someone please point me in the right direction?
Hello, i have a table with the fields: "employee_number", "shiftdate" "department" "basic_hours", and "over_time"
And a query named "employee_hrs" looks at the fields in this table, i would like a new field in the query to be able to calculate the total hours for all employees together but for each department.
eg the total of: basic hours + overtime for department A)
the total of: basic hours + overtime for department B)
and so on for each department
would only like to show one instance of a department (distinct) with the total hours done by all employees for that department?, if any one could please help me out that would be great!?
I have a problem with Microsoft access 2003. I want to design a Check in/Check out Time Data base for my university final term project. In this data base I need to show the total of the working hours of each personnel in a month.( for instance: 124 hours and 55 minutes in one month). But adding up the hours, when the time variable reaches 24 then time automatically goes back to 01:00. As a result, the correct total hours cannot be calculated....
View 1 Replies View RelatedI am facing one problem while calculating the number of hours an employee spent in the office. Each employee will have a swipe card and the swipe data is stored in an .mdb file. Some employees will have different shifts and may come after 6pm and will go next day 5 am , while calculating their spent hours getting the problem. The swipe data is stored in IOData table , I wrote a simple query below to get the data., it is calculating correctly for the employees who come in between 11AM to 8PM but who are coming after 6PM and going next day , facing problem. Time is storing in 24 hour format in table with Status as Entry,Exit . There may be any no.of exits and entries , so i took min and max of the time in the below query.
I am attaching the mdb file data in an excel sheet(Master_Data.xls) and also the data of the below query(Swipe_Data.xls) and highlighting the employee for which we got problem.
SELECT HD.JobTitle, HD.HolderName, IO.IODate,IO.IOStatus, min(IO.IOTime), max(IO.IOTime), DateDiff("n", min(IO.IOTime), max(IO.IOTime)) AS Minutes, [Minutes] 60 & Format([Minutes] Mod 60, ":00")
FROM HolderData AS HD, IOData AS IO
WHERE HD.HolderNo = IO.HolderNo and
HD.DepartmentNo IN ('0008', '0009') and
IO.IODate between #01/20/2014# and #01/24/2014#
GROUP BY HD.JobTitle, HD.HolderName, IO.IODate,IO.IOStatus;
I am having difficulty getting a second textbox to display the correct total of hours based on a date entered into a first textbox.
Setup:
Table Name: TestTable1
Fields: 'RequestDate' & 'Hours'
Form: 'Form1'
2 Textboxes Unbound: Named 'Date' & 'Total'
What I am trying to accomplish:
Based upon a date entered into the "Date" textbox I want the "Total" textbox to display the total hours associated with that date.
What I have tried:
I have tried using, in the control source property of the "Total" textbox, many iterations of both Sum(IIF & DSum(
I am currently using the following:
=DSum("[Hours]","TestTable1","[RequestDate]='Forms! Form1!'Me.Date'")
I have tried this without the Forms designation; without the "Me" designation; Etc.
Some attempts return the Error or Name error while other efforts return a blank textbox...
I'm a new italian member.
I have a problem:
How can I show hours, in a field, greater 24 h (p.e. 35:15) ?
Thanks
Babaiaga
I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
Hi,
1) I am pretty newbie to this access programming, do forgive me if my questions sounds stupid.
2) Basically I create an application in access capturing or production information for my company. now the top management suddenly wanted whats their main concern:- Total Daily/Monthly, Quarterly, Annual Sales (By Model If possible)
3) I start with daily (Lets don't be too overly ambitious).
4) I try to let user select dates from my calender control and reflect daily sales (in Total & By Model break down) insert into my form.
5) Understand someone told me from my previous post in Calender control I can achieve it either through forms or queries, which is a better way. (in terms of flexibility to change for program maintenance/ scalibility) wise ?
PS: Please forgive my ignorance :o:(
Thanks (In advance) & God Bless.
Hi All.
I created form with unbound listboxs based on query which included Credit field that I want to calculate total and show result in caption of window. If is it possible how to do it?
Thanks.
I have a query that just shows all the records in a table. It is used by the end user for filtering primarily. Now the user would like to see a total for the amount filtered.
For example; the table is for repairs. The query just shows ALL the repairs. The user filters the client field to find all repairs for one client. He then wants to see what the total charges are for that query.
I cant create a new field and sum the records because it is not a totals query. Is there any way to embed the query in a form and use the form portion to sum the filtered results?
hi
i am trying to show the total number of records that are in a table.
there are currently 8 entries in the table 'applications'
now i would like to show this on a tabcontrol in a form. I have been told to use Dcount. I have read about this and tried this on my form but unfortunately its not working.
Can anyone show me an example of how i would do this.
Hi ALl,
I have a subform that accounts and adds all the expenses for a certain project (for example, total is $27,000). I would like to know how to duplicate the final cost in the subform and show it on an unbound textbox of that linked main form (the unbound textbox in the main form shows $27,000 also).
Thanks in advance, guys...
Caliboi
Access 2010 ... I have 2 tables. One with base information second one is linked with multiple results each having a price. On table one i see the + sign when i click i can see the linked second table. Can i get a total amount of the price on table 2 on table one?
IE:
Table 1:
Trans ID - Seller - Quantity - Lot Cost - Parts Cost<-- the one i need total for.
123 - joe - 3 - $100 - $20 <-- the total of the 2 linked parts (Keyboard, Mouse)
Table 2:
ID - Trans ID - Part - Cost
1 - 123 - Keyboard - $10
2 - 123 - Mouse - $10
I have a continuous subform. Because the records are often many, the user needs to scroll down the page.
How do I have a text box in each row that shows Record x of Total Records?
I tried setting a text box control source = [currentrecord] in the Detail area of the subform but it didn't work . It did weird things because I suspect the control is trying to show the record that has the focus or something
This code shows current record number:
Code:
=[CurrentRecord]
This code shows total record number:
Code:
=DCount("[Category]","tblCategory")
How do I combine them in a textbox to say something like "8 of 10"?