Queries :: Query Time Difference AND Monthly Totals

Mar 21, 2013

I am VERY, and I mean VERY new to Access. I've been racking my brain all afternoon and googling like crazy. I just completed two levels of training on Access 2010 and have never worked with the program before. I already set up my tables and now I am on to querying. I have a table with several columns, two of which are "Start Time" and "End Time". I already created a query using the DateDiff function to calculate the time difference for each record. It output a new field with the time difference in hours. Now, I want to sum the totals of the time differences by month and I cannot for the life of me figure it out. My new query has Date (m/dd/yyy) and Hours.

View Replies


ADVERTISEMENT

Queries :: Create Query To Filter Customers When It Is Time Of Their Monthly Payments

May 21, 2014

I'm trying to make a query to filter or show only those customers when it is the time for their monthly payment.The query I have consists of four fields which are

1- Order ID
2- Payment
3- Date (Default value set to Date ())
4- Date for next time Payment (Default value set to date () + 30)

I made another field called "states". In this field I putted the following expression

Code:
IIf(([Date for next time Payment]-Date())= 0 "Should Pay";" ")

Then, I set the criteria for such field to "should pay" so that only customer "should pay" will appear in the query datasheet. However, this method has the following shortcomings:

1- It works only for one day (alert day) ,i.e., customers will filtered only when the expression is true.
2- Customers who have paid will still appear as a "should pay" until the day (the day that make the expression true) finish.
3- Customers whose pay late (maybe after 1 week) will disappear form the query datasheet after alert day finish.

To overcome the above shortcomings, i modified the expression to

Code:
IIf(([Date for next time Payment]-Date())<-1 And ([Date for next time Payment]-Date())>-15;"Should Pay";" ")

This method will extend the alert duration to 15 days, so the customers who haven't pay yet will appear in the query datasheet for 15 days. However, the customers who have payed will appear also, and that is the problem. The problem here is I can't compare the current payment date with previous one for one customer. method to create suitable expression, or even another query scheme ??

View 1 Replies View Related

Queries :: Calculate Time Difference And Show Result Under Certain Criteria In Query

Jul 9, 2013

ID Up Time Down Time
John 18:00 15:00
Kelvin 08:00 08:05
Melisa 23:00 02:00

This is the Table , i need the Query result show IF [Up Time]-[Down Time] is >=3 hrs

Like Below :

ID Up Time Down Time
John 18:00 15:00
Melisa 23:00 02:00

IDUp TimeDown TimeJohn18:0015:00Melisa23:0002:00

I tried DateDiff("h",[Up Time],[Down Time]) in Field but POP out with error "Syntax error (comma) in query expression 'Table1.[DateDiff("h",[Up Time],[Down Time])]' " ...

View 9 Replies View Related

Cumulative Monthly Totals?

May 31, 2006

Hi,

I'm trying to create an expression that will calculate cumulative monthly totals but my expression seems to only calculate totals for all months
e.g
MonthDirect DespatchesCum Direct Despatches
2006/031580 21867
2006/0410681 21867
2006/059606 21867

The expression I’m using is
Cum Direct Despatches: (Select Sum([Direct Despatches]) from QRY_DirectDespatches_ByMonth_ByModel)

The query should display the following results -
MonthDirect DespatchesCum Direct Despatches
2006/031580 1580
2006/0410681 12261
2006/059606 21867

I'm using Access 2002 on XP.
Can anyone please advise me where i'm going wrong?!
Thanks in advance for the response

View 3 Replies View Related

Queries :: Calculate Difference Between Two Date Fields To Get Total Time

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

[Query]Time Difference

Dec 30, 2007

Hi all, bit stuck on a query I'm working on.

What I have is a table with three columns: Name, Start, End (the last two being a start time and end time column in 24 hour format). What I need to do is calculate the time interval in minutes between the start and ending times (this has been done already and quite easily). What I find myself stuck on is when the ending time exceeds 00:00 and the start time is before this. IE: 23:30 start, 00:30 end, which is 60 minutes (duh). I wind up getting a 23 hour time.

Is there a way that I can code up a query (has to be a query as the rest of the DB results rely on the correct minutes per person)? Appreciate any help in this

View 5 Replies View Related

Reports :: Generating Totals In Monthly Report

Jun 1, 2013

I've got a simple invoice database with 10 amount fields, that needs to be summed up intoa Total including Gst box on a monthly report.

Report only has Date, Invoice Number And Total Amount fields on it, so I need to pull the information from table and sum it into Total Amount.

ie. = sum ( t1 +t2 + t3 + t4 + t5 + t6 + t7 + t8 + t9 + t10)
and sum above divided by 10% then added together to form total.

I'm just not sure how to do it, everything I've tried so far ain't worked.

Every example I've looked at is only doing maths with 1 field.

View 4 Replies View Related

Query Difference In Time From Multiple Records

Sep 8, 2005

I currently have a table with the following fields:

dtmDateintPressintCycleintProgramintLotysnAxialdtmFStartdtmFFinishdtmPStartdtmPFinishdtmDStartdtmDFinish

How might I go about querying the difference between dtmDFinish and dtmFStart for multiple records?

EX:

09/07/05-1-1-1-5681-NO-12:30-1:00-1:30-2:00-2:30-3:00
09/07/05-1-1-1-5681-NO-3:30-4:00-4:30-5:00-5:30-6:00
09/07/05-1-1-1-5681-NO-6:15-6:45-7:30-8:00-10:30-11:00

Would return two results: difference for 3:30-3:00 = 30 minutes and difference for 6:15-6:00 = 15 minutes

Thanks for your help!

View 6 Replies View Related

Queries :: Aggregate Sales Totals Within Specific Time-frame?

Mar 23, 2013

stuck with this problem on trying to calculate the aggregate sales totals of a product within a specific time frame. The query that I have built instead divides each sum by date, where it should be grouped according to product instead.

Here are some screen shots as to how it looks in Access. [also see attached ZIP if you don't want to unbreak links ]

[URL]

As you can see, the PRODUCT_ID column is not combining together according to their IDs.

[URL]

This how my Design View looks.

The SQL for my current query is:

SELECT PRODUCTS.PRODUCT_ID, Sum(SALES.SALES) AS SumOfSALES, SALES.TRANSDATE
FROM PRODUCTS INNER JOIN SALES ON PRODUCTS.PRODUCT_ID = SALES.PRODUCT_ID
GROUP BY PRODUCTS.PRODUCT_ID, SALES.TRANSDATE
HAVING (((SALES.TRANSDATE)>=#9/1/2008# And (SALES.TRANSDATE)<=#12/31/2008#));

View 3 Replies View Related

DateDiff In A Query - Calculate Time Difference Between Two Columns

Jan 22, 2012

DateDiff in a query

I am trying to calculate time difference between two time columns as follows in a query which has a few inner joins

Diff: DateDiff("h",[Table1.TimeStrat],[Table1.TimeStrat])

This gives problem, it is asking [Table1.TimeStrat] to be entered, where is the problem?

View 4 Replies View Related

Date/Time Query And Totals

Jan 6, 2005

I have a query that will return a total number of hours
between to specific dates. I would also like to have that
same column return the total number of ours regardless of the
dates. Can anyone please help me?

View 4 Replies View Related

Queries :: Monthly Unpaid Students Finder Query?

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

Queries :: Query Count Difference Of Two Fields?

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

Queries :: Calculating Difference Between A Table And Query?

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

Help With Recurring Monthly Payments And First Time Fee

May 31, 2007

Hello,

I have a database for rental units and i have two problems that i cannot solve. I have a table with 4 different options of units with different prices. what i would like to do is for the first time customers there is 1 time fee, how would i be able to charge them only 1 time and no charge after that.

My second question would be i have a payment form also and lets say a person joined today and has to pay 40 for monthly rental and 20$(the 1 time payments) which comes to 60$, and the person pays it in full so the balance is 0$, how would i set it up that a month from now, the 0$ will turn again to 40$ so the person has to pay again?

And also would i be able to set up a late charge if they dont pay on the day that they are supposed to?

Thanks for all the help....

View 14 Replies View Related

Queries :: Create A Query That Will Count The Days Difference Between Two Dates

Jul 3, 2015

I am using Access 2013.I am trying to create a query that will count the days difference between two dates. The dates are in the same field. I want to group by Region.So:

tblRegion = RegionID
tblStatus = StatusDate

I know how to use the DateDiff when it is two different fields, but I can't figure out how to do it from the same field.

View 7 Replies View Related

Time Difference

Jan 30, 2006

This has probably been raised before but here goes anyway.

I have two fields in a table: time_in and time_out, both as date/time fields and formatted for short time with a mask in the form.

I need to calculate the time spent, i.e. time_in 10.00, time_out 12.15 therefore 2hours 15minutes.

any help much appreciated.

Jim

View 1 Replies View Related

Time Difference Calculation

Mar 8, 2006

Hello:

I have a report with three text boxes:

Box 1 contains an employee's total weekly hours in hours and minutes: 40:00
Box 2 contains the actual total hours worked for the week: 35:30
Box 3 is the difference between Box 1 minus Box 2

I tried all the date/time functions to calculate the difference between Box 1 and Box 2 and none of them worked. I keep on getting an error message. Does anybody know how to calculate the difference between two sets of time (in hours and minutes)?

Thank you in advance.

View 2 Replies View Related

Date And Time Difference

Jan 12, 2007

I have four fields

Date Sent (date) Time Sent (Time) Date Recvd (date) Time Rcvd (time)

I want to know how many hours have elapsed between date and time sent and date and time rcvd


Any Ideas please as I am having a blonde moment

View 14 Replies View Related

Time Difference Help Needed!

May 25, 2005

Hi all,

I've just started a new job and one of the things they have got me doing involves getting some extra reports out of some access databases they have for the phone system. I've managed OK so far but I'm stuck on regarding phone diverts.

Basic jist is, when reception is unmanned they put the phone on divert and when they come back they take it off again. Simple and this info is recorded in the log file which is pulled into access each night.

The problem is all of the information is kept in a single table which I run a query on to get the info to look like below. The info is exactly as it is in the table, just that I have limited it to the reception user and where the func is like *54* or *55*:-

DateTimeUserFunc
03/05/200511:40:00Reception*54*200#
03/05/200511:47:00Reception*55*200#
04/05/200511:35:00Reception*55*200#
04/05/200511:40:00Reception*54*200#
04/05/200511:59:00Reception*55*200#
05/05/200509:57:00Reception*54*200#
05/05/200510:08:00Reception*55*200#
05/05/200512:10:00Reception*54*200#
05/05/200512:17:00Reception*55*200#
05/05/200512:19:00Reception*55*200#

The FUNC *54* means put on divert and *55* is off divert. What I need to figure out is the time between each of *54* and *55* and total them for all the data in the table (we have a table for each month). The problem I can see here is that occasionally the receptionist will put in the *55* first which doesn't mean anything so this bit would need to be ignored as only bits where its a *54* followed by a *55*.

Hope this is self explaintory and that someone is able to help. I have attached a CSV fle on the table so you can have a play with it if you can help.

Thanks in advance!

Regards,

Andy Roberts

View 7 Replies View Related

Calculate Time Difference

Aug 3, 2005

Hello,

I,ve made the following date fields in a form: begintime, endtime. I've also made a field Diff that calculates the difference between the two by the code : Diff = Format([begintime] - 1 - ([endtime], "Short Time").
Bij this code you can calculate the difference even over night.

But now I've got to calculate the part of the time someone worked between midnight and 06:00AM if that person indeed worked in those hours.

e.g. someone started at 16:00 end ended at 03:30. The part of time I need = 3:30
e.g. someone started at 01:00 end ended at 11:00. The part of time I need = 5

How can I accomplish this? I hope someone can help me because I'm :confused:

Thnx Remco

View 4 Replies View Related

Date Time Difference

Jan 5, 2007

I'm trying to find the difference between 2 dates [Start_Date] and [End_Date], however I want to strip out out of hours work, which is [OOH_START] eg, 17:30 and [OOH_END], eg, 08:00. Also I need to strip out the weekends as well.

Can you help?

View 5 Replies View Related

Calculate Time Difference

Oct 25, 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.

Code:
SELECT [Message Tracking_tbl].[Date & Time Left], [Message Tracking_tbl].[Date Returned], [Message Tracking_tbl].ID
FROM [Message Tracking_tbl];

View 14 Replies View Related

Queries :: Totals In A Select Query

Jul 28, 2014

After you "group by", is there a way to have the next field presented without a sum or avg etc.. if I know there is only ever one value to present?

View 1 Replies View Related

Queries :: Last Function In Totals Query

Apr 26, 2013

I'm trying to build (a rather simple) totals-query, but the "last" function doesn't give me the right values.Suppose I have a database where the amount of products that were sold are stored per month. It's possible that some products are not sold in some months. Now, I want the LAST REAL VALUE (= amount) for each product, no matter what month :

prod1 = 4
prod2 = 3
prod3 = 3

How can I do that, because the LAST-function gives me
prod1 = (empty)
prod2 = 3
prod3 = (empty)

View 6 Replies View Related

Queries :: Summing Query Totals Together

Mar 17, 2014

I have a number of querys which search by month and year, I want to be able to sum up 3 of the querys together and display this in a text box on a form... I have tried dsum etc but I can't seem to do it...

So basically I want the totals which are dsumed into the same form to calculate together in a different query if required... This would be simple except one of the months if from the previous year...

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved