Urgent issue! Create field for intervals

[Bjorn]

Hi,


is there a simple way to create a field for intervals (see example):

Based on the field time which concists of a value like "12:59:30" I would like to create a field, called "TimeGroup".

If time 00:00:00 - (07:00:00) => TimeGroup = "00-07"
If time 07:00:00 - (11:15:00) => TimeGroup = "07-11:15"
If time 11:15:00 - (13:00:00) => TimeGroup = "11:15 - 13:00"
and so on (typically 4-8 groups).

Using a lot of nested IIF(...) will solve it but will be extremely long.

I know how to do this very simple in Excel (Vlookup... TRUE), but how do I solve it by creating a normal query (no VBA please) in MS Access?

Can I somehow create a small table with the group intervals that I "join" to?

I hope someone can help me quickly!
Many thanks in advance!!

Kind regards
Bjorn

[pbaldy]

Yes, you can create such a table, and it's the way I would go. You'd have fields for the minimum and maximum values plus the "TimeGroup".

[Bjorn]

Hello,
thanks for your reply.

I am not sure how I would implement that in a query.

Assuming I have the table "Orders" with a lot of fields, where one of them is "OrderTime" (could have all possible values, even seconds, between 00:00:00 and 23:59:59).

In addition I create a small table "tblTimeGroup" of 5-8 lines (records) with the fields:
FromTime (example: 07:00:00)
ToTime (example: 09:00:00)
TimeGroup (example: "Orders 7-9")

How do I join from table "Orders" to table "tblTimeGroup" ?

Kind regards
Bjorn

[pbaldy]

Here's an example:

SELECT tblActivity.Order_num, tblActivity.TransDate, Rate
FROM tblActivity LEFT JOIN Rates ON tblActivity.TransDate Between Rates.StartDate and Rates.EndDate

This is often called a "non-equi" join. Note that the non-equi join can't be displayed in design view, only SQL view.