Newbie Import/Export problems Please help!!!!

[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

 

[Moniker]

So long as you have a SoldierID of some sort that's common to the table(s) where you are either updating or appending, this is easier than you think. Before I delve into that, do you have it structured that way already?

 

[gblaxx42]

yes, the SSN is the unique identifier. I know it is something easy, but its been so long since I have done this. Thanks for your help.

 

[Moniker]

The process from going Company to Batallion, and then Batillion to Brigade is identical, so I'll do the first one for you.

1) We want to update the company members that already exist in the batallion, so using the SSN, we look for matches between the two tables using a left join:

UPDATE 
    BattalionTable
LEFT JOIN 
    CompanyTable ON BattalionTable.SSN = CompanyName.SSN 
SET 
    BattalionTable.Field1 = CompanyTable.Field1
    ,BattalionTable.Field2 = CompanyTable.Field2
    ,BattalionTable.Field3 = CompanyTable.Field3
    ,.                       .
    ,.                       ,
    ,BattalionTable.FieldX = CompanyTable.FieldX
WHERE 
    CompanyTable.SSN Is Not Null
;

Replace BattalionTable with your BatallionTable name. Replace CompanyTable with your CompanyTable Name. Replace Field1 through FieldX with the field names you're updating (location, age, etc. -- whatever they are).

2) Now we need to do about the exact same thing, but we're appending members from the company that don't exist in the battalion yet (damn green recruits ). To do this, we reverse the logic and tell the query to show us everyone that is in the CompanyTable that is not in the BatallionTable:

INSERT INTO BattalionTable 
    (Field1
    ,Field2
    ,.
    ,.
    ,FieldX)
SELECT 
    CompanyTable.Field1
    ,CompanyTable.Field2
    ,.
    ,.
    ,CompanyTable.FieldX
FROM 
    CompanyTable 
LEFT JOIN 
    BattalionTable ON CompanyTable.SSN = BattalionTable.SSN
WHERE 
    BattalionTable.SSN Is Null
;

Make the same replacements for CompanyTable, BattalionTable, and FieldX names.

3) Now we've updated existing members of the BattalionTable (if changes existed) and we've added any new members from the CompanyTable to the BattalionTable.

4) Going from Battalion to Brigade is the identical process. To do it, use the process above and replace the CompanyTable name with the BattalionTable name, and replace the BattalionTable name with the BrigadeTable name.

HTH

 

[pbaldy]

Under the right conditions it can be done in one query:

http://support.microsoft.com/kb/208870/en-us

 

[Moniker]

Yup, that one just uses a right join instead of a left join. In our example, this is right joining CompanyTable on BattalionTable, including everything from the CompanyTable and only matching records in the BattalionTable. It seems odd that an update would actually perform an append in this situation, though, as it's not standard SQL. (Then again, a lot of Access is not standard SQL.)