Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Consolidate data in same record (Access 2003)

    Hi,

    Please help give me a general idea of how to do this....I do not have a sample DB to send <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    I have a Main table with records in it. Say that one of the records has a Primary Key of "B1" and a Value field with a value logged as 100.
    The next month I get an Excel File with new data. I import the file as a table into Access. The table as the same fields as the Main table. One of the recods in this new table is Primary Key "B1" and a value filed with a value of 10.

    I want this record to be updated to Primary Key "B1" and Value field with value 110. (It must consolidate the data by adding).
    In the same light, all new records which do NOT have a duplicate Primary Key must be appended to the Main table too. (IE. Adding the entire record, not replacing the record by consolidation.)

    Any ideas??
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Consolidate data in same record (Access 2003)

    Create two queries:

    1) Create a query based on the two tables, joined on the primary key field.
    Add the Value field from the Main table to the query grid.
    Select Query | Update Query.
    Enter the following expression in the Update to line, substituting the correct names:

    [Main].[Value]+[Imported].[Value]

    This query will update the matching records.

    2) Create a query based on the imported table.
    Add all fields to the query grid.
    Enter the following expression in the Criteria line for the primary key column, substituting the correct names:

    Not In (SELECT [PrimaryKey] FROM [Main])

    Select Query | Append Query and select the Main table as target.
    Access should automatically fill in the field names in the Append to line, but check carefully.

    This query will add the non-matching records to the Main table.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Consolidate data in same record (Access 2003)

    Wow...tx. Your steps look very clear. I will gve it a try ASAP, but it looks pretty sound just reading your steps.
    Cheers Hans!
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •