SQL/MS Access - Finding on/off peak days in data set

Question

I have a data set that looks like the following (but obviously much larger):

Date ------------------ Customer ---------- Output
1/01/2016 01:00 ---------- 4 ---------------- 5
1/01/2016 03:00 ---------- 5 ---------------- 20
1/02/2016 09:00 ---------- 4 ---------------- 10
1/04/2016 06:00 ---------- 5 ---------------- 5
1/06/2016 13:00 ---------- 4 ---------------- 10
1/11/2016 23:00 ---------- 5 ---------------- 25
2/10/2016 12:00 ---------- 5 ---------------- 15
2/10/2016 24:00 ---------- 4 ---------------- 25
2/14/2016 16:00 ---------- 4 ---------------- 10
2/16/2016 04:00 ---------- 5 ---------------- 5
2/14/2016 16:00 ---------- 5 ---------------- 10
2/16/2016 04:00 ---------- 4 ---------------- 5
2/18/2016 16:00 ---------- 5 ---------------- 10
2/19/2016 12:00 ---------- 4 ---------------- 5

I need a query that does the following:
1) is able to tell whether the date is on or off-peak, meaning if it is a holiday OR if it falls on the week or weekday
2) if it falls on a weekday, must be able to further break-down on/off peak by hours (1-7 and 24 are off-peak and 8-23 are on-peak)
3) be able to pivot all of that together by month and by average

Basically it would look like this:

Month ----- Cust ------- Year --------- Avg.On-peak ----------- Avg.Off-peak
1 ----------- 4 ------------- 2016 -------------- 10 -------------------------- 7.5
1 ----------- 5 ------------- 2016 -------------- 25 ------------------------ 12.5
2 ----------- 4 ------------- 2016 -------------- 15 -------------------------- 7.5
2 ----------- 5 ------------- 2016 -------------- 12.5 ---------------------- 12.5

I have a linked table to Access from Excel. The data is downloaded from an online database and is stored in excel (but if better to store in Access LMK)

I have tried a switch statement:

For reference [Hrly_Daily_Monthly 2] is a table that just dateparts the datetime_beginning_ept into month, hour, year, etc.

[Off-peak days] is a table of all holidays

Switch(
[Hrly_Daily_Monthly 2].Date=#1/1/2016#, "off-peak day",
[Hrly_Daily_Monthly 2].Date=#5/30/2016# , "off-peak day",
[Hrly_Daily_Monthly 2].Date=#7/4/2016# , "off-peak day",
[Hrly_Daily_Monthly 2].Date=#9/5/2016# , "off-peak day",
[Hrly_Daily_Monthly 2].Date=#11/24/2016# , "off-peak day",
[Hrly_Daily_Monthly 2].Day = 1, "off-peak day",
[Hrly_Daily_Monthly 2].Day = 7, "off-peak day",

TRUE, "on-peak day") AS [On/Off Day]

Get an error because of too many statements.

I also tried creating multiple queries, rather than doing in one, and using the Make Table, to then put all queries together. And it severely slowed the database.

Off-peak day query AKA [off-peak days 2] table

SELECT [Hrly_Daily_Monthly 2].datetime_beginning_ept, [Hrly_Daily_Monthly 2].output, [Hrly_Daily_Monthly 2].cust, [Hrly_Daily_Monthly 2].Date
FROM [Hrly_Daily_Monthly 2], [Off peak list]
WHERE ([Hrly_Daily_Monthly 2].datetime_beginning_ept=[Off-peak days] OR [Day] NOT BETWEEN 2 and 6);

On-peak day query (which is dependent on the off-peak query)

SELECT [Hrly_Daily_Monthly 2].Date, [Hrly_Daily_Monthly 2].cust, [Hrly_Daily_Monthly 2].output, 
FROM [Hrly_Daily_Monthly 2]
WHERE NOT EXISTS (Select 1 FROM [Off-Peak Days 2] WHERE [Off-Peak Days 2].Date = [Hrly_Daily_Monthly 2].Date AND [Hrly_Daily_Monthly 2].Date = [Off-Peak Days 2].Date);

any help is much appreciated

Why would it NOT be better to store data in Access? Need to provide example data for each of the datasets referenced in query. — June7, May 06 '19 at 21:54
The data is already downloaded as an excel file so just figured to leave it that way. So we could just say that the [Hrly_daily_monthly] is the same as the table I created with the date, customer, and output. Ignore the one that says [SESA hrly_daliy_monthly]. Both are the same, wanted to make it simple, but forgot to make it all uniform. The [Off peak days] table looks like this: off peak list 1/1/2016 7/4/2016 12/25/2016 (etc....) — user11416683, May 06 '19 at 22:02
So why keep it as Excel and not import to Access? Is this a periodic download? Is it cumulative or just new info? — June7, May 06 '19 at 22:07
So I will have to update the file ~ once a week. Ideally I would have the table linked which would then update all queries/tables. It is an imported file though, hopefully I'm understanding you correctly. VERY new to all of this — user11416683, May 06 '19 at 22:11

June7 · Answer 1 · 2019-05-07T02:48:22.703

Be aware that midnight is 0 hour in an Access date/time field.

Consider this query that associates data table with OffPeakDays and calculates date parts and off peak factor:

SELECT datetime_beginning_ept, Customer, Output, Dt, Hr, Wd, Mo, Yr, OffPeakDays.HolDate, 
       IIf([Dt]=[HolDate] Or [Wd]<3 Or [Hr]<8, False, True) AS OnPeak
FROM OffPeakDays 
RIGHT JOIN 
    (SELECT datetime_beginning_ept, Customer, Output, DateValue([datetime_beginning_ept]) AS Dt, 
     Hour([datetime_beginning_ept]) AS Hr, WeekDay([datetime_beginning_ept],7) AS Wd, 
     Month([datetime_beginning_ept]) AS Mo, Year([datetime_beginning_ept]) AS Yr
     FROM DataTable) AS [Hrly_Daily_Monthly 2] 
ON OffPeakDays.HolDate = [Hrly_Daily_Monthly 2].Dt;

Now use that query in other queries to do aggregate calcs or use it as RecordSource for report and use report Sorting & Grouping features with aggregate calcs in textboxes. Report allows display of detail data as well as summary calcs.

One possible query based on the above:

SELECT qryDateCalcs.Mo, qryDateCalcs.Customer, qryDateCalcs.Yr, 
     Avg(IIf([OnPeak],[Output],Null)) AS AvgOnPeak, 
     Avg(IIf(Not [OnPeak],[Output],Null)) AS AvgOffPeak
FROM qryDateCalcs
GROUP BY qryDateCalcs.Mo, qryDateCalcs.Customer, qryDateCalcs.Yr;

Sadly the first query doesn't work :( the IIf statement basically looks at the first true statement then disregards the rest. — user11416683, May 13 '19 at 19:54
It works in my query. Reconfirmed. In my test, IIf() calculates correctly and only 1 record meets "On Peak" criteria. — June7, May 13 '19 at 21:25

SQL/MS Access - Finding on/off peak days in data set

1 Answers1