Add new column to linked table

[access-rob]

I have data in .csv format and I intend to make a database with a linked table in it to this data. However, the first few columns of this linked table are the year, julian day, time with ten minute intervals between records. I want to make a new column in the database to automatically add a column in the linked table that contains a timestamp of type date/time which is of the form yyyy-mm-dd hh:mm. I haven't found a way of doing this, is it even possible to do it automatically, or would you have to open the database and then run a macro to make a new table with the extra column?
An extra but related question, I assume it is not possible to index a linked table to increase querying efficiency as the table isn't actually stored in the database, but just pulled in when asked for?
Thanks in advance for any help.

 

[Rabbie]

It would depend on where/what the linked table is. Some more info would be helpful

 

[access-rob]

Sorry, I don't really understand what information you need. I have made a linked table using the New->Link Table Wizard, selected a .csv file that contains comma separated data. In the Advanced options I have selected the data type and various other things about the format of the data to get it imported correctly and then I have the desired linked table in an access database. However in some cases I don't have a proper timestamp, but columns as described below. Therefore I want, preferably automatically, to add another column to the linked table so that whenever it is opened a proper timestamp is formed from the data in the first few columns and becomes a column in the linked table. Hope that helps, if there is any other info. you need, let me know.

 

[boblarson]

While you can link to a text file, you can't edit it directly as a linked table. You aren't going to be able to add information to the CSV file as if it were a table.

 

[akt01]

can u explain the timestamp .... are you trying to capture the date/time of download?

 

[access-rob]

The data is recorded by a logger, with one record containing data from a ten minute interval, that data is then sent to us daily in the form of a .csv file. The first column contains the year, the second the Julian day of that year and the third the time, so for example:
2009 | 32 | 1530
would be the first of February 3:30pm.
Does that explain what you wanted?

As it seems from what boblarson says above that I can't modify the linked table directly, would it be possible to make another table within the database that contains the same data as the first, but has the timestamp in the desired format, however this second table would have to automatically update with the data contained in the first table whenever the database is queried from excel?

Rob.

 

[Atomic Shrimp]

This would probably be one of the few instances where a one-to-one relationship is appropriate.

So, as long as the linked table has a unique key (or a combination of fields that can be used as such), you could create another table, within your database, containing the same key, and additional fields to store your timestamps, etc.

You'd need to find some way of updating your companion table with new records as they appear in the linked one, but that can be done with a fairly simple find unmatched>append query.

 

[access-rob]

Hi Mike,
that sounds really useful, but I didn't understand all of it!
I have a unique key either in the record number or that could be formed from the year day and time columns. So to do as you suggest, how would I go about making this table with a one-to-one relationship? This would then also presumably mean that I could index this second table, whereas a linked table can't be indexed?

As regards updating the second table, I found a helpful article here: http://office.microsoft.com/en-us/access/HA011860631033.aspx (for the benefit of any future readers). Would it be possible to automate this append and update query so that it happens whenever the table is queried from VBA imbedded in an excel spreadsheet?

Thanks for all the help,
Rob.

 

[Atomic Shrimp]

Is the record number a field in the original table? (i.e. like an autonumber in whatever program natively generates/holds the data)

If so, that would be the ideal choice of key. Otherwise, it'll have to be a combination of the other columns (only if there is absolutely no possibility of duplication - i.e. two records containing the exact same year, day and time)

So... you need to create a table (hereafter called 'companion table') in your database with field definitions matching the ones in your linked table that you want to use as the key.

Then you need to add some other fields to this table, to store your timestamps and anything else the original linked table won't do for you.

Then use the 'find unmatched' query wizard to find rows in the original table that are not in the companion table, linking on the key field(s) between the two. On first run, that query should return all rows in the original table (because they're all missing from the companion table). Save the query as 'newrows'.

Then create an append query to append the records returned by 'newrows' into the companion table.

Then find some way to get that query to run on a regular basis - for example, in a macro that runs when the database is opened.

That should do it...

 

[access-rob]

Hi Mike, thanks for your great response, unfortunately I got majorly sidetracked and have just got onto implementing it. Everything works fine apart from the fact that I don't have a unique identifier in the original linked table as I at first thought. This means that I need to form the "Find unmatched" query from a combination of the year, julian day and Hour/Min columns, however I only seem to be able to match on one column in Access 2000 9.0.3821 (SR-1), is there something I've missed or is this a limitation of this version of access?

Just for anyone reading this in future, if you need to convert the following columns:
yyyy, ddd, hhmm (where yyyy is year ddd is Julian day, 0 to 365 or 366, hh is hour and mm is minutes)
into a date/time type timestamp of the form:
dd/mm/yyyy hh:mm
here is the required expression for a query:
TimeStamp: DateSerial([yyyy],1,[ddd])+TimeSerial(Int([HourMin]/100),[HourMin] Mod 100,0)
quite specific need, but I suppose it might save someone some time

 

[access-rob]

SOLVED - Re: Add new column to linked table

Ok, I've now found a way to do this directly in SQL, then you can just list as many fields as you want with ANDs in between. Thanks for everyone's help.