Assign a numeric value to a time range?

[NSkram79]

I have a database that I created for logging Calls for Service for a Security Department I work for.

In that database it logs the Date of Entry [EntryDate] and Time of Entry [EntryTime]. For statistical purposes I would like to be able to assign a numeric value to certain time ranges.

Such as;
07:00 - 14:59 = 1
15:00 - 22:59 = 2
23:00 - 06:59 = 3

I cannot figure out how to do this though. Any help would be appreciated.

[FishVal]

Hello.

Create a table with a structure like the following:

lngKeyRangeNum    Long, PK
dteStart                 Date
dteEnd                  Date
 

I guess the question is not about "assignment", but how to practically use it.
So, how do you intend to use it?

Regards,
Fish

[NSkram79]

I have the table and I'm sorry if what I state above didn't make any sense , but truth be told I don't know how to explain it.

What I'm looking for/to do is that if I enter an in item into the table either by query/vba/form etc it will assign a numeric value to the entry based on what time the item was entered. That numeric value is determined by the ranges I listed above.

What it is , is the database tracks the calls for service we recieve at our Security Department. Those calls come in at all times of the day , and we operate 24/7 on a 3 shift assignment, 1st shift is 0700 to 1459, 2nd is 1500 - 2259 and 3rd is 2300 - 0700. I'm trying to determine a way to assign the call to the shift based on what time the call was entered.

[FishVal]

Well.

There is no need to store additional number in the table where time is stored since conversion rules are "stored" in the table which I've suggested you to add to your database.
Read please a very similar thread - Grouping Query Range.

Also, I guess you will benefit from reading the following articles:
Database Normalization and Table Structures
SQL JOINs

Regards,
Fish

[OldBirdman]

This doesn't need another table or a new field. It is a calculated value:

(Int(((Hour(#<Your Time Or Date Here>#)-7)+24) Mod 24) / 8 ) + 1 

I would store the actual time, and convert to the 1..2..3 format when needed. This way the precise data remains available if needed in the future

[FishVal]

Exelltnt point, OldBirdman.

However, this approach lacks of flexibility. Though it gives a more simple solution, it is not suitable for future or temporary changes which should be foreseen even when they are not expected...

.... I would say: especially when they are not expected ;)

Regards,
Fish

P.S. Something tells me that the table storing shift times is not unnecessary in more or less comprehensive database.

[OldBirdman]

The "Grouping Query Range" reference isn't exactly straightforward either, and would present another programmer some effort if the program had to be modified in the future.
Keeping it simple, maybe create a table with 24 records, one for each hour.
tblShiftNumber

ShiftHr (PK)  ShiftValue
     0          3
     1          3
     2          3
    ...
     7          1
     8          1
     14         1
    ...
     15         2
    ...
     23         2

This would make the SELECT query a simple join on Hour([LoggedTime]), would be easy to correct by programmer or a simple form, and meet requirements.
In my part of the world, shifts are not always equal length, the day(normal) shift running between 8AM-5PM with 1 hour off for lunch (staggered if coverage required). 2nd shift 5PM-Midnight and 3rd Midnight-8AM.
Above table could be expanded to 1/2 hour increments (or 1/4 hour) if needed by using Int(Minutes([LoggedTime]) / 30) to build the ShiftHr key.

[FishVal]

@OldBirdman
I'm afraid I don't follow. What has been simplified by this table?

Kind regards,
Fish.

[OldBirdman]

Spanning Midnight. Aren't your dteStart and dteEnd actually times. Then dteStart = "23:00" is greater than dteEnd = "06:59:59"

[FishVal]

@OldBirdman
It do affect nothing. :)

[OldBirdman]

OK. I read that link about Grouping Query Range, and didn't really follow. But I don't have the problem myself, so I didn't generate a test database. So I mis-understand. So forget my idea. in post #7. Thank you FishVal for watching out for NSkram79 and not allowing me to muck things up.

[NeoPa]

I see where you're going OB, but I think Fish's solution is probably more flexible and robust. Your point about midnight is a good one though, and the table may well need an AutoNumber PK as well as a field to identify the shift number. Two separate records would be required for the shift spanning midnight. Different PK values, but the same [ShiftNo] value.

Linking would be along the lines of :

SELECT Security.XXX,
       Shift.ShiftNo,
       ...
 
FROM   [Security] INNER JOIN [Shift]
  ON   Security.EntryTime Between Shift.Start And Shift.End

[OldBirdman]

Probably off-topic, but this is really a very common issue. Program needs a number for a small list of words. We build these little tables for Months of the Year so May->5, Choices like "Yes", "No", "Maybe", "Not Applicable" so Maybe->3, etc. These lists also exist as Value Lists for combo boxes. Either requires a programmer to change, but admittedly it is easier to change tables or controls than find the VBA statements that have tricky algorithms to do it.

Is there an easy way to use an .INI file for this? That would make changing the month names to another language very easy. For this thread, .INI file might be:

FirstShiftStart=07:00
SecondShiftStart=15:00
ThirdShiftStart=23:00
 

I would then call a function that returned the value of 1, 2, or 3.

    intShiftNumber = ShiftValue([EntryTime], _
            FirstShiftStart, SecondShiftStart, ThirdShiftStart)

[NeoPa]

I have to admit, if I'm already in a database system, I'll use the tables every time. They can be incorporated into functions if desired, but they fit the requirement so fundamentally well.

The multi-language example, for instance, is so easy to implement. Just add an extra field to identify the language. The data's still all held in the table, and the performance won't degrade.

Everyone has their opinions of course, and that's how it should be, but this is mine.