Start End Intervals - Specified or Implied

[wjs2015]

Hello all,

This question is under the general section as the best solution may have emphasis on table design or clever code.

Ideal Situation:
User inputs changes in allocation into a table corresponding to the date from which the "allocation" value changes.

START DATE	ALLOCATION
01/01/2014	75%
15/01/2014	80%
19/01/2014	82%

From this, a non-overlapping range of dates can be implied:

START DATE	END DATE	ALLOCATION
01/01/2014	14/01/2014	75%
15/01/2014	18/01/2014	80%
19/01/2014	{indefinite}	82%

Should another allocation change be added, such as:

START DATE	ALLOCATION
25/01/2014	88%

This will effectively close off the previously indefinite range of 82% from 19th January 2014 (with end date 24th January 2014).

Similarly, if the user subsequently adds the following record which dissects an existing date range:

START DATE	ALLOCATION
10/01/2014	70%

Then the resulting date ranges would be:

START DATE	END DATE	ALLOCATION
01/01/2014	09/01/2014	75%
10/01/2014	14/01/2014	70%
15/01/2014	18/10/2014	80%
19/01/2014	24/01/2014	82%
25/01/2014	{indefinite}	88%

So by entering just the starting/effective date, the above date ranges can be implied/constructed without having to enter a specific end date (as this may result in overlapping issues).

The question is - by what means would this be achieved efficiently? Two methods I could think of:

Updating Table
The table design includes 3 columns - including one for end date. Thus whenever a new record is added, it needs to update the "END DATE" of the appropriate previously saved record along with the new record.

Query


Or having a table that just records the START DATE and ALLOCATION columns and use queries to form an array of date ranges such as the below:

START DATE	END DATE	ALLOCATION
01/01/2014	09/01/2014	75%
10/01/2014	14/01/2014	70%
15/01/2014	18/10/2014	80%
19/01/2014	24/01/2014	82%
25/01/2014	{indefinite}	88%

The purpose being, that given the following dates, the program will output the below values:

Given: 05/01/2014
Result as single output: 75%
Result as detailed range:

START DATE	END DATE	ALLOCATION
01/01/2014	09/01/2014	75%

And likewise, given something in the future (open period)

Given: 31/01/2014
Result as single output: 88%
Result as detailed range:

START DATE	END DATE	ALLOCATION
25/01/2014	{blank}	88%

Please let me know the best approach for the above.
Many thanks in advance for any input.

[June7]

This requires finding value from another record of the same table - not easy. For one method review http://allenbrowne.com/subquery-01.html#AnotherRecord

Another method could use DMin() domain aggregate function. But domain aggregates can perform slowly.

Then you want to subtract 1 day from the retrieved value.

SELECT *, DMin("StartDate", "tablename", "StartDate>#" & [StartDate] & "#") - 1 AS EndDate FROM tablename;


Don't bother with updating field in table, calculate the value when needed.

[wjs2015]

This works great, thank you June7.

Would it be able to expand slightly and differentiate for two other column criteria?

START DATE	CRITERIA 1	CRITERIA 2
01/01/2014	A	E
10/01/2014	C	G
15/01/2014	C	G
19/01/2014	A	E
25/01/2014	A	F

The result would be:

START DATE	END DATE	CRITERIA 1	CRITERIA 2
01/01/2014	18/01/2014	A	E
19/01/2014	{blank}	A	E
10/01/2014	14/01/2014	C	G
15/01/2014	{blank}	C	G
25/01/2014	{blank}	A	F

* where result if blank (so indefinite) then I will replace with "31/12/9999"

My attempt with the below did not quite work out:

SELECT *, DMin("StartDate", "tablename", "StartDate>#" & [StartDate] & "#" AND "Criteria1 = '" & [Criteria1] & "'") - 1 AS EndDate FROM tablename;

Thank you very much

[June7]

The AND needs to be within quotes.

SELECT *, DMin("StartDate", "tablename", "StartDate>#" & [StartDate] & "# AND Criteria1 = '" & [Criteria1] & "'") - 1 AS EndDate FROM tablename;

[wjs2015]

Thank you very much.

For anyone else viewing this, adding multiple criteria:

SELECT *, DMin("StartDate", "tablename", "StartDate>#" & [StartDate] & "# AND Criteria1 = '" & [Criteria1] & "' AND Criteria2 = '" & [Criteria2] & "'") - 1 AS EndDate FROM tablename;