Queries :: Time Series Calculation In Access?

May 23, 2013

Let's say that you have a cookie jar that's to be shared with two children, let's call them Jack and Jill.

You know that to start with (t = 0), there are 100 cookies in the cookie jar

Now the strange thing about Jack and Jill is that they start eating cookies on different days and that their cookie appetite depends on the number of cookies in the jar when they start, Specifically

Jack has an appetite of 10% of the total cookie jar and he starts eating on day one.

Jill eats after Jack and she has an appetite of 5% of the jar.

So what this would look like is:

Day 0 - Cookie Jar has 100 cookies
Day 1 - Jack eats, Cookie Jar has 100 - 100(10%) = 90 cookies
Day 2 - Jill eats, Cookie Jar has 90 - 90(5%) = 85.5

Problem:

1. Given that I have a table containing the starting number of cookies e.g.:

CookieTable
Startingcookies
100

2. Given that I have a table containing a record for Jack and a record for Jill, each with two fields: Eating Start date, Appetite % e.g.:

AppetiteTable
Child, EatingDay, Appetite%
Jack, 1, 10%
Jill, 2, 5%

What will the query look like that:

1. Calculate the number of cookies in the cookie jar over time e.g.

Day 0, Day 1, Day 2
100, 90, 85.5

2. Calculate the total number of cookies eaten by Jack and Jill e.g.

Child, Cookies Eaten
Jack, 10
Jill, 4.5

View Replies


ADVERTISEMENT

Queries :: Unique / Distinct Result In Time Series

Oct 2, 2014

I have been struggling with understanding Queries related to Time Series. I find it difficult to get the right answer or understanding how to ask the right question Access style.

Table below is a sample data/table example for my question. The intent is to build a query that will return the latest date for each unique/Distinct name. I have Time Series Table that I haven't been able to do this, it doesn't return with Distinct Name. I Also have included a sample database.

TABLE
Name Date_1 Distance
------- --------- -----------
A 1/1/2014 10
B 1/3/2014 5
C 2/1/2014 10
A 1/3/2014 16
A 2/5/2014 3
B 5/1/2014 5
C 6/2/2014 6
C 7/1/2014 7

ANSWER SHOULD BE.
Name Date_1 Distance
------- --------- -----------
A 2/5/2014 3
B 5/1/2014 5
C 7/1/2014 7

View 2 Replies View Related

Queries :: Time Calculation Using Current Time?

Oct 9, 2014

I need a Select Query to display data on a form. When an order is appended to my table the field named Printed is updated with the time it was appended. When my form opens it needs to display the field Printed and a field I call MinutesFromPrint. This field needs to display the total minutes elapsed from the time in the Printed field to the current time. My expression is not working.

MinutesFromPrint: DateDiff("n",[Printed],Now())

Here are a couple of samples of my results when I run the query at 7:49 A.M.:

Printed MinutesFromPrint
2:35 60366554
5:07 60366402

View 11 Replies View Related

Queries :: Time Calculation Query

Aug 6, 2014

I am needing a query to calculate elapsed time in business hours for each record selected (I normally base this on a date range). For the purposes of this query, business hours are defined as Mon-Fri from 7 AM until 9 PM.

So for example:

With a start time of 6:45 AM and an end time of 9 AM, the query would need to return 02:00 (in [h]:mm format).Likewise, with a start time of 7 AM and end time of 9 AM the query would also return 02:00.Is there any way to do this easily? Or at all for that matter? Is it possible to deal with weekends?

View 5 Replies View Related

Queries :: Date Time Calculation From Strings

Oct 9, 2014

I have four columns: date1, time1, date2, time2. All are strings.I want to concatenate date1 & time1. Then date2 & time2.Then I want to do the following calculation and the answer to expressed as number of days:

datetime2 - datetime1.

I am using MS Access 2007 and the results are being grouped by a different variable. Ideally I want to complete this action as one complete statement in the SELECT statement of my query. This is very easy to do in Excel but I'm baffled by MS Access!

View 2 Replies View Related

