Automatically Update Table

Kheribus

Registered User.
Local time
Today, 11:51
Joined
Mar 30, 2015
Messages
97
Hello,

I am creating a database and application to automatically produce invoices for transportation contractors. It will be analyzing tables for specific metrics to dynamically generate bills, based on mileage, time, in service status, rates associated to contractors, etc...

I have a SQL back-end connected to Access front end with linked tables.

The core of the dynamic data, the trip metric data (trip name, mileage, time...) should be updated daily and time stamped when new changes are made. This way, there is an audit trail of the trip data, showing all historical changes for each record. Currently, my primary key in the trip table is tripName, but since I will be reproducing the primary key when an update is made and adding a new line, then I will be using a composite key of trip name and dateChanged.

For example:

tripName mileage duration dateChanged
CR101A 1.0 61 9/25/2015
CR101A 0.9 55 9/22/2015
CR101B 1.0 67 9/25/2015
CR101A 0.9 62 9/17/2015


So, this table would originally start out with only one row per unique tripName identifier (with timestamp), but as time progresses and changes are made to those trips, a new row would be added for that trip with a different time stamp.

The difficulty here will be that 'changes' to the trip table will not happen via user input. I would like these to happen dynamically via an update function. There will be a trip table (excel file) on an ftp server (i'll start locally for now) which will have the 'current' trip table on any given day. I want my program to fetch that table and update the local trip table in my sql database automatically by realizing what has changed in the table (either new trips or new columns on pre-existing trips) and add a new row with a time stamp.

What will be the best way to accomplish this? Can I fetch the external data from the server into a recordset, and then cycle through it, checking the associated parameters in my local table, creating a new line when I notice a change or a new tripName?

Lets say there are no more than 2500 trip identifiers to start, with changes happening not _that_ often, so I do not believe that the scalability here should be a huge problem. I could also schedule this task eventually to be run overnight.

Any thoughts?

Thanks,
K
 
Last edited:
Well, you can certainly automate downloading a file via FTP nightly. You can open a recordset on that file (probably after linking or importing). I don't understand the structure of the file, but presumably you can step through it, compare to the existing data, and append as appropriate.

I guess the short version of that is "yes it can be done, but it may get involved".
 
Right. So basically I have one table that is changing daily, and I want to cycle through that table and compare it to my local table to determine changes.

I can't think of a better way to do this than cycling through the recordset and examining every piece of data.

I suppose I'll just start and come back with some more directed questions.

Thanks!
 
No problem! Post back with the specifics as they develop.
 
Right. So basically I have one table that is changing daily, and I want to cycle through that table and compare it to my local table to determine changes.

I can't think of a better way to do this than cycling through the recordset and examining every piece of data.

Why can't you use a query?
 
What do you mean use a query?

Basically i'm going to have my stored table as a sql table. The table that may be changing every day will be an excel table (locally at first for testing then on an ftp).

What I need to do is look at the excel table and compare it to the stored sql table, and make a new line for a primary key (in sql table) every time any of its fields change (in the excel table) and timestamp it.

Since the excel table will always have newer data than the sql table, so am thinking i need to iterate through the excel table (my thought was loading the excel table into a recordset), searching through every field associated with the recordset PK for changes by comparing the data between the two, and if there is a change, make a new record with a timestamp and regurgitate the data that changed into my backend sql table.
 
I'm not stuck on the recordset idea... so if there is a way I can 'dif' the data, like through a query, that will serve my purpose, im all for it!
 
Import the Excel table into Access, join it to the stored table and use an append query to add records where the values are different.
 
Ok. I'll have to play around with this. It sounds much better than iterating through every piece of data.

When I get a chance I'll work out how this would work with my current data structures and post some questions.

Thanks for the advice!
 
I'm setting up some sample tables in sql to test this idea. Thanks. I'll be back shortly (tomorrow)
 
Sorry, but my SQL knowledge is somewhat limited. Would you mind helping me with a couple of those queries?

For instance, lets say we have a standard tables:

Table1 (stored):

Col1 Col2 Col3 .. Colx

Table2 (hosted):

Col1 Col2 Col3 .. Colx

Could you show me how to do the join query to realize the different data in these two tables? Not only do I need to know rows that are in table 1 that are not in table2, but also vice versa, AND i need to know when rows that are in both table 1 and table 2 have different column data.

Not only that, but it's going to get complicated with the fact that the stored table, table 1, is going to produce an audit trail. I think what I have to is actually store the audit trail in a third table, and Table 1 above is a table of all of the primary keys of the audit table with the "highest" date. That way I can truly compare table 1 and table 2 without convoluting the join or comparison queries with the historical audit data.
 
It will need multiple queries.

Probably best if you post a sample database, ideally with a simulation of what it would be like after a hosted table had been integrated into the stored table, the audit trail stuff and a new hosted table ready to be integrated.

If you zip it you will be able to attach it to your post.
 
First, let me say that I am very impressed by the support in this community. I really appreciate it.

I think that this example is fairly general in that it is not necessarily tied to my database schema. This would just be producing an audit trail as updates are applied to a singular hosted table.

Table structure:

Route#, mileage, duration, date, change flag... really the extra (non key) columns are arbitrary as they are shared between the two tables, and route# and date are used as composite keys. So right now i'm using a generalized version of my route table that is just as explained above, the composite keys (route#, date) and the two metric columns (mileage, duration). The change flag will be used (in the audit trail table) to tell when a record has either changed, is new, or has been terminated from the stored table.

So, in this example, I will have three tables, all of them sharing the same columns as above:

1) Hosted table: This table will be accessed by my program nightly, downloaded, thrown into a table on my backend, and analyzed to create the audit trail. This table will not have any dates.

