Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008
    Posts
    5

    Unanswered: Synchronise Data in Access

    Hi all,

    I hope you might be able to help.

    I have today setup an Access database for work as we were using Excel but it has become far too hard to manage.

    We receive a list of properties which are empty every month in an Excel spreadsheet. I've managed to import my first set (March) successfully into Access. The idea is that we'll supplement Access with investigation notes etc having one access record per property (each property has its own unique ref. number).

    However, next month (April) I'm going to receive a new list of empty properties which will include some from the previous month and some new ones.

    I want to import this into Access but tell Access:

    - Not to import ones which are duplicated (as there will already be case notes on file). I presume it'll be able to check the unique reference number;
    - Insert non-duplicated entries (i.e. any new properties which have come on);

    However, I also need to be able to identify those which are no longer empty and mark them as non-empty BUT not delete them (as we need the records on file).

    That means that running a report in April will give me the same number of empty properties as the Excel spreadsheet.

    Is there a way to do this (not manually as we're talking about 2,000 records per month!) or is it going to become too complicated for Access?

    Thanks

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    It shouldn't be too difficult but you'll need to do a little bit of vba programming or manually running of a couple of queries to modify/add records in your data table. Here's a suggestion showing a couple of queries you'd run:

    I had a very long description here but I thought it best to just show you an example and explain the example. In the attached example, you would import your excel data into a table called TempImportTable (every time you import the excel data, it would be to a table called TempImportTable.) Then a couple of queries are run which update the data.

    After you import the new data, you'd then run the "AppendNewDataFromImportTable" (which prompts you for the DataMonth for the new set of records) or "AppendNewDataFromImportTableEx2WithAutoDate" (which automatically sets each record of the new data to the 1st of the current month.)

    The concept with having a DataMonth field in the data table is that you want to 1. Have a DateMonth field to distinguish the dates of the newly imported records. and 2. Make it a date field type so you can do future searches on a range of dates.

    Also note: UniquePropertyID in the example table might be your unique property ref number.

    Then optionally you would run the 2nd query called "UpdateMatchingDataFromImportTable" which updates the matching unique property ID's from the TempImportTable to the data table (note: I didn't update the DataMonth field in the query for the data table but you can copy the expression from the Append query to the Update row of the DataMonth field for the data table.) Hopefully that makes sense for you to edit the Update query if you want to do this. The SomePropertyData1 or 2 field might be the new "non-empty" data from the newly imported TempImportTable, otherwise, you could modify this query to change your field which identifies the record as "empty" to "non-empty" (again - I hope that makes sense as an example.)

    SCRATCH THAT ON THE ORDER OF THE QUERIES - you'd want to run the update query FIRST!, then the Append query 2nd (otherwise you're updating the records you've just imported.) Make sense?

    I did this example very quickly to just to show you a couple of example queries. What would be left to do is perhaps design a form with a button to automate the importing of the new data import table and automatically run the queries. I personally like to get fancy on the form and have a "Browse" type button which let's the user browse for the excel import file but you can find some examples of this (not sure but there might be one in the code bank of this forum.)

    I hope this helps. The table/fields will most likely be different in your mdb but the running of the queries concept is similar to update your data table.
    Attached Files Attached Files
    Last edited by pkstormy; 03-18-08 at 18:50.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2008
    Posts
    5
    Thanks for the response and sorry for not coming back sooner.

    I've managed to setup the Temporary Table fine and insert the data.

    Based on your suggestion, I've setup the query to insert the date and then integrate the new entries into the original database. However, whenever I try and run the report I get 'duplicate output destination' (for the account reference).

    When I'm in design view and I go to database view, it asks me to insert the date and takes me through to the entries which I want to see (i.e. the new entries which aren't duplicated in either database).

    Any ideas how to solve the problem?

    That will hopefully integrate any new fields.

    I'm confused about how I can change the status of non-duplicated entries in the original database to closed to show that they are no longer empty. Is there a quick way of doing this?

    Thanks!

Posting Permissions

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