Creating Time Series For Portfolio Values

Sep 3, 2007

Dear All,

once again I need your help, I have no idea how to tackle the following problem. I am taking records of stock market transactions. As a final result I want to have a query which gives me for every end of the day the total value of my portfolio.
Therefore I have two tables. The first one, called tblTransactions with columns - among others - Date, Ticker, Quantity, Price does contain my transactions. The second one, called tblQuotes with columns Date, Ticker, LastPrice contains prices for each security traded for every day.
The following SQL code gives me the portfolio holdings with the respective LastPrices on an arbitrarily chosen date (03/09/07):


SELECT T.Ticker, sum(T.Qty) AS TotQty, Q.qCl AS [Last Price], (T.Qty*Q.qCl) AS [SubTotalValue]
FROM tblTransactions AS T INNER JOIN tblQuotes AS Q ON T.Ticker=Q.qTicker
WHERE T.Date<=#3/9/2007# And Q.qDate=#3/9/2007#
GROUP BY T.Ticker, Q.qCl, (T.Qty*Q.qCl)
HAVING sum(T.Qty) >0
ORDER BY T.Ticker;


This works so far. However, now I struggle with the next step. I want to have a query, which for each date (I could use the date column of tblQuotes) adds all SubTotalValues, i.e. gives me the total value of my portfolio for each day.

Could you give me please some hints on how to proceed from here? I would be very pleased if somebody could help me with that question.

Best regards
JapanFreak

View 4 Replies View Related

Queries :: Query Calculation Based On Current Time

Apr 24, 2013

I have a database that has 2 forms. After submitting the first form, the user should complete the second form within 24 hours. The first form stores the Date/Time the form was submitted. I want to be able to run a query and have a column in the query that is "Time Remaining". In non-technical terms, this column would be: Date/Time form submitted + 24 hours - Current date/time.

View 3 Replies View Related

General :: Identify Cycling / Fluctuations In A Time Series Data?

Mar 12, 2013

I am trying to find an algorithm to identify patterns in my data.

My task is to accomplish whether the data shows a very sharp decline and whether or not it follows previous fluctuation.

If it declines sharply and doesn't follow previous fluctuations it will indicate a production problem.

My time series data is as follows.Also sharp decline according to the below data is highlighted.

Data
-0.027663709
-0.057051957
-0.077941988
-0.070009989
-0.033860193

[code]....

View 2 Replies View Related

Forms :: Set Up A Series Of Controls Then Bind To SQL Queries?

Jul 10, 2014

I have about 12 tables in a 2010 database and I want to create a single summary form showing key details of the database. Most of these will be count fields using various subset of the tables e,g. total members, count of new members this year, count of those not attending a meeting etc. More than one table will be represented on the form.How would I set up a series of controls that I then bind to SQL queries? Or is this not the way to do it?

View 8 Replies View Related

Modules & VBA :: Way To Check If Series Of Action Queries Can Be Run

Mar 4, 2014

Is there a way to check if a series of action queries can be run (without any error) before actually running the queries?

View 1 Replies View Related

Queries :: Create Series Of Records Automatically

Jul 5, 2013

I have a table in access which captures a couple of bits of information. The database is for tenant management and payment records. This is what i am trying to achieve:

In one table I record payment information, when they paid, and how much they paid.

In the lease table I capture information such as first pay date, and the payment schedule, whether this be weekly, monthly, fortnightly etc.

I would like to create a table of sorts which has the next twelve months worth of payments dates. Then when i add a payment to my payment table it will match it up with the correct payment date. This will allow me to track arrears and missed payments.

View 3 Replies View Related

Queries :: Creating Particular Calculation In Access 2010 Query

Dec 9, 2013

I have played with this problem for 3 days and have come close but not quite solved it. My problem, I have several drivers delivering several orders, the orders are named 101, 102 and so on lets say to 150. Due to locations of the drivers, some deliver more orders then others. I want to be able to create a report that looks like

"Driver #1 101 - 106"
"Driver #2 107 - 110"

