CODEWITHSUNDEEP
sqldatems-accessrange
nellywilson
nellywilson

Reputation: 25

How to update field from one table to another based on a time range join?

I have tried many different things but I am really struggling with this issue. I am used to MySQL, SQLite, and other databases but I can't seem to figure this one out in Access.

I have two tables that I want to join based on if the timestamps of table1 fall within a range of timestamps in table2 grouped by ID. See the following:

Table1:

ID  Timestamp
    8:00 AM
    8:01 AM
    8:02 AM
    8:03 AM
    8:04 AM
    8:05 AM
    8:06 AM
    8:07 AM
    8:08 AM
    8:09 AM
    8:10 AM
    8:11 AM
    8:12 AM
    8:13 AM
    8:14 AM
    8:15 AM
    8:16 AM
    8:17 AM
    8:18 AM
    8:19 AM

Table2:

ID  Timestamp
1   8:00 AM
1   8:02 AM
1   8:04 AM
1   8:06 AM
2   8:10 AM
2   8:12 AM
2   8:14 AM
2   8:16 AM

What I want to happen in Table1:

ID  Timestamp
1   8:00 AM
1   8:01 AM
1   8:02 AM
1   8:03 AM
1   8:04 AM
1   8:05 AM
1   8:06 AM
    8:07 AM
    8:08 AM
    8:09 AM
2   8:10 AM
2   8:11 AM
2   8:12 AM
2   8:13 AM
2   8:14 AM
2   8:15 AM
    8:16 AM
    8:17 AM
    8:18 AM
    8:19 AM

Here is what I tried initially (and wish would work) but have gone through many iterations of different queries without getting anywhere.

UPDATE Table1 
  SET Table1.ID = Table2.ID 
  WHERE Table1.Timestamp IN (SELECT Table2.Timestamp GROUP BY Table2.ID);

I either get no output (Table1.ID remains empty) or I get the error "Operation must use an updatable query".

Upvotes: 1

Views: 83

Answers (1)

Mark C.
Mark C.

Reputation: 6460

You need to create a temp table and use it as a temporary recordset to use to search the records. The reason for this is that you need the Min/Max timestamp per ID, which requires an aggregate query, which cannot be used in an update query.

SELECT Table2.ID, 
Min(Table2.TS) AS MinOfTS, 
Max(Table2.TS) AS MaxOfTS
 INTO try      '<- this is your temporary table.
FROM Table2
GROUP BY Table2.ID;

Now that we have values we can use to search with in our temp table, we can just reference that in our UPDATE statement.

UPDATE Table1, try SET Table1.ID = [try].[ID]
WHERE (((Table1.TS) Between [try].[minofts] And [try].[maxofts]));

Edit: I suppose you could use a DLookup - but they tend to run extremely slow compared to this method.

Upvotes: 1

Related Questions