locked
Import data from Excel to Access RRS feed

  • Question

  • User-853847052 posted

    Hi! I copy records from a modify and copy records from a table

    I've a table ANAGRAFE with a list of companies

    NAME - ADDRESS - TYPE  - ZIPCODE .....
    COMPANY1 - VIA SAN RAINERI, ZONA FALCATA, 12
    COMPANY1 - RAMPA LEONARDO DA VINCI STAZIONE TORPEDINIERE - sede principale
    COMPANY1 - RAMPA LEONARDO DA VINCI STAZIONE TORPEDINIERE - sede principale
    COMPANY1 - RAMPA LEONARDO DA VINCI STAZIONE TORPEDINIERE
    AAA
    - VIA F.LLI CABOTO, 11 - sede principale
    AAA
    - VIA F.LLI CABOTO, 11
    DRAG
    - VIA AL MOLO GIANO
    DRAG
    - VIA AL MOLO GIANO - sede amministrativa
    DRAG
    - VIA SPAGNA, 13
    DRAG
    - VIA SPAGNA, 13
    DRAG
    - VIA SPAGNA, 13
    DRAG
    - VIA SPAGNA, 13
    ENEL
    - VIA CASTELLANA, 201 - sede principale
    ENEL
    - VIA CASTELLANA, 201
    ENEL
    - VIA BIXIO, 39
    ENEL
    - C.SO REGINA MARGHERITA,267

    I have to:

    STEP 1) keep only one record when I've records with same: NAME + ADDRESS + ZIPCODE and delete other

    (I don't want 2 companies with the same NAME + ADDRESS + ZIPCODE)

    please note: before the delete I have to check if the field TYPE is not null... if TYPE is not null I don't have to delete this record and delete the other

     Example if I've
    COMPANY1 - RAMPA LEONARDO DA VINCI STAZIONE TORPEDINIERE - sede principale
    COMPANY1 - RAMPA LEONARDO DA VINCI STAZIONE TORPEDINIERE - sede principale
    COMPANY1 - RAMPA LEONARDO DA VINCI STAZIONE TORPEDINIERE

    don't delete 

    COMPANY1 - RAMPA LEONARDO DA VINCI STAZIONE TORPEDINIERE - sede principale

    delete
    COMPANY1 - RAMPA LEONARDO DA VINCI STAZIONE TORPEDINIERE - sede principale
    COMPANY1 - RAMPA LEONARDO DA VINCI STAZIONE TORPEDINIERE

     
    Result

    ID - NAME - ADDRESS -TYPE  - .....

    1
    - COMPANY1  - VIA SAN RAINERI, ZONA FALCATA, 12
    3
    - COMPANY1  - RAMPA LEONARDO DA VINCI STAZIONE TORPEDINIERE - sede principale
    4
    - AAA - VIA F.LLI CABOTO, 11 - sede principale
    6
    - DRAG - VIA AL MOLO GIANO - sede amministrativa
    7
    - DRAG - VIA SPAGNA, 13
    10
    - ENEL - VIA CASTELLANA, 201 - sede principale
    11
    - ENEL - VIA BIXIO, 39
    13
    - ENEL C.SO - REGINA MARGHERITA,267



    STEP 2) Now I've to keep in this table the
    Head Office and insert in a second table, named ANAGRAFE_SECONDARIE, the seconday Offices of each company

    Whan I've the sane NAME

    10 - ENEL - VIA CASTELLANA, 201 - sede principale
    11
    - ENEL - VIA BIXIO, 39
    13
    - ENEL C.SO - REGINA MARGHERITA,267

    the first  remains in ANAGRAFE table and the others 2 moved in ANAGRAFE_SECONDARIE

    please note:

    If I've a text in  TYPE (TYPE is not null), this company is the Head Office... and the others (with the same NAME) moved in ANAGRAFE_SECONDARIE
    if I've 2 records (with the same NAME) and with TYPE not null, I choose one of 2 records (the first) and second moved in ANAGRAFE_SECONDARIE

    When I move a company in ANAGRAFE_SECONDARIE I've to delete the record in ANAGRAFE

    in the ANAGRAFE_SECONDARIE I've
    ID - rifID (ID of ANAGRAFE) - ADDRESS -ZIPCODE  - ...

    the join is : ANAGRAFE_SECONDARIE.
    rifID e ANAGRAFE.ID


    STEP 3) I've a third table with
    people (keyman) of each company named ANAGRAFE_REFERENTE and I've to join this table with 2 tables create in STEP 1 and STEP2:


    Table ANAGRAFE_REFERENTE
    idReferente - rifID
    (ID of ANAGRAFE) - NAME - ADDRESS - ZIPCODE - cognomeRef - rifSedeAggiuntiva (ID of ANAGRAFE_SECONDARIE)


    STEP A
    I've to search in ANAGRAFE the NAME (company) with the same name in
    ANAGRAFE_REFERENTE
    ANAGRAFE_REFERENTE.NAME = ANAGRAFE.NAME

    and UPDATE ANAGRAFE_REFERENTE.rifID  with the ID of ANAGRAFE


    STEP B

    I search in ANAGRAFE_SECONDARIE if there is a record with
    ADDRESS AND ZIPCODE with the same value of
    ADDRESS AND ZIPCODE  of ANAGRAFE_REFERENTE table
    if I find a record, I UPDATE the
    ANAGRAFE_REFERENTE.rifSedeAggiuntiva with ANAGRAFE_SECONDARIE.ID value
    in all other records (that don't have a match in
    ANAGRAFE_SECONDARIE are set with  ANAGRAFE_REFERENTE.rifSedeAggiuntiva = -1)


    ANAGRAFE_REFERENTE e metto l'idContatto di ANAGRAFE_SECONDARIE in
    ANAGRAFE_REFERENTE.rifSedeAggiuntiva

    I hope that you can help me!!


    Thanks

    Tuesday, January 13, 2009 6:14 PM

