Newbie Question

[gblaxx42]

I created a DB to track soldier information. Each month reports are due to
Brigade. Each Company and Battalion has there own version of the DB. What I
need to do is export data from the Company then import it to the Battalion.
Then I need to export the info from the Battalion and import them into the
Brigade. This information is either all new for a new soldier or updated info
for an existing record for an existing soldier. I need to update all new
changes to the info. What is the best method for doing this.

Also, when I try to export a .dbf, it pops up with an error saying there are
duplicate field names. Im confused because ieach field name is unique.

Thanks for any help I can get.


Lt Butler

 

[Klatuu]

First, thanks for your service to the country.

I would export the data to a csv file and send that to where it needs to go.
Then at the receiving unit, link to the csv file as if it were a table using
the TransferText action or method. It would be beneficial if you set up an
import specification for the file. Then create update queries to update the
existing data. An Update query will also add any new records to the Access
tables.

As to the dbf file, I can't say what may be causing the problem. How are
you doing the export?

 

[gllincoln]

Hi Lt Butler

The process is about the same for each table and each database going up the chain. You match on key fields and test for new or changed records.

Handling new records is easy - assuming that each new soldier has an identifying serial number and somehow I suspect they might! <grin>

SELECT IncomingTable.*
FROM IncomingTable LEFT JOIN ExistingTable
ON IncomingTable.SoliderID = ExistingTable.SoldierID
WHERE ExistingTable.SoldierID is Null;

Bring up this query - if it looks right, then build an Append query that takes the appropriate fields of this recordset and inserts them into ExistingTable.

Without seeing the field names in each table, and knowing the relationships between table, and if tables have auto-numbered fields etc. I can't get very specific but

assuming you have a matching (or close match) between company & battalion & Brigade tables...

My recommendation for handling edited records would be, if they don't already exist - create a login process to the database so you know who is doing the entry.

Make two date/time fields that are hidden and two user id fields beside them.

createdUser - (the user's id)
createdTime - (the time of new record entry)
LastModifiedUser - (who edited this record)
LastModifiedTime - (when it was last modified)

When somebody creates a new record you insert the entry person's id and set the createdTime to Now()
When somebody edits the record you insert the entry person's id and set the LastModifiedTime to Now()
You know it's an edit if you find data already exists in the createdUser field, right?

If you want to make this a very robust system, each time someone edits a record, you make a copy of the old version to a changes table. Then you know what you had before the edit took place. Sure helps if somebody screws up and saves the wrong info, particularly if the old data is otherwise tough to recreate. This gives you the potential capability to rollback, reverse changes - a very handy administrative tool.

So - if you have a date lastmodified stamp then you can join the incoming table to your local table on the SoldierID field (whatever its name is) and only update those records where incomingtable lastmodified date is newer than existingtable lastmodifiedtable date.

SELECT IncomingTable.*
FROM IncomingTable INNER JOIN ExistingTable
ON IncomingTable.SoldierID= ExistingTable.SoldierID
WHERE ((IncomingTable.LastModifiedDate Is Not Null) AND
((ExistingTable.LastModifiedDate Is Null) OR
(IncomingTable.LastModifiedDate > ExistingTable.LastModifiedDate)));

You bring up this query - look it over and if everything looks okay, then you can run a query that saves the old rows to a changes table and then run a query that updates the existing record with the contents of the new record, or you can delete the old row and append the new row, or just append the new row, whichever works better for your particular application and specific table.

Having the timestamps is much better, more concise, more military, ya know? <grin>

However, if you don't have them at the moment and need to make something work....
You have a set of fields that are the important data, the data that might get updated/changed, fields that exist in both the incoming and existing table, right?

FirstQuery

SELECT IncomingTable.*
FROM IncomingTable INNER JOIN ExistingTable
ON IncomingTable.Field1 = ExistingTable.Field1
IncomingTable.Field2 = ExistingTable.Field2
IncomingTable.Field3 = ExistingTable.Field3
IncomingTable.Field4 = ExistingTable.Field4
IncomingTable.Field5 = ExistingTable.Field5
etc. = etc.;
'#### we do this for every field that matches up
'#### between the two tables

Now we join the FirstQuery to the IncomingTable and
see what doesn't fit, ie has a changed field

SELECT IncomingTable.*
FROM IncomingTable LEFT JOIN FirstQuery
ON SoldierID=SoldierID
WHERE FirstQuery.SoldierID Is Null;

This will bring up only those records from the IncomingTable where something isn't the same as the ExistingTable.

Hope this helps...
Gordon