Create Histogram Query from complex query

**EricRex** · 03-12-2025, 08:54 PM

Hey guys,

Here's another data analysis problem with intraday stock data using 3-minute bars. My stock data has date/time, high, low, and a couple of other non-relevant fields.

I created a query that returns the high and low of the day for each day in the series:

Code:

SELECT A.IntraDayDate, TIMEVALUE(B.BarDate) AS HighTime, A.IntraDayHigh, TIMEVALUE(C.BarDate) AS LowTime, A.IntraDayLow
FROM ((SELECT DATEVALUE(BarDate) AS IntraDayDate, MAX(high) AS IntraDayHigh, MIN(low) AS IntraDayLow FROM DIA_3min WHERE TIMEVALUE(BarDate) BETWEEN #7:30:00 AM# AND #1:57:00 PM# GROUP BY DATEVALUE(BarDate))  AS A INNER JOIN DIA_3min AS B ON (A.IntraDayHigh = B.high) AND (DATEVALUE(B.BarDate) = A.IntraDayDate)) INNER JOIN DIA_3min AS C ON (A.IntraDayLow = C.low) AND (DATEVALUE(C.BarDate) = A.IntraDayDate)
WHERE TIMEVALUE(B.BarDate) BETWEEN #7:30:00 AM# AND #1:57:00 PM# AND TIMEVALUE(C.BarDate) BETWEEN #7:30:00 AM# AND #1:57:00 PM#
ORDER BY A.IntraDayDate;

A snippet of its output is:

IntraDayDate	HighTime	IntraDayHigh	LowTime	IntraDayLow
11/1/2023	1:33:00 PM	$333.36	7:42:00 AM	$330.10
11/2/2023	1:54:00 PM	$338.52	7:42:00 AM	$334.41
11/3/2023	11:03:00 AM	$341.62	8:54:00 AM	$339.39
11/6/2023	7:30:00 AM	$341.64	12:15:00 PM	$339.89
11/7/2023	9:39:00 AM	$342.07	7:39:00 AM	$340.30
11/8/2023	7:48:00 AM	$342.59	10:06:00 AM	$339.94
11/9/2023	7:30:00 AM	$342.05	1:39:00 PM	$338.77
11/10/2023	1:51:00 PM	$343.30	8:42:00 AM	$339.27

I'd like to take that output and create a histogram of percentages based on 30-minute buckets but haven't figured out how to join that query with my histogram buckets table that looks like this:

ID	StartTime	EndTime
2	7:30	8:00
3	8:00	8:30
4	8:30	9:00
5	9:00	9:30
6	9:30	10:00
7	10:00	10:30
8	10:30	11:00
9	11:00	11:30
10	11:30	12:00
11	12:00	12:30
12	12:30	13:00
13	13:00	13:30
14	13:30	14:00

I created another query to calculate the individual timestamp's percentages using the first query's output:

Code:

SELECT HighTime, COUNT(HighTime) AS NumHighs, ROUND(100 * COUNT(HighTime)/(SELECT TOP 1 COUNT(DATEVALUE(A.IntraDayDate)) FROM qryIntraDayDateHighLowTimes_v2), 2) AS PctHighs
FROM qryIntraDayDateHighLowTimes_v2
GROUP BY HighTime;

But when I try to INNER JOIN the query with the histogram times table, it gives me the error, JOIN expression not supported
FROM qryIntraDayDateHighLowTimes_v2 INNER JOIN HalfHourIntervalsT ON (HighTime<HalfHourIntervalsT.EndTime) AND (HighTime>=HalfHourIntervalsT.StartTime)
I take it that means I can't join a query to a table.

I also tried to incorporate the original query as a subquery in the second query, and then add another INNER JOIN to the FROM clause, but can't figure out what's wrong with the query. It throws a Syntax error in FROM clause:
SELECT HighTime, COUNT(HighTime) AS NumHighs, ROUND(100 * COUNT(HighTime)/(SELECT TOP 1 COUNT(DATEVALUE(A.IntraDayDate)) FROM qryIntraDayDateHighLowTimes_v2), 2) AS PctHighs
FROM (SELECT A.IntraDayDate, TIMEVALUE(B.BarDate) AS HighTime, A.IntraDayHigh, TIMEVALUE(C.BarDate) AS LowTime, A.IntraDayLow
FROM
(((SELECT DATEVALUE(BarDate) AS IntraDayDate, MAX(high) AS IntraDayHigh, MIN(low) AS IntraDayLow
FROM DIA_3min WHERE TIMEVALUE(BarDate) BETWEEN #7:30:00 AM# AND #1:57:00 PM# GROUP BY DATEVALUE(BarDate)) AS A
INNER JOIN DIA_3min AS B ON (A.IntraDayHigh = B.high) AND (DATEVALUE(B.BarDate) = A.IntraDayDate))
INNER JOIN DIA_3min AS C ON (A.IntraDayLow = C.low) AND (DATEVALUE(C.BarDate) = A.IntraDayDate)
INNER JOIN HalfHourIntervalsT ON (HighTime<HalfHourIntervalsT.EndTime) AND (HighTime>=HalfHourIntervalsT.StartTime)
WHERE TIMEVALUE(B.BarDate) BETWEEN #7:30:00 AM# AND #1:57:00 PM# AND TIMEVALUE(C.BarDate) BETWEEN #7:30:00 AM# AND #1:57:00 PM#
ORDER BY A.IntraDayDate)
GROUP BY StartTime;

Help?
Eric

**madpiet** · 03-12-2025, 09:32 PM

Do you just want the bucket number? Basically, you'd have to have a table of all the minutes of the day (or the ones you need buckets for, but with NO gaps), and then you'd add another column that would be the "bucket number". (Well, if you do it that way, you can create custom buckets). Otherwise you could do something like get the number of minutes since midnight (zero hour) and integer divide by your bucket size (minutes). Then you just join the "buckets" table to your data table, and the relationship would put the times into proper buckets for you. You would have to make sure to split any DateTime columns into one column for the date and another for the time or it won't work, though (obviously).

**EricRex** · 03-12-2025, 10:36 PM

Originally Posted by madpiet

Do you just want the bucket number? Basically, you'd have to have a table of all the minutes of the day (or the ones you need buckets for, but with NO gaps), and then you'd add another column that would be the "bucket number". (Well, if you do it that way, you can create custom buckets). Otherwise you could do something like get the number of minutes since midnight (zero hour) and integer divide by your bucket size (minutes). Then you just join the "buckets" table to your data table, and the relationship would put the times into proper buckets for you. You would have to make sure to split any DateTime columns into one column for the date and another for the time or it won't work, though (obviously).

Sorry if I wasn't clear about the desired final output.

I want to have the total number of highs and lows and percentage of highs and lows in each bucket of time. So, for the period 7:30 to 7:59, N highs and N' lows. For the period 8:00 to 8:29, M highs and M' lows and so on.

An example snippet of the desired output from a different input dataset is:

Label2	Label3	NumWin	PctWin
07:30	08:00	1	100
08:00	08:30	0	0
08:30	09:00	0	0

**June7** · 03-13-2025, 01:57 AM

Your buckets overlap. Which bucket should 8:00 fall into? Should fix the buckets table.

For each date/time, calculate rounding down to half hour. Use that to aggregate data.

Consider this expression tested in immediate window:

?CDate(Int(48*#3/12/2025 11:10:00 PM#)/48)
3/12/2025 11:00:00 PM
?CDate(Int(48*#3/12/2025 11:30:00 PM#)/48)
3/12/2025 11:00:00 PM
?CDate(Int(48*#3/12/2025 11:30:20 PM#)/48)
3/12/2025 11:30:00 PM
?CDate(Int(48*#3/12/2025 11:40:00 PM#)/48)
3/12/2025 11:30:00 PM
?CDate(Int(48*#3/12/2025 11:59:59 PM#)/48)
3/12/2025 11:30:00 PM

If you need the exact half hour to be start time, that will be a more complicated calculation. Midnight is also a complication.

Here's where I found this https://www.accessforums.net/showthread.php?t=78057

Am exploring madpiet's idea. Whichever approach, likely need a VBA custom function.

Other options may be an unequal join or cartesian query with WHERE clause or DLookup domain aggregate function but these all can perform very slowly with large dataset.

Provide sample of raw data table.

**jojowhite** · 03-13-2025, 02:40 AM

you can adjust qryFinal, it it is likely the answer to your query.

StartTime	EndTime	Win	Pct
7:30	8:00	6	37.50%
8:00	8:30	0	0.00%
8:30	9:00	2	12.50%
9:00	9:30	0	0.00%
9:30	10:00	1	6.25%
10:00	10:30	1	6.25%
10:30	11:00	0	0.00%
11:00	11:30	1	6.25%
11:30	12:00	0	0.00%
12:00	12:30	1	6.25%
12:30	13:00	0	0.00%
13:00	13:30	0	0.00%
13:30	14:00	4	25.00%

**EricRex** · 03-13-2025, 07:22 AM

Originally Posted by June7

Your buckets overlap. Which bucket should 8:00 fall into? Should fix the buckets table.
Here's where I found this https://www.accessforums.net/showthread.php?t=78057

My Inner Join clause in the working example resolves this apparent overlap issue:
INNER JOIN HalfHourIntervalsT ON (HighTime<HalfHourIntervalsT.EndTime) AND (HighTime>=HalfHourIntervalsT.StartTime)

Snippet of raw data table, DIA_3min

ID	BarDate	open	high	low	close
29	11/1/2023 7:30:00 AM	$330.92	$331.12	$330.52	$330.75
30	11/1/2023 7:33:00 AM	$330.72	$330.72	$330.38	$330.57
31	11/1/2023 7:36:00 AM	$330.49	$330.59	$330.30	$330.36
32	11/1/2023 7:39:00 AM	$330.37	$330.58	$330.23	$330.32
33	11/1/2023 7:42:00 AM	$330.30	$330.41	$330.10	$330.15
34	11/1/2023 7:45:00 AM	$330.15	$330.71	$330.12	$330.58
35	11/1/2023 7:48:00 AM	$330.59	$331.00	$330.59	$330.89
36	11/1/2023 7:51:00 AM	$330.87	$331.02	$330.70	$330.84
37	11/1/2023 7:54:00 AM	$330.82	$331.04	$330.82	$330.94
38	11/1/2023 7:57:00 AM	$330.99	$331.01	$330.78	$330.99
39	11/1/2023 8:00:00 AM	$330.98	$331.64	$330.98	$331.27
40	11/1/2023 8:03:00 AM	$331.21	$331.32	$330.80	$330.82

Cheers,
Eric

**EricRex** · 03-13-2025, 10:27 AM

Originally Posted by jojowhite

you can adjust qryFinal, it it is likely the answer to your query.

Hey jojo,

The only immediate question I have about your elegant solution is that I don't have a table of the count of highs and lows like you do. I have a query that generates that data:

Code:

SELECT A.IntraDayDate, TIMEVALUE(B.BarDate) AS HighTime, A.IntraDayHigh, TIMEVALUE(C.BarDate) AS LowTime, A.IntraDayLow
FROM ((SELECT DATEVALUE(BarDate) AS IntraDayDate, MAX(high) AS IntraDayHigh, MIN(low) AS IntraDayLow FROM DIA_3min WHERE TIMEVALUE(BarDate) BETWEEN #7:30:00 AM# AND #1:57:00 PM# GROUP BY DATEVALUE(BarDate))  AS A INNER JOIN DIA_3min AS B ON (A.IntraDayHigh = B.high) AND (DATEVALUE(B.BarDate) = A.IntraDayDate)) INNER JOIN DIA_3min AS C ON (A.IntraDayLow = C.low) AND (DATEVALUE(C.BarDate) = A.IntraDayDate)
WHERE TIMEVALUE(B.BarDate) BETWEEN #7:30:00 AM# AND #1:57:00 PM# AND TIMEVALUE(C.BarDate) BETWEEN #7:30:00 AM# AND #1:57:00 PM#
ORDER BY A.IntraDayDate;

My DIA_3min table is actual stock quotes on 3-minutes bars including high, low, etc. How can I integrate your qryBucketHighLowCount and qryFinal into my query above for a single query?

Cheers,
Eric

**orange** · 03-13-2025, 10:42 AM

You might review this Datapig video.

**EricRex** · 03-13-2025, 05:01 PM

Hey all,

To answer my own question, the solution is to give the FROM clause table/query aliases:

Code:

SELECT B.StartTime, B.EndTime, COUNT(A.HighTime) AS NumHighs, ROUND(100 * COUNT(A.HighTime)/(SELECT TOP 1 COUNT(DATEVALUE(A.IntraDayDate)) FROM qryIntraDayDateHighLowTimes_v2), 2) AS PctHighs
FROM qryIntraDayDateHighLowTimes_v2 AS A INNER JOIN HalfHourIntervalsT AS B ON (A.HighTime<B.EndTime) AND (A.HighTime>=B.StartTime)
GROUP BY StartTime, EndTime;

Works with them, generates a Syntax not supported error without them.

Now if I could just get the Highs and the Lows onto a single datasheet!

Cheers,
Eric

**madpiet** · 03-13-2025, 05:20 PM

Originally Posted by EricRex

Hey all,

Now if I could just get the Highs and the Lows onto a single datasheet!

Cheers,
Eric

Starting from qryIntraDayDateHighLowTimes_v2? or where?
I think you have to join on the value and the date back to the original table (or other query) to do that. Or did you want something like
(Date, HighLow, Time, Value)?

**EricRex** · 03-13-2025, 10:10 PM

Originally Posted by madpiet

Starting from qryIntraDayDateHighLowTimes_v2? or where?
I think you have to join on the value and the date back to the original table (or other query) to do that. Or did you want something like
(Date, HighLow, Time, Value)?

For completeness, the input table snippet is this:

ID	BarDate	open	high	low	close
1	11/1/2023 2:45:00 AM	$329.34	$329.34	$329.34	$329.34
2	11/1/2023 2:54:00 AM	$329.16	$329.16	$329.16	$329.16
3	11/1/2023 4:03:00 AM	$329.24	$329.26	$329.24	$329.26
4	11/1/2023 4:39:00 AM	$329.50	$329.50	$329.50	$329.50
5	11/1/2023 5:00:00 AM	$329.51	$329.56	$329.51	$329.56
6	11/1/2023 5:21:00 AM	$329.36	$329.36	$329.36	$329.36
7	11/1/2023 5:30:00 AM	$329.36	$329.36	$329.35	$329.35
8	11/1/2023 6:00:00 AM	$329.24	$329.24	$329.24	$329.24

The query to extract daily dates, highs and lows, and the times of the highs and lows, qryIntraDayDateHighLowTimes_v2 is:

Code:

SELECT A.IntraDayDate, TIMEVALUE(B.BarDate) AS HighTime, A.IntraDayHigh, TIMEVALUE(C.BarDate) AS LowTime, A.IntraDayLow
FROM ((SELECT DATEVALUE(BarDate) AS IntraDayDate, MAX(high) AS IntraDayHigh, MIN(low) AS IntraDayLow FROM DIA_3min WHERE TIMEVALUE(BarDate) BETWEEN #7:30:00 AM# AND #1:57:00 PM# GROUP BY DATEVALUE(BarDate))  AS A INNER JOIN DIA_3min AS B ON (A.IntraDayHigh = B.high) AND (DATEVALUE(B.BarDate) = A.IntraDayDate)) INNER JOIN DIA_3min AS C ON (A.IntraDayLow = C.low) AND (DATEVALUE(C.BarDate) = A.IntraDayDate)
WHERE TIMEVALUE(B.BarDate) BETWEEN #7:30:00 AM# AND #1:57:00 PM# AND TIMEVALUE(C.BarDate) BETWEEN #7:30:00 AM# AND #1:57:00 PM#
ORDER BY A.IntraDayDate;

The snippet of that query's output is:

IntraDayDate	HighTime	IntraDayHigh	LowTime	IntraDayLow
11/1/2023	1:33:00 PM	$333.36	7:42:00 AM	$330.10
11/2/2023	1:54:00 PM	$338.52	7:42:00 AM	$334.41
11/3/2023	11:03:00 AM	$341.62	8:54:00 AM	$339.39
11/6/2023	7:30:00 AM	$341.64	12:15:00 PM	$339.89
11/7/2023	9:39:00 AM	$342.07	7:39:00 AM	$340.30
11/8/2023	7:48:00 AM	$342.59	10:06:00 AM	$339.94
11/9/2023	7:30:00 AM	$342.05	1:39:00 PM	$338.77

The input table with half-hour intervals that allows us to calculate bucket totals and percentages for those half-hour intervals is:

ID	StartTime	EndTime
2	7:30	8:00
3	8:00	8:30
4	8:30	9:00
5	9:00	9:30
6	9:30	10:00
7	10:00	10:30
8	10:30	11:00
9	11:00	11:30
10	11:30	12:00
11	12:00	12:30
12	12:30	13:00
13	13:00	13:30
14	13:30	14:00

The query to extract the count of the highs and the percentage of those highs in half-hourly time timeslots, is:

Code:

SELECT  B.StartTime, B.EndTime, COUNT(A.HighTime) AS NumHighs, ROUND(100 *  COUNT(A.HighTime)/(SELECT TOP 1 COUNT(DATEVALUE(A.IntraDayDate)) FROM  qryIntraDayDateHighLowTimes_v2), 2) AS PctHighs
FROM  qryIntraDayDateHighLowTimes_v2 AS A INNER JOIN HalfHourIntervalsT AS B  ON (A.HighTime >= B.StartTime) AND (A.HighTime < B.EndTime)
GROUP BY StartTime, EndTime;

The final output of that query is:

StartTime	EndTime	NumHighs	PctHighs
7:30	8:00	110	28.5
8:00	8:30	24	6.22
8:30	9:00	23	5.96
9:00	9:30	19	4.92
9:30	10:00	18	4.66
10:00	10:30	12	3.11
10:30	11:00	16	4.15
11:00	11:30	16	4.15
11:30	12:00	17	4.4
12:00	12:30	17	4.4
12:30	13:00	17	4.4
13:00	13:30	27	6.99
13:30	14:00	70	18.13

Replace all of the Highs in the above query with Lows and you get the Daily Lows and Percentages:

StartTime	EndTime	NumLows	PctLows
7:30	8:00	126	32.64
8:00	8:30	42	10.88
8:30	9:00	23	5.96
9:00	9:30	12	3.11
9:30	10:00	14	3.63
10:00	10:30	17	4.4
10:30	11:00	16	4.15
11:00	11:30	19	4.92
11:30	12:00	17	4.4
12:00	12:30	17	4.4
12:30	13:00	18	4.66
13:00	13:30	19	4.92
13:30	14:00	46	11.92

The only remaining problem is that when I both highs and lows into the same query, both the highs and lows have the same numbers which is obviously incorrect. I think it's because the criteria are different for the highs and lows, but I don't know how to resolve those differences in a single query.

This is the combined query which produces the duplicate results:

Code:

SELECT B.StartTime, B.EndTime, COUNT(A.HighTime) AS NumHighs, ROUND(100 * COUNT(A.HighTime)/(SELECT TOP 1 COUNT(DATEVALUE(A.IntraDayDate)) FROM qryIntraDayDateHighLowTimes_v2), 2) AS PctHighs, COUNT(A.LowTime) AS NumLows, ROUND(100 * COUNT(A.LowTime)/(SELECT TOP 1 COUNT(DATEVALUE(A.IntraDayDate)) FROM qryIntraDayDateHighLowTimes_v2), 2) AS PctLows
FROM qryIntraDayDateHighLowTimes_v2 AS A INNER JOIN HalfHourIntervalsT AS B ON (A.HighTime<B.EndTime) AND (A.HighTime>=B.StartTime) OR (A.LowTime<B.EndTime) AND (A.LowTime>=B.StartTime) 
GROUP BY StartTime, EndTime;

The bogus output:

StartTime	EndTime	NumHighs	PctHighs	NumLows	PctLows
7:30	8:00	229	59.33	229	59.33
8:00	8:30	66	17.1	66	17.1
8:30	9:00	45	11.66	45	11.66
9:00	9:30	31	8.03	31	8.03
9:30	10:00	32	8.29	32	8.29
10:00	10:30	29	7.51	29	7.51
10:30	11:00	32	8.29	32	8.29
11:00	11:30	35	9.07	35	9.07
11:30	12:00	34	8.81	34	8.81
12:00	12:30	34	8.81	34	8.81
12:30	13:00	35	9.07	35	9.07
13:00	13:30	46	11.92	46	11.92
13:30	14:00	116	30.05	116	30.05

If you have any ideas about combining the highs and lows into a single query, I'm all ears. A Union query won't work because the column headers would be misleading.

Cheers,
Eric

**jojowhite** · 03-13-2025, 10:44 PM

create both the Low and High query.
then create another query that will join them by StartTime and
output fields from both Low and High query.

creating smaller queries, one after the other, is not
a silly thing to do. it is easy to debug and maintain.
rather than all-in-one, slamdunk approach, query
which is very difficult to trouble shoot.

**madpiet** · 03-13-2025, 10:44 PM

(Sorry, I butchered your post down to a manageable size! Hopefully I managed to leave in just the relevant bits!)

Say we call this vwLows:

StartTime	EndTime	NumLows	PctLows
7:30	8:00	126	32.64
8:00	8:30	42	10.88
8:30	9:00	23	5.96
9:00	9:30	12	3.11
9:30	10:00	14	3.63
10:00	10:30	17	4.4
10:30	11:00	16	4.15
11:00	11:30	19	4.92
11:30	12:00	17	4.4
12:00	12:30	17	4.4
12:30	13:00	18	4.66
13:00	13:30	19	4.92
13:30	14:00	46	11.92

(and the other one vwHighs)

The only remaining problem is that when I both highs and lows into the same query, both the highs and lows have the same numbers which is obviously incorrect. I think it's because the criteria are different for the highs and lows, but I don't know how to resolve those differences in a single query.

Code:

SELECT H.StartTime, H.EndTime, H.NumHighs, H.PctHighs, L.NumLows, L.PctLows
FROM vwHighs AS H INNER JOIN vwLows AS L ON (H.StartTime = L.StartTime AND H.EndTime = L.EndTime)
ORDER BY H.StartTime, H.EndTime;

If you have any ideas about combining the highs and lows into a single query, I'm all ears. A Union query won't work because the column headers would be misleading.

Cheers,
Eric[/QUOTE]

No, you don't want a UNION (that'll stack them). You want an INNER JOIN as I showed above, which will put the Highs and Lows data on the same line for each time period (StartTime, EndTime). As long as both tables have a StartTime and EndTime for every time frame, then it'll work just fine.

**EricRex** · 03-14-2025, 05:01 PM

Originally Posted by madpiet

(Sorry, I butchered your post down to a manageable size! Hopefully I managed to leave in just the relevant bits!)

Code:

SELECT H.StartTime, H.EndTime, H.NumHighs, H.PctHighs, L.NumLows, L.PctLows
FROM vwHighs AS H INNER JOIN vwLows AS L ON (H.StartTime = L.StartTime AND H.EndTime = L.EndTime)
ORDER BY H.StartTime, H.EndTime;

That's just beautiful, thank you!

And only 3 layers of queries (lol)!
1. Extract the Highs and Lows of the day and the times they occur.
2. Calculate the percentages by half-hour interval for the highs and the lows separately.
3. Combine the percentages into a single output datasheet.

My final query:

Code:

SELECT A.StartTime, A.EndTime, A.NumHighs, A.PctHighs, B.NumLows, B.PctLows
FROM qryIntraDayHighTimesPercentages AS A INNER JOIN qryIntraDayLowTimesPercentages AS B ON (A.StartTime=B.StartTime) AND (A.EndTime>=B.EndTime)
ORDER BY A.StartTime, A.EndTime;

Now, if only there were a way to parameterize the original, raw stock quotes table name so I can easily do each of my small universe of stocks simply! I know it can be done in VBA, but this was much more of an exercise in understanding JOINs much better, than in writing more VBA code as a one-off study.

Thank you for sticking with me on this problem!
Cheers,
Eric

**EricRex** · 03-14-2025, 05:03 PM

Originally Posted by jojowhite

create both the Low and High query.
then create another query that will join them by StartTime and
output fields from both Low and High query.

creating smaller queries, one after the other, is not
a silly thing to do. it is easy to debug and maintain.
rather than all-in-one, slamdunk approach, query
which is very difficult to trouble shoot.

Couldn't agree more. Especially since reading complex, multiple JOIN syntax is wickedly difficult! Thanks for your input.

Create Histogram Query from complex query

Thread Tools

Create Histogram Query from complex query

Similar Threads

How to create a complex query (inner join, sort by ID and time, cut list)?

Query is too complex to create report

Histogram for fields

Histogram - Resources over time, Multiple queries into one report

Query to create Histogram based on user input into text boxes

Posting Permissions