Driver 1 delivered 6 orders. Driver #2 delivered 4 orders and so on.

I have tried the 'count" which gives me the number of orders per driver but having trouble figure out the design of the calculation in the query.

View 2 Replies View Related

Queries :: Converting CSV File Into Text Format - Export Records With Specific Series And Date

Jun 7, 2015

I have a CSV file and want to convert it in a text format with some filtered data and with some formatting. This is an everyday task for me. So I made a table and imported the data in to it by the command :

DoCmd.TransferText acImportDelim, "fo Import Specification", "fo", FileName:="C:UserswelcomeDesktopfo.csv", HasFieldNames:=True

Actually I have a column "SERIES", contains various series like "EQ", "BE", "DR", "BZ", "D1" and so on. And one more column with the dates having 4 / 5 current months dates and one next months date and one next to next month's date. And every date has got several thousand records.

now the issue is that : After importing these several thousand records, I want to export it but with a specific date and with a specific series.

The other thing is that, these dates change every month so if hard coded, the problem will occur the next month.

I use this code for export :

DoCmd.TransferText acExportDelim, "NewFnoSpec", "fnoquery", "C:UserswelcomeDesktopFO Output.txt", True

this code is working fine but when the month will change, the code won't work.

Can we have a date & series picker attached to this query, so it can export the records with the specified SERIES & DATE.

I tried putting a textbox on the form named TxtDate and in a Query ( Design mode ) under the date column, in criteria I have put [Forms]![Futures]![TxtDate] and after putting this line, the query becomes empty and no data is there.

View 14 Replies View Related

Time Calculation

Jan 25, 2006

i have two date/time fields

sdate (start date) 01/01/2006 14:30 - format(dd/mm/yyyy hh:mm)
edate (end date) 02/01/2006 14:31 - format(dd/mm/yyyy hh:mm)

i need to get answer in HH:MM (24:01)


pls help me

thnx best regards
ibu

View 4 Replies View Related

Time Calculation

Jan 25, 2006

i have two date/time fields

sdate (start date) 01/01/2006 14:30 - format(dd/mm/yyyy hh:mm)
edate (end date) 02/01/2006 14:31 - format(dd/mm/yyyy hh:mm)


i need to get answer in a textbox as 24:01 (hh:mm)


pls help me

thnx best regards
ibu

View 2 Replies View Related

Time Calculation

Feb 8, 2006

Hi all,


I'm a beginner, and i want to know how to calculate time difference. For ex, if i substract 23:45 of 02/09/2006 with 00:10 of 02/10/2006 then i get a - "ive" value... Please somebody help me with a vba code for this...

thanks
Senthil.

View 1 Replies View Related

Time Calculation

Dec 4, 2006

Can someone please help me as this is driving me crazy and I know the answer must be simple.

I have a query/report that shows the duration a call has taken i.e. it times each part of the call. For example it may take a member of staff 00:00:30 to answer a call, then he puts a customer on hold whilst he speaks to another department which could be another 30 seconds, and then he ends the call with the customer.

So my report and query shows:

Ringing 00:00:30
Speaking to customer 00:04:10
On hold 00:00:30
Ends call with customer 00:02:00

The duration of the action is in a seperate column.
How do I tell access to add these up so I get a total of 00:07:10 for the entire call from start to finish?

Thanks for you help

Rich

View 2 Replies View Related

Time Calculation

Nov 19, 2007

I am struggling with a filter I thought would be simple. I have a field called close time and I want a filter that will show a record one hour before the close time?

View 2 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 Time Calculation

Apr 9, 2006

Hello friends,

I have a table for "Production Orders" ie. work orders.

there are feilds : start date , start time

based on these feilds i want to calculate the remaining time to start production.

but what i want is

if remaining time is > 1 day then
give results in the format " 1 day and 2 hrs"

elseif remaining time < 1 day then
give result in the format " 2 hrs and 24 mins"

and also current time - remaining time > 0 else result = "N/A"



how can i do this simply

View 2 Replies View Related

Date / Time Calculation Help

Jul 13, 2006

