Calculating Difference Between Two Fields?
Feb 22, 2007
I'm trying to create a database for a Cattery business.
One thing I'd like to include is a query that calculates how long a cat has been staying at the cattery and then calculating the amount owed by the owner.
I don't have a cost field in any of my tables, so I need to create this on every run of the query.
So for example
A Cat resides in the cattery from the 22nd of Feb to the 28th of Feb. So 6 days (not including the day the cat is brought into the cattery) at a cost of 4.95 a day equals 29.7
Heres what I have so far
http://www.tdh1987.co.uk/pics/query.JPG
Bear in mind I'm from the UK, so the date field is in a different format to yours.
Any help will be greatly appreciated.
View Replies
ADVERTISEMENT
Jun 27, 2005
In access in one field I have one date and in another field I have another date. I want to calculate the number of years.,months and days elapsed between these two date. e.g. suppose in one field I have 30.06.2005 and in other 20.04.2004 then it should show 1 year 2 months and 10 days.
View 14 Replies
View Related
Nov 12, 2007
Hi, I wonder if someone can help me with the following problem:
I have a table with 2 columns,start time and end time, both containing time values in a four digit format, eg 0930 being 9:30am etc.
I have made a query to convert these into the standard format (eg 0930 becomes 09:30), using left/right and & functions.
Now I use the timevalue function to convert the string , eg 09:30m into a recongnised time value.
The problem is now I want to find the difference between the start and end times- but when i try a simple end_time - start_time formule it returns a long number which i dont understand, rather than just giving me the difference betwen the two times.
Im sure there is a simple way to do this which I dont know, can anyone help me out? Thanks in advance!
View 1 Replies
View Related
Nov 12, 2007
Hi, I wonder if someone can help me with the following problem:
I have a table with 2 columns,start time and end time, both containing time values in a four digit format, eg 0930 being 9:30am etc.
I have made a query to convert these into the standard format (eg 0930 becomes 09:30), using left/right and & functions.
Now I use the timevalue function to convert the string , eg 09:30m into a recongnised time value.
The problem is now I want to find the difference between the start and end times- but when i try a simple end_time - start_time formule it returns a long number which i dont understand, rather than just giving me the difference betwen the two times.
Im sure there is a simple way to do this which I dont know, can anyone help me out? Thanks in advance!
View 1 Replies
View Related
Nov 12, 2007
I need help to calculate time difference from the record above where ID is same. I have attached an example in excel with this request where I have if statement doing exacly what I want to do in Access. I have a table in Access that has three columns - as below
ID, Reg, TimeDiff
66646, 14/06/2007 21:33,
66646, 15/06/2007 06:03, 8:30:00
66646, 15/06/2007 12:20, 6:17:00
66646, 15/06/2007 15:08, 2:43:00
67844, 14/06/2007 04:39,
67844, 14/06/2007 09:05, 4:26:00
67844, 14/06/2007 09:08, 0:03:00
67844, 14/06/2007 16:27, 4:09:00
If ID is equal to the ID in record above then TimeDiff is equal to reg minus reg in the above recored, else blank.
Hope someone can help me with this.
Thanks, GS
View 5 Replies
View Related
Jul 29, 2014
I'm trying to work out the difference between 2 records both of which have a call out date [bas start date]..basically the structure is
equipment number call number bas start date
12345678 112255 1/7/14
12345678 112256 3/7/14
What i'm after is the 4th column to work out the date diff... in this case 2 days the equipment can be multiple times so i might see equipment number 12345678 - upto 10 times with consecutive dates - all of which i need to know the difference between the current call date and the previous call date..
View 3 Replies
View Related
Nov 18, 2011
I currently have a table that has patient ID (unique record (SSN)) and a date of visit field. The SSN may have numerous rows with different dates of visits. What I am trying to do is a DateDiff function that will retrieve records that are 48 hours apart or 2 days apart. We are tracking patients that return within 48 hours of a previous visit.
View 10 Replies
View Related
Nov 5, 2006
I have created a database in Access where parents of students may come in and volunteer and receive free time credit from their employer (those companies who participate).
The database runs fine: Main table (demographics; student table; signIn/signOut table; and Volunteer Duty (a combo box that list all of the approved volunteer duties to qualify for the time credit) Every thing works perfect with the exception of my signIn/signOut form. After struggling with this for countless hours, I have settled for a manual entry of the signIn and signOut time, entered at the time of their start/stop times. I would like for it to be an automated function, but I have had such a difficult time up to this point I will settle for this method. What I NEED is to figure out how to make my "total vol hours today" field calculate the total volunteer hours at the time the parent "signOut".
If some of you Access MVP's can help with this I will be very grateful.
I awaite your answers, and THANKS IN ADVANCE.
signed: "head spinning like-a-top"
View 1 Replies
View Related
May 2, 2013
I'm trying to make a query where it can calculate the difference between a table field value and a query field value.
Ex. Table INVOICE - fields
invoice no, invoice value,
Query TOTAL - fields
invoice no total invoice value
I need to get the difference between invoice value and total invoice value.
How can I put this into a query (invoice value - total invoice value)?
View 4 Replies
View Related
Aug 12, 2015
I have a query that must calculate the different between values in two different records, based on the date.
The underlying ORIGVALUES table contains:
Date; Value; Diff
1/10/15; 100; (this is what I am trying to calc)
1/11/15; 101; (this is what I am trying to calc)
1/14/15; 102; (this is what I am trying to calc)
What is the best way to have each record calculate the selected date value divided by the prior date value)? E.g.
For 1/11 the math is 101 / 100 (so the 1/11/15 record would show the result of 1.01)
For 1/14 the math is 102 / 101 (1/11 to 1/14 represents a weekend)
I could copy the prior day values to the current day records and do that math easily but that is very redundant.
View 5 Replies
View Related
Oct 25, 2013
I have a list of client activity - client name, loan ID and loan date. I would like to create a new field that shows the number of days between one loan and the next. If I was doing it in excel, I would need to sort the data by client name and loan date and then calculate the number of days between the loan date of one loan and the loan date of the loan immediately prior to this.
View 14 Replies
View Related
Aug 8, 2006
I have a query that is looking to calculate staff absence.
Absence Start date & Return to work date
Looking to calculate number of days (not a problem) but to exclude weekends ie saturday and sunday ????
Not sure if this can be done --- any assistance would be greatly received.
thanks and regards
ian watson
Yarm Cleveland UK
View 10 Replies
View Related
Jun 23, 2014
I am starting to create a resourcing database that needs to be able to work with dates and times and perform calculations on them.
I need to work out the number of hours. I have two date/time fields Day_Start_Time and Day_End_Time. I want to calculate the difference between the two in hours and then multiply by the number of working days.
I tried this: Number_Of_Hours: DateDiff("hh",[Day_Start_Time],[Day_End_Time])*[Number_of_days]
But get #Func! when I run the query.
View 3 Replies
View Related
Jan 24, 2008
Hi, I am a longtime Excel user, and am now migrating some work to Access.
I have 2 fields dealing with dates in the format mm/dd/yyyy hh:mm, OPEN and CLOSE
I'd like to get the difference between the two in hours, but I have no idea how or where to do this in Access. I'm assuming it is put into the queries, but it would be easier if I could just put it in the table.
Here is an example
1/1/2008 1:00 1/2/2008 2:00
I want the result to be "25"
Thanks in advance!
View 9 Replies
View Related
Jul 20, 2013
i have a access table (AUTH) with following field
Company Auth Held
Tata 12
Dell 11
HP 21
Opera 11
Bangour 10
i used the following query to calculate the Held from a table named pers.
SELECT AUTH.company, AUTH.Auth, Count(AUTH.company) AS Held
FROM pers INNER JOIN AUTH ON pers.company = AUTH.company
GROUP BY AUTH.company,AUTH.Auth
then i got the result as under
Company Auth Held
Tata 12 02
Dell 11 08
HP 21 20
Opera 11 12
Bangour 10 12
now i want the difference between Auth and Held as Sur/Defi
how can be it done.
View 2 Replies
View Related
Aug 4, 2005
I have two tables - Hours and Employees. Employees table has these fields:
EmpID, and First, and Last name.
Hours table has these fields:
EmpID, Hours, Funding Source, Nature of Work, Pay Period
I need to produce a report or a query that will display these results:
A column with a list of all the employees and all the Nature of Work categories going across with the sum of hours for each Nature of Work. I'm attaching an Excel spreadsheet that shows kind of what I need. Also, these reports need to be produced for each Funding Source and
Pay Period, so something like [Enter Funding Source] and [Enter Pay Period]
My question is that I don't know how to make a report that will just list a column with all the names and count the sum of hours for each Nature of Work.
Hope this is not too confusing, and any help would be greatly appreciated. thank you!
View 8 Replies
View Related
Sep 25, 2006
Ohh Stress. Need Help....I have a mainform that contained two subforms in it. MainForm called "Employee", the two subform called "vac", and "Prevac". There is a field in my mainform called "Hours", that I'd like to use to Add with a field in Vac subform called "VacA" and a field in Prevac called "FHours". I'd like to display the total (sum of all three field on the same mainform. Is there anyone have any idea? Thanks in advance...
View 3 Replies
View Related
May 8, 2013
I have a database with two tables, one for the amount that was estimated in each cost section, and one for the actual amount billed for each cost section. The tables have the same number of fields, all with the same names. They can be linked together with event ID. Each table has over 100 fields and I would like to find the difference between what was estimated and what the actual was for each event. I would also like to see which cost section has the most and least variance. I am trying to do this without going through each cost and putting [tEst].[CostName]-[tActual].[CostName].
View 2 Replies
View Related
Aug 6, 2015
I have a Query contains field that is :
Code : AllNv: Concatenate("SELECT NAll FROM NormalsQ WHERE SerName='" & [SerName] & "'" & " ORDER BY NormID")
but [SerName] is a text type field that is not Primary Key.
I have in the FamilyTbl , [ServiceID] is numeric type field, and a primary key. I try :
Code ; AllNv: Concatenate("SELECT NAll FROM NormalsQ WHERE ServiceID='" & [ServiceID] & "'" & " ORDER BY NormID")
but this returns all [NAll] records. I use concatenate :
Code:
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ", _
Optional pstrLastDelim As String = "") _
As Variant
[Code] .....
View 14 Replies
View Related
Apr 22, 2013
I am trying to calculate the difference in dates between two fields. How do I find the difference in days between field one which contains the date 04/12/2011 and field two which contains the date 04/12/2013? I have tried to use the datediff function, but it keeps telling me it doesn't recognize the field name, even though the spellings correct.
View 3 Replies
View Related
Oct 26, 2014
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.
View 4 Replies
View Related
Oct 23, 2013
I want to add three fields in a table namely from, to and diff .
How to add them in a form so that I can enter
The time in (xx:xx) 24hr format in the "from" and "to" fields and calculate and save the time difference in minutes to the "diff" field automatically.
I am using access 2010.
View 8 Replies
View Related
Nov 10, 2005
i have two text boxes in a form:
1) Todays Hours
2) Total Hours
when i enter a number in text box todays hours i want the value in the text box total hours to increment by that amount. this operation will be done on a daly basis for a internet cafe where the user comes and i put in the hours. it seems simple but i really do not know. could someone help me please :confused:
View 1 Replies
View Related
Dec 20, 2007
Hi,
I am creating a form which has many fields which should have values in them. I need to know how to total all the fields across each field.
I have managed to do this(almost!) by creating a text box, and "on lost focus", putting the VBA:
TextBox = [SumOfConnection Bonus] + [sumofspice bonus] + [sumofvolume bonus]
(When I get this working I'll make a field in the table so there is no need for the text-box.)
This is basically, three types of commision that I want to total at the end in a "total". The amounts of commision are obviously different for each record but when I click in the "TextBox" it totals the values for the record that box is in ONLY, and all records show the same value in this field. I will click in a different box, and it will change, but ALL of them will change. How do a make it so that all "TextBox" field will show their own value.
Thanks,
View 2 Replies
View Related
Oct 3, 2006
I have a table for invoices that need to show Nett, VAT & Total. I have set up a form and have included a calculation on both VAT (Nett*17.5/100) and Total (Nett+VAT) which works well in the form. Because these calculations are in the ControlSource, I do not know how to have these fields bound to the original table.
Am I missing something really obvious???
View 1 Replies
View Related
Jan 4, 2005
I am new to Access and have made a databse using Access 2000. This is used for a dart league.
In one of the Tables called Members each "record" is made up of the persons details
(Name,address, etc). Included in the Row record I have "fields" (columns) that keep track of
the points each person hits on a nightly basis. I also have a Total Field.
Exampleof fields)
ID#,First Name, Last Name, Address, Phone #, Week 1, week 2, week 3, Total
How do I get the Total Field in the record to add up the nightly points in Week 1 + Week 2 + Week 3.?
In Excel it would be a simple calculation (e.g =sum(a1+b1+c1) etc.
As I said I am new to this database and it is very frustrating at the moment. I have used the help function
but it mostly double dutch to me.
ANY HELP in plain english would be appreciated
View 1 Replies
View Related
