Struggling with a nested where, group by and min/max in SQL

I have this table in MS Access and need to write a query for a report and am struggling. My SQL is largely unused for at least 5 years, and was never really that good, but I thought I could do this without too much trouble…alas.

Product	ProductSubCode	OrderDateTime	Order Amount
A	1	2021-05-25 11:30	5
A	2	2021-05-25 12:30	50
A	1	2021-05-25 13:30	500
B	1	2021-05-25 09:30	400
B	2	2021-05-25 10:30	40
B	1	2021-05-25 11:30	4
C	1	2021-05-25 13:30	30
C	1	2021-05-25 14:30	300
C	2	2021-05-25 15:30	3
A	2	2021-05-24 11:30	5
A	2	2021-05-24 12:30	50
A	1	2021-05-24 13:30	500
B	1	2021-05-24 09:30	400
B	1	2021-05-24 10:30	40
B	1	2021-05-24 11:30	4
C	1	2021-05-24 13:30	30
C	1	2021-05-24 14:30	300
C	2	2021-05-24 15:30	3

I’m trying to query the above table to display the following results

Product|ProductSubCode|MinYesterday|MaxYesterday|MinTwoDaysAgo|MaxToDaysAgo

i.e. For each product/sub code, select the min & max order amount for today and yesterday.

What I have so far is as follows;

SELECT distinct Product as masterID,ProductSubCode
(SELECT MIN(Order Amount) FROM TableName WHERE Product = masterID AND DateValue(OrderDateTime) >= DateAdd("d",-1,Date())) AS MinYesterday,
(SELECT MIN(Order Amount) FROM TableName WHERE Product = masterID AND DateValue(OrderDateTime) >= DateAdd("d",-1,Date())) AS MaxYesterday,
(SELECT MIN(Order Amount) FROM TableName WHERE Product = masterID AND DateValue(OrderDateTime) >= DateAdd("d",-2,Date())) AS MinTwoTodayAgo,
(SELECT MIN(Order Amount) FROM TableName WHERE Product = masterID AND DateValue(OrderDateTime) >= DateAdd("d",-2,Date())) AS MaxTwoDaysAgo
FROM TableName
GROUP BY Product, ProductSubCode

I get it, it’s definitely wrong but I could really use some help here.

Answer

I think you are very close, changed it just a little bit:

1. It’s better to avoid spaces in the column names, so use OrderAmount. If it’s not possible to change a column name already, MIN([Order Amount]) will work instead of MIN(Order Amount).

2. You have MIN in all the rows, whereas it should be MINs and MAXs.

3. I changed >= with = because you need a strict equation there

4. changed column alias masterID to table alias t1

SELECT distinct Product, ProductSubCode,
(SELECT MIN(OrderAmount) FROM TableName WHERE Product = t1.Product AND DateValue(OrderDateTime) = DateAdd("d",-1,Date())) AS MinYesterday,
(SELECT MAX(OrderAmount) FROM TableName WHERE Product = t1.Product  AND DateValue(OrderDateTime) = DateAdd("d",-1,Date())) AS MaxYesterday,
(SELECT MIN(OrderAmount) FROM TableName WHERE Product = t1.Product AND DateValue(OrderDateTime) = DateAdd("d",-2,Date())) AS MinTwoTodayAgo,
(SELECT MAX(OrderAmount) FROM TableName WHERE Product = t1.Product AND DateValue(OrderDateTime) = DateAdd("d",-2,Date())) AS MaxTwoDaysAgo
FROM TableName t1
GROUP BY Product, ProductSubCode