Answers

  • User-821857111 posted

    The manipulation that you seem to be attempting can be done using SQL queries.  Take a copy of your database and practice constructing the queries using the Access Query Designer.  It has a wizard for finding unmatched records etc.  If you use that, you should find that the SQL View of the tested and working query will provide you with a lot of help.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 14, 2009 7:31 AM

All replies

  • User-821857111 posted

    I would first import all the records from the Excel spreadsheet into a temporary table, and then perform any validation and manipulation on them once they are in there.  I would also write the queries and save them in the database, and call them one after the other as if they were stored procedures.

    I had a similar requirement a long time ago, and when I read each record from the Excel spreadsheet and checked and validated it one by one before inserting, updating or discarding as necessary, it took at least 50 times longer than importing them into a temporary table first in one batch, and then operating on them as I described above.

     

    Wednesday, January 14, 2009 6:23 AM
  • User-853847052 posted

    Excuse me... my error!!!

    I've already the records in 2 Access tables:

    ANAGRAFE
    ANAGRAFE_REFERENTE


    I've to copy the secondary offices in
    ANAGRAFE_SECONDARIE table

    Delete secondary offices from
    ANAGRAFE table

    Update IDs in of the "key man" in:
    ANAGRAFE_REFERENTE table

    Wednesday, January 14, 2009 6:39 AM
  • User-821857111 posted

    The manipulation that you seem to be attempting can be done using SQL queries.  Take a copy of your database and practice constructing the queries using the Access Query Designer.  It has a wizard for finding unmatched records etc.  If you use that, you should find that the SQL View of the tested and working query will provide you with a lot of help.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 14, 2009 7:31 AM
  • User-853847052 posted

     Thanks for your reply!!

    I know SQL queries (INSERT, UPDATE, DELETE) but I don't know how to apply each single query to my problem.... with the "if" condition that I've written!

    Can you help me?

    Wednesday, January 14, 2009 8:13 AM