Results 1 to 3 of 3
  1. #1
    leeamra is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    1

    I want to identify the data in an interval of time between two days

    I have two tables, the first is the data table about the stock price per minute. It is composed of six columns, the date and time, the opening price, the highest price, the lowest price, and the closing price.

    Table Data

    `ID Field1 Field2 Field3 Field4 Field5 Field6
    2 2016.02.17 11:36:00 PM 1.42918 1.42924 1.42904 1.4292
    3 2016.02.17 11:37:00 PM 1.42919 1.42922 1.42886 1.42912
    4 2016.02.17 11:38:00 PM 1.42911 1.42917 1.42902 1.42917
    5 2016.02.17 11:39:00 PM 1.42918 1.42918 1.42887 1.42904`

    The second table consists of seven columns, the date and six different times.

    Table Time
    `ID Field1 Field2 Field3 Field4 Field5 Field6 Field7


    2 2016.01.02 5:35:00 AM 6:58:00 AM 12:25:00 PM 3:30:00 PM 5:52:00 PM 7:22:00 PM
    3 2016.01.03 5:35:00 AM 6:58:00 AM 12:25:00 PM 3:30:00 PM 5:53:00 PM 7:22:00 PM
    4 2016.01.04 5:36:00 AM 6:59:00 AM 12:26:00 PM 3:31:00 PM 5:53:00 PM 7:23:00 PM
    5 2016.01.05 5:36:00 AM 6:59:00 AM 12:26:00 PM 3:31:00 PM 5:54:00 PM 7:24:00 PM`

    I do the work of four queries to get the stock price in a specified period from the second table showing the date, the opening price, the highest price and its time of achievement, the lowest price and time and its time of achievement, and the closing price.

    Quiries 1

    `SELECT First(Times.Field1) AS [Date], First(Times.Field2) AS FirstOfField2, First(Times.Field6) AS FirstOfField6, First(Data.Field3) AS [Open], Max(Data.Field4) AS [Max], Min(Data.Field5) AS [Min], Last(Data.Field6) AS [Close]
    FROM Data, Times
    WHERE (((Data.Field1)=[Times].[Field1]) AND ((Data.Field2) Between [Times].[Field2] And [Times].[Field6]))
    GROUP BY Times.ID;`

    Result 1
    `Date FirstOfField2 FirstOfField6 Open Max Min Close
    2016.02.19 5:29:00 AM 6:22:00 PM 1.43217 1.4337 1.42461 1.42653
    2016.02.22 5:28:00 AM 6:23:00 PM 1.42744 1.42892 1.40567 1.41282
    2016.02.23 5:27:00 AM 6:24:00 PM 1.4133 1.41527 1.40615 1.40813`

    Quiries 2

    `SELECT First(Result.Date) AS [Date], First(Data.Field2) AS MaxTime, First(Result.Max) AS MaxValue
    FROM Data, Result
    WHERE (((Data.Field4)=[Result].[Max]) AND ((Data.Field1)=[Result].[Date]) AND ((Data.Field2) Between [Result].[FirstOfField2] And [Result].[FirstOfField6]))
    GROUP BY Result.Date;`

    Result 2

    `Date MaxTime MaxValue
    2016.02.18 3:21:00 PM 1.43938
    2016.02.19 8:39:00 AM 1.4337
    2016.02.22 8:37:00 AM 1.42892`

    Quiries 3

    `SELECT First(Result.Date) AS [Date], First(Data.Field2) AS MinTime, First(Result.Min) AS MinValue
    FROM Data, Result
    WHERE (((Data.Field5)=[Result].[Min]) AND ((Data.Field1)=[Result].[Date]) AND ((Data.Field2) Between [Result].[FirstOfField2] And [Result].[FirstOfField6]))
    GROUP BY Result.Date;`

    Result 3

    `Date MinTime MinValue
    2016.02.18 10:01:00 AM 1.42558
    2016.02.19 4:32:00 PM 1.42461
    2016.02.22 3:40:00 PM 1.40567`

    Quiries 4 (Final)

    `SELECT First(Result.Date) AS [Date], First(Result.FirstOfField2) AS FirstOfFirstOfField2, First(Result.FirstOfField6) AS FirstOfFirstOfField6, First(Result.Open) AS [Open], First(Result.Max) AS MaxValue, First(Max.MaxTime) AS MaxTime, First(Result.Min) AS MinValue, First(Min.MinTime) AS MinTime, First(Result.Close) AS [Close]
    FROM Result, [Max], [Min]
    WHERE (((Result.Max)=([Max].[MaxValue])) AND ((Result.Min)=([Min].[MinValue])))
    GROUP BY Result.Date;`

    Result 4 (Final)

    `Date FirstOfFirstOfField2 FirstOfFirstOfField6 Open MaxValue MaxTime MinValue MinTime Close
    2016.02.18 5:30:00 AM 6:22:00 PM 1.42989 1.43938 3:21:00 PM 1.42558 10:01:00 AM 1.43572
    2016.02.19 5:29:00 AM 6:22:00 PM 1.43217 1.4337 8:39:00 AM 1.42461 4:32:00 PM 1.42653
    2016.02.22 5:28:00 AM 6:23:00 PM 1.42744 1.42892 8:37:00 AM 1.40567 3:40:00 PM 1.41282
    2016.02.23 5:27:00 AM 6:24:00 PM 1.4133 1.41527 11:41:00 AM 1.40615 5:47:00 PM 1.40813`

    The problem that I am facing, when I want to identify the data in an interval of time between two days.
    Example: when I need to analyze the data in an interval of time between 11:00 pm on 17.02.2016 and 05:00 am on 18.02.2016

    Thank you
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,365

  3. #3
    sneuberg is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Posts
    23
    I am going to assuming that if you can get the data between the dates and times you want you know how to crunch it to your needs. To get the data it's probably easiest to convert the date and time to a Date/Time. You can do this with DateSerial to get the date and then just add the time to it. So the expression is:

    Code:
    DateTime: DateSerial(Left([Data]![Field1],4),Mid([Data]![Field1],6,2),Right([Data]![Field1],2))+[Field2]

    In the attached database I put this in qryData with the other fields and the SQL is

    Code:
    SELECT DateSerial(Left([Data]![Field1],4),Mid([Data]![Field1],6,2),Right([Data]![Field1],2))+[Field2] AS [DateTime], Data.Field3, Data.Field4, Data.Field5, Data.Field6, Data.Field7
    FROM Data;

    You can use this query instead of the Data table for you starting point. I've made a query that gets the data in an interval of time between 11:00 pm on 17.02.2016 and 05:00 am on 18.02.2016. The SQL for that is

    SELECT qryData.DateTime, qryData.Field3, qryData.Field4, qryData.Field5, qryData.Field6, qryData.Field7
    FROM qryData
    WHERE (((qryData.DateTime) Between #2/17/2016 23:0:0# And #2/18/2016 5:0:0#));

    If you want to feed this query dates in the format yyyy.mm.dd you can use the expression given early to convert them to american dates.

    I suggest you use better names. Field1, Field2, etc will probably confuse you sooner or later.
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-25-2014, 11:32 AM
  2. Replies: 7
    Last Post: 07-22-2014, 12:33 AM
  3. Time interval calculation
    By gar in forum Queries
    Replies: 3
    Last Post: 04-09-2013, 02:05 PM
  4. Replies: 6
    Last Post: 03-30-2013, 05:42 AM
  5. Replies: 1
    Last Post: 02-12-2013, 03:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums