hi all,
i am trying to create a report based on my query, and would like to split the total time up by 2 diff tasks. admin and investigative. problem i am running into is when using the total time functino i have and setting the criteria to match the task ID i am getting an error saying it is too complex. any ideas?
IT goes something like this
AdminTime: NZ(IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))/60)+(Nz([ExpenseHour]))+(Nz([ExpenseMinute]/60))
WHERE TaskID = 2
In My Db I have TblSupportTickets and a Table which records support ticket activity. When adding a new activity detail to the Db it automatically records when the FrmActivityDetail opens and on closing, it records the time the Form was closed. So in TblSupportTickets which relates by ID to TblSupportTicketActivity on a one-to-many relationship.
I then created a query with SupportTicketID, TimeOpen, Time Closed. SupportTicketID criteria is Forms.FrmSupportTicket.txtSupportTicketID
This lists all the time open's and timeclosed's for that support ticket.
how can I work out the total time spent on that support ticket?
I have a database consisting of two tables. One is "articles" and the other is "tasks". To put it simply, I would like to find how much time the article spends in tasks, but one article can have many tasks, and they often (but not always) overlap. Tasks have a start and end date field.
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..
I have been trying to help a user calculate elapsed time. I referenced the GetElapsedTime from the following Microsoft article
http://support.microsoft.com/kb/210604/
It displays ok on a form or inserting on the detail in a report like it suggests but the user would also like to total up the elapsed time on the report. Not sure how to do that with the way this function is built.
I have two time/date fields, have subtracted one from the other, and get the number of hours and minutes. From there the data is totaled in a report.
The problem is when the total hours goes beyond 24 hours, the additional time is not added. (eg. if you have 23:30 and you added 5 more hours it does not come out to 28:30):mad: :confused:
I am trying to put together this report where I can see how many hours the printers were running in a month's time. The sum property for this is working up until it reaches 24 hours then it starts all over again. How can I get it to keep adding hours when it gets to 24?
Hi, 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.
I have a table in access that has 1 ) persons ID and ) log date/time 3) direction in or out
I need to calculate the amount of time spent by the person in the office. e.g User with ID 1 will come in at 8 in the morning and go out after an hour then come in again and go out. Till he leaves out for the day. I need to find the first time he came in and last time he went out and find the difference for the hours he worked
I have to create a query in access that will calculate two Date and time fields [Date & Time Left]/ [Date Returned], need to figure out between the two fields. Trying to identify when the rep returned the call and the number of business hours (6:00am - 4:30pm) it takes to return a message in Ms Access 2010.
The only issue is the calculation has to be done by time and so I have to calculate what time they left the message(so the difference between [Date & Time Left] and [Date Returned) [Date & Time Left] and when the rep returned the message which is suppose to be [Date Returned] but the problem with this field is the data entry is in date format (10/9/2014, 00/00/0000) of Date and not Date and Time like the [Date & Time Left] field, so I don't know what to do now. Not sure what to do now not a database that create or have allot of control over.
I have a question about summing information on a report. I am developing a report to see have percentage of time Staff work within a certain area. On the data base the time percentages entered as ranges e.g. 75-100%, or 11-25%. We needed to figure out the total percentage of time so I created a report to add the total time. I used a text box which it titled FTE_Total if embedded iif functions to display the total time. Here is the function I used:
This worked perfectly however I cannot get the FTE_Total fields to sum. I tried the following function in the group footer: =Sum([FTE_Total]) but everytime I try to look at the report in report view I get a message asking for the FTE_Total Parameter.
Any way of setting up a table containing the following date/time fields.
StartDate StartTime EndDate EndTime
Ultimately I will need to be able to subtract these date/times to get a total time between the two. Should I combine the start date and times in one cell or keep them separate.
In either scenerio, how do I subtract the two in a query for a report?
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).
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 ?
I have finger print machine and i already connect to it and get all log.then i tray to get data and here is the code that im using
SELECT Format(CHECKINOUT.CHECKTIME,"dd/mm/yyyy") AS CDate, IIf([CHECKINOUT.CHECKTYPE]=I,Format(CHECKINOUT.CHECKTIME,"hh:nn:ss ampm")) AS StartTime, Format(CHECKINOUT.CHECKTIME,"hh:nn:ss ampm") AS EndTime, USERINFO.USERID, USERINFO.Name, CHECKINOUT.CHECKTYPE FROM CHECKINOUT INNER JOIN USERINFO ON CHECKINOUT.USERID=USERINFO.USERID WHERE (((Format([CHECKINOUT].[CHECKTIME],"dd/mm/yyyy"))='10/04/2014'));
O.K, I am really trying to figure this out from other postings but my limited query experience is limting my understanding of the other posts.
I only know how to use the query wiz and then a bit in design mode.
I have a Query
[ID]pk [Contest Name], [Score]
There will be many records for the same [Contest Name] in the underlying table. Therefore i want to sub total by [Contest Name] so i can then create a report. I have created the report perfectly using the Report support in another posting submitted. But the report does not allow me to order the results as the sum calc is a function on the report. Therefore I am now exploring the Query Sum [Score] order by [Contest Name].
I just need it in a Qery for dummies format.
Go into design mode and put the following in what portion of the query on what line.
I have a query which returns charge_cost (based on course cost, whether it went ahead, if hospitals are eligible for charging etc) which is then used in an existing report.
I want to make another report which simply is:
Total training spend for 2004-2005: £1276.04
And i just want that to be the SUM of charge_cost.
I cant work out how to do this - i did a new query including charge_cost and then created a new field called total_spend: sum([charge_cost]) but i keep getting the message "You tried to execute a query which does not include the specified expression charge_cost as part of an aggregate function"
hmmm... found another little problem. I've got a list of ingredients with cost and amount from a table, total cost per ingredient is simply amount times cost, how would I go about getting a recipe total by summing the seperate ingredient totals?
is there a way to calculate/store total cost value of qty x unitcost in total cost field of same table. if it requires a query, would appreciate guding me how to write it.;) ;)
I again ran into something that I can't figure out.
I have a table:
Date Time FirstName LastName SSN InAmount OutAmount
I need a query to sum up the InAmount and OutAmount into one total based on the SSN. This query is placed into a form that is then placed onto another form. The form is to alert the user if the amount of the Inamount and Outamount of a unique SSN totals above $10,000.01 on the current date.
So for example if on 01/01/07 if SSN=111-11-1111 has an Inamount of $5,000.00 and an OutAmount of $5,000.01 thus totaling $10,000.01, then the person's name will appear on the form list. This will change/clear when the date is 01/02/07.
Hi, I am trying to write a query that will total the number of fields that have matching values. For example i need to have IP addresses added into the table via a form, that bit is done but i need to create a query that will count how many times an individual IP address is added to the list. So that on the report i can show the list of IP address and instead of showing duplicates it will show how many times it has been added to the table.
Any help on how i could do this would be greatly appreciated.
Does anyon ehave any experience of running totals in an access query. I'm reporting the data through excel not access reports so need a query not a report solution..
What I would like is to have an additional column which keeps a monthly summary of spend based on running total month 1to 12. All items have months 1 - 12 and are ordered in that fashion.
