Left Join with Time Intervals

[montjoy]

First post here. Fairly basic level of knowledge with SQL/Access. I need to merge two sets of data. Suppose we have a data set below that has an observation for two different units at a time step. I want to also join what phase the unit was in at that time.

ID	Time	Observation
1	1	111
1	2	101
1	3	134
1	4	137
1	5	167
1	6	42
2	1	210
2	2	222
2	3	235
2	4	648
2	5	13
2	6	19

The phase data looks as below. That is, there is only a recording for when a unit enters a new phase. For example, unit 1 was in phase 2 for time steps 4 and 5.

ID	Phase	EnterTime
1	1	1
1	2	4
1	3	6
2	1	1
2	2	5

The resulting table should look as follows. Can anyone help with some sample code? Any help would be greatly appreciated.

ID	Time	Observation	Phase
1	1	111	1
1	2	101	1
1	3	134	1
1	4	137	2
1	5	167	2
1	6	42	3
2	1	210	1
2	2	222	1
2	3	235	1
2	4	648	1
2	5	13	2
2	6	19	2

[June7]

Try:

Q1:
SELECT [Table1.ID] & [Time] & [Phase] AS UID, Table1.ID, Table1.Time, Table2.EnterTime, Table1.Observation, Table2.Phase
FROM Table2 INNER JOIN Table1 ON Table2.ID = Table1.ID
WHERE (((Table2.EnterTime)<=[Time]));

Q2:
SELECT * FROM Q1 WHERE UID IN
(SELECT TOP 1 Dupe.UID FROM Q1 AS DUPE WHERE Dupe.ID=Q1.ID AND Dupe.[Time]=Q1.[Time]
ORDER BY Dupe.UID DESC);


Here is the reference that helped me http://allenbrowne.com/subquery-01.html


BTW, Time is a reserved word. Should avoid reserved words as names.

[montjoy]

Ah, Ok. Definitely learned something here (and will avoid reserved keywords).

This method definitely solves the problem I presented, but my issue is a little more complicated. I understand that we use the UID and we are essentially taking the observation with the largest UID if there is a duplicate.

However, with my actual data set, the "Phase" variable is more like a "State" variable in that it's not strictly ascending. For example, the data may actually look like the following.

ID	Phase	EnterTime
1	1	1
1	2	4
1	1	6
2	2	1
2	1	5

And the final table would look like:

ID	Time	Observation	Phase
1	1	111	1
1	2	101	1
1	3	134	1
1	4	137	2
1	5	167	2
1	6	42	1
2	1	210	2
2	2	222	2
2	3	235	2
2	4	648	2
2	5	13	1
2	6	19	1

Would this still be the right kind of approach to do this join (as) efficiently (as possible)?

Thank you!

[June7]

Did you try it? Is the output correct? I don't think it will be. The only alternative I can see will be using VBA.

[montjoy]

The output is correct on the original problem I posed, but on the modified it is not because of the issue of taking the maximum UID.

How about this. Is there a way to create a new column such that it is the end of the interval in table 2? That is, it is the EnterTime of the next phase minus 1. If this variable existed, I could do an inner join like you did before, but with the condition that the variable "Time" is greater than or equal to "EnterTime" and less than or equal to "ExitTime."



Table 2 previously was as follows.

ID	Phase	EnterTime
1	1	1
1	2	4
1	1	6
2	2	1
2	1	5

The new table would look like:

ID	Phase	EnterTime	ExitTime
1	1	1	3
1	2	4	5
1	1	6	inf
2	2	1	4
2	1	5	inf

[montjoy]

If this isn't possible, do you have a VBA reference that can get me started on problems like this in Access?

[June7]

For the query approach you envisioned, check out the link I provided in earlier post, in particular the example for 'get the value in another record'.

For VBA, no single reference to offer. The code would probably be a function called from a query and involve opening a recordset, looping through the records, evaluating data and returning a result.