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

[leeamra]

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

[orange]

cross posted http://www.access-programmers.co.uk/...d.php?t=285106

Cross posted www.utteraccess.com/forum/index.php?showtopic=2035350

See this for info regarding cross posting.

[sneuberg]

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.