import CSV, update timestamp to now() if already in table. (1 Viewer)

jgaard

New member
Local time
Today, 17:58
Joined
Sep 13, 2013
Messages
4
Hi
--{Warning: Complete DB and ACCESS N00B}--

I have a set of data that I update daily.
The data comes from a CSV. It contains a key, and 2 timestamps.
It looks something like this:
KeyLastScanLastAgentTime
Key108-03-2022 05:00:2702-03-2022 02:14:13
Key208-03-2022 04:44:4815-02-2022 02:48:01
Key307-03-2022 16:00:4925-02-2022 11:08:31

I have in my table of data added a coulmn called "First seen" it adds a date to all new records using now()
So my table looks like this:
KeyLastScanLastAgentTimeFirstSeen
Key108-03-2022 05:00:2702-03-2022 02:14:1308-03-2022 09:17:28
Key208-03-2022 04:44:4815-02-2022 02:48:0109-03-2022 09:19:42
Key307-03-2022 16:00:4925-02-2022 11:08:3109-03-2022 09:19:42

And it works very well.

But I would like to add a column, lets call it, "LastSeen", that is the timestamp of the last import where that "Key" was present in an import. So when a "Key" is in an import, it should update the "LastSeen" time stamp. If it's not in the import it should leave the timestamp alone.

Kind regards JGaard
 

Mike Krailo

Well-known member
Local time
Today, 12:58
Joined
Mar 28, 2020
Messages
1,030
When you do the import, import to a temp table and then create query to join temp table to your main table by the key. Use same now() function to update LastSeen field in main table.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:58
Joined
May 7, 2009
Messages
19,169
when you Import, it means this is New record, otherwise it will overwrite previous record.
since new record is added, therefore LastSeen field is Empty/Null.
so you create an Update query Updating LastSeen with Now() (but saved to variable first, so all records have
same timestamp):

Update yourTable Set LastSeen = Now() Where LastSeen Is Null;
 

Users who are viewing this thread

Top Bottom