Retrieve value based on time of day (1 Viewer)

MrHans · Apr 1, 2019

Hi all,

I need to classify events based on weekday and hour of day.
I plan on creating a table that holds:
CustID - WeekdayID (0 to 6) - HourID (0-23) - ClassificationValue

So I cycle through all the events from Customer X, retrieve weekday number from the event date and the Hour from the event time and then Classify the event based on Day and Hour.

What is the most efficient way to retrieve the Classification value for each event?

A Dictionary or Collection would require to concatenate day and hour first.
Dlookups would be slow.

Maybe a 3 dimensional array?

Thanks for your thoughts!

The_Doc_Man · Apr 1, 2019

The first question is how many of these are we talking about?

A 7 x 24 array for each CustID would get very big very fast if you have a lot of CustIDs. That is 168 slots per customer but would most likely be sparsely populated - and for arrays, wasted space is bad because Access has to pre-allocate the entire array. That is, the array would be lightly populated but fully allocated.

A Dictionary or Collection would possibly be sparse but as you point out, you have extra key manipulation.

You are correct that DLookups would be slow if that is all you did. We would need to know what a processing cycle looks like to know if it would be so slow as to be useless or whether there is some sneaky way to make a JOIN work. Without knowing a bit more about the problem and its end goal, this is a really tough question to answer.

plog · Apr 1, 2019

You description is kind of ambigous. Mostly surrounding:

and then Classify the event based...

Does that mean you will populate [ClassificationValue]? If so, you wouldn't store that value in your table, you would "classify the event" in a query--you don't store values that are calculable.

As for the actual classifying, without more details it seems a simple table would do the job and just use a query to retrieve that value:

tblClassifications
WeekdayID, number, 0-6
HourID, number 0-23
Classification, text, the classification value you want

If that doesn't help, please provide sample data to illustrate your issue.

MajP · Apr 1, 2019

Can you explain this better, I cannot get what you are saying.

You want a table that holds

CustID
WeekdayID
HourID
Classification

Then I assume you have some other table of events. Something like
EventID
EventTimeDate
ClientID

so a record would be
Event1 1/1/2019 10:00 Am Emp123

You read that record and insert a new record into your other table
Emp123 2 10 ClassSomething

What are you looking up for the classification? Not sure why you need any kind of data structure.

MrHans · Apr 1, 2019

Thank you guys for your replies.
Sorry if I'm not being clear.

I get a weekly list of events (about 50 events) for each customer (about 100 customers).
The event includes a customer ID, Date and Time. I need to add a classification letter to each event.

The customer provides me with a classification formula. For example Mo to Fri = X, Weekend = Y. But it varies for each customer.

So indeed I think about creating a table that contains the CustomerID, Weekday, Hour and Classification Letter.

All combinations will be populated (7 x 24 per customer).

So I need a function or something that will let me retrieve the Classification letter.
Like:
GetClassification(CustID as Long, EventDateTime as Date) As String
...
End

But with an external function it would take more time to process (50 calls per customer), so it would be best if I could preload a Dictionary or something per customer cycle. That way I would have all 7x24 values available instantly...

MajP · Apr 1, 2019

Doing that with a dlookup will be about instantaneous. No need for another data structure.
Wrap it like you said. You could do it in sql too