I'm having trouble getting my head around this.
I have a database that collects stats of patients admitted to ICU.
I have a table PtDataTable (pk = PtID) with a one to many relationship with PtVisitTable (pk = VisitID, fk = PtID). The patient visit table has the fields AdmissionDate and AdmissionTime, and DischargeDate and DischargeTime.
I need to calculate if the patient is readmitted to ICU in less than or equal to 48hrs. In other words calculate the time elapsed between DischargedDate and DischargedTime in one record and the AdmissionDate and AdmissionTime for other records in the PtVisitTable for the same patient.
I'm not sure if I stated this clearly.
Any help is appreciated as always.

View 2 Replies View Related

How To Make Time Calculation

Nov 23, 2006

Hi.
I'am trying to count: todays date -12 month.
So how I tell access that I want answer which date is year from this date?
Or how I must "write" 12 months in SQL?

View 2 Replies View Related

Date & Time Calculation

Dec 19, 2007

Table
------
I have one field called [Time] in dd-mm-yyyy hh:mm format in table A.

I have another field called [Deadline] in text format (Mon 10:00) in table B.

Queries
-------
Need to build one queries that have the above two field, but with additional field called [PullDeadline] that the [Time] will look out the [Deadline] and return the actual time & date value in [PullDeadline]. No matter how the [Time] change, the [PullDeadline] will stick to [Deadline] and return the value in that particular week.

For example,

[Time] = 01-12-2007 05:00:00 AM
[Deadline] = Mon 10:00
[PullDeadline] = 26-11-2007 10:00 AM

How to handle, if the deadline is previous or next week?

Eg. [Deadline] = Sun 10:00

Then [PullDeadline] = 25-11-2007 10:00 AM instead of 02-12-2007 10:00 AM.

View 5 Replies View Related

Time And Date Calculation

Oct 15, 2006

OK so I have a form with the following fields which are all date/time type
apptTime
timerOn
pickupDate
reminder - combo box with 1 hour, 2 hour...etc up to 10 hours

so what happens is when user enters apptTime say 18:30 with pickupDate set for current days date 10/14/2006 then chooses 2 hours from the reminder combo. This then sets timerOn to (apptTime-2 hours) so it is 16:30. I have set a conditional formatting to change background color when "timerOn<=Time() and pickupDate=Date()" so user knows to dispatch this load. This all works great until you get to midnight.
If user sets apptTime to 00:30 with pickupDate to 10/15/2006 and chooses 2 hour reminder, so timerOn does get set to 22:30 and you would expect the conditional formatting to work once the current Time() is 22:30, but since the current Date() is 10/14/2006, the expression is false. I have tried the DateAdd such as DateAdd("d", 1, Date()) command in various ways but can't get it to work for "timerOn<=Time() and pickupDate=DateAdd("d", 1, Date())"...I mean it works because it adds 1 day to pickupDate and condition is then true, but it ignores the timerOn<=Time() part and formats even if I reset system time to 22:29. Can anybody give me insite on where the logic is wrong?

View 1 Replies View Related

Need Help With Time/ Hours Worked Calculation

Jan 22, 2006

Hi Guys

I need to create a method of calculating -week number,hours worked per week and a leiu hours total.

Explanation

Users enter their worked hours per day. I need to calculate the total hours worked per user per week and if this is greater or less than their contracted hours a record of this figure over a continual time period (or year).

Variables :- Time worked,Holiday hours,Leiu hours taken,Sick leave, Other authorised leave.

I realise that i'm asking on a lot but working for a charity has financial limits.

Attached the basic database-so far

Thanks in advance for your help

Row

View 4 Replies View Related

Date And Time Format Calculation

Jul 13, 2007

Hi,

I need to calculate the number of days between two days however the format on which i have my date includes the time in the same field -

2007-02-20 07:15:30.474

When I calculate one date minus an other the result is #error
I require the result to be in a count format e.g 2.3 days (like in excel)

Is there anyway to get around this without splitting the date and time?

Please help!:)

View 3 Replies View Related







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