2) "Stored current table": This table will be every route# from the audit trail table, including all of the associated columns, with the highest date. So, every route # will only have one row, and I will only show the route data with the highest date (using a rank table).

3) Audit Trail Table: This table relies on some program logic or queries to compare the stored current table and the hosted table, and when one of the three following conditions are met, it produces a new line with the route#, mileage, duration, and currentdate(), and then sets the change flag depending on the following three conditions.

I) There is a new route# in the hosted table that is not in the stored table. Copy the entire row from the hosted table to the audit trail table, timestamp with currentdate(), set the change flag as 'New'​

II) A route# from the stored table is no longer in the hosted table.
Copy the row of the route# in the stored table, add a new row with that information, time stamp with currentdate(), set the change flag as 'Terminated'​

III) A route # that exists in both the stored table and the hosted table has one of it's associated columns (mileage, duration) change in the hosted table. In this case, I want to copy the entire row from the hosted table to the audit trail table, time stamp with currentdate(), and set the change flag as 'change'​

So, I believe that I have the pseudocode or program logic fairly worked out here. Now I just need to write some queries to provide the data to accomplish realizing when condition I, II, and III are met above.
 
Last edited:
It looks like outer joins can accomplish the first two conditions above, but the third is what i'm having the most trouble with.
 
Since you asked for something more concrete instead of hypotheticals, i put up a trivial example of this on SQL Fiddle: http://sqlfiddle.com/#!3/dd082/3/0

So, there are two tables here, sourceTable and hostedTable. sourceTable has a datechanged column but hostedTable does not.

I have populated the tables with some sample data. I would want to have a query that showed me if there is a new route in the hostedTable that is not in the sourceTable. Also, I would want to produce a query to recognize that the mileage on CR01A had been changed in the hostedTable.

The dataset will be much larger than this, but generally i'm trying to figure out when some pre-determined column data changes when entries have the same route name, or if there is a new or removed route in the hosted table.
 
You could try (untested)
Code:
--new  records---
select * from hostedtable h left join sourcetable s 
on s.route = h.route 
WHERE
 s.route is null
and
Code:
---changed records---
SELECT h.route, h.mileage,s.mileage,h.duration,s.duration
FROM sourcetable  as S inner join Hostedtable as H 
on s.route = h.route
WHERE 
s.mileage<>h.mileage or
s.duration <> h.duration
 
You are welcome, happy to help.
 

Users who are viewing this thread

Back
Top Bottom