join your event table to your classification table on
weekday([EventDateTime) to EventID and hour([eventDateTime]) on HourID.

MajP · Apr 1, 2019

Code:

Public Function GetClassification(ClientID as long, EventDateTime as Date) as string
  dim dayID as integer
  dim hourID as integer
  dim strWhere
  dayID = weekday(eventDateTime)
  hourID = hour(eventDateTime)
  strWhere = "ClientID = " & ClientID & " AND WeekdayID = " & dayID & " AND HourID = " & hourID
  GetClassification = Dlookup("ClassID","TblCodes", strWhere
end function

Untested should work fine. You are talking 5,000 not 500k. If not that sql will be fast.

MrHans · Apr 1, 2019

Yes, I agree that would be the function I create as well, but wouldn't that be very slow?

Wouldn't it be faster to have the classifications preloaded in an 3D array or something? I barely have any experience with Arrays so not sure if that would be suitable for this purpose...

The update query could also work yes, didn't think of that. Maybe fiddle with the joins a little.

I can definitely use the function you provided for now and see if that's fast enough. Thank you Majp!

Mark_ · Apr 1, 2019

You receive about 5000 "Events" per week.
Doing one lookup for each with a function will probably take less time than you've spent asking about this. Over all, you'll probably not notice any significant speed increase by doing an optimized array reference as you are talking about relatively small record sets. You may have already spent more time trying to do this quicker than what you would save this year in processing time.

Galaxiom · Apr 2, 2019

MrHans said:
The customer provides me with a classification formula. For example Mo to Fri = X, Weekend = Y. But it varies for each customer.

So indeed I think about creating a table that contains the CustomerID, Weekday, Hour and Classification Letter.

All combinations will be populated (7 x 24 per customer).

The best way is in SQL like MajP suggested. The joins will be funky and need to be written in SQL rather than the designer.

The_Doc_Man · Apr 2, 2019

I have diddled with the spacing of your quote.

The event includes a customer ID, Date and Time. I need to add a classification letter to each event.

The customer provides me with a classification formula. For example Mo to Fri = X, Weekend = Y. But it varies for each customer. So indeed I think about creating a table that contains the CustomerID, Weekday, Hour and Classification Letter.

So here is the way I would do it, but understand that there are other ways as well. I would treat date and time as a combined field if possible on the event table.

1. Your events come in. You have the Customer ID, Date, and Time. You also want an event code of some sort that you will look up. But in this event table, you need TWO more items - the date converted to day of the week and the time converted to hour of the day. These will be integers, 0 to 6 and 0 to 23. You will have the CustID, which is a number around 100 give or take. So when you are in-processing, you run your 5000 events kind of like this:

Code:

UPDATE EventList SET HourCode=DatePart( "h", DateTime ), DayCode=DatePart( "w", DateTime ) ;

Where obviously, the table of incoming events is being translated so you have your numeric codes that would feed the lookups. You want to have non-unique indexes on HourCode, DayCode, and CustID on this event table AND on the code lookup table.

2. Now you have the Codes to assign:

Code:

UPDATE EventTable INNER JOIN CodeTable 
    ON ( EventTable.CustID = CodeTable.CustID ) AND
       ( EventTable.DayCode = CodeTable.DayCode ) AND
       ( EventTable.HourCode = CodeTable.HourCode )
     SET EventTable.EventCode = CodeTable.EventCode ;

Two queries. Done. (You MIGHT have to play with it a little to get it just right.)

MrHans · Apr 2, 2019

Thank you very much also DocMan, in fact the event date/time are currently in 1 field. I was thinking to split them up, but I understand in this case it's better not to.

Adding the 2 extra fields in the Events table is also no problem, so we can populate these later with the queries.

Basically I don't need to store a query in this case right? Just two CurrentDb.Execute strSQL statements as you described.

I will definitely give this approach a try as well.

Ty!

The_Doc_Man · Apr 2, 2019

Like I said, try it. I can say that IF you were worried about speed, doing it this way is about as fast as Access could do it. Please let us know if this worked for you.

You probably could get away with using string-based SQL. On the other hand, if you were doing your damnedest to be a speed demon, two stored queries run by either a macro or two VBA CurrentDB.Execute statements would be the ultimate in speed for Access. That is because stored queries also store their own query plan and don't have to make a new one each time. To be honest, though... for the amount of data you are describing, you might get a few milliseconds back at most by using stored queries rather than just using SQL strings each time.

Retrieve value based on time of day (1 Viewer)

MrHans

Registered User

The_Doc_Man

Immoderate Moderator

plog

Banishment Pending

MajP

You've got your good things, and you've got mine.

MrHans

Registered User

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

MrHans

Registered User

Mark_

Longboard on the internet

Galaxiom

Super Moderator

The_Doc_Man

Immoderate Moderator

MrHans

Registered User

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread