Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    36

    Unanswered: import unique append

    Hi,

    I recieve daily reports as plain text which I need to import and append to a table. The report consists of three fields (MachineID, date, transactions).

    There is a chance that the report may contain an entry contained in a previous days report. e.g 13966 12/06/03 20045 is in the report i get on the 12th and the 13th.

    How can i be sure i only append unique entries?
    I was thinking of trying to create a unique id for each record by joining the date and machineid fields. Is this necessary? How would i do this though.

    Thanks

    Nicky

  2. #2
    Join Date
    Apr 2003
    Posts
    42

    Try this....

    I started to write and suggest a way you could do this with code, and eventually wrote the code in the process.

    If you know how to program in VB my example should be helpful. (Although it could be more effient)

    If you don't... Well, it works and you may find it some use.

    Open the form frmUpdate in the example DB.

    The code behind the [UPDATE] button will read thru the table tblMachineTransTemp and update it to the table tblMachineTrans if there are no matches of the same MachineId AND Date.

    Cheers!

    Ventelation
    Attached Files Attached Files

  3. #3
    Join Date
    Dec 2002
    Location
    Glasgow, UK
    Posts
    100
    If you don't want to use code you could create a multiple field unique index based on the three fields - if you try to import a duplicate value access will not append it to the table (you will get a warning)

  4. #4
    Join Date
    Mar 2003
    Posts
    36
    Thats great. Thanks ven for the db. really helpful.

  5. #5
    Join Date
    Apr 2003
    Posts
    42

    Re: import unique append

    Originally posted by nicky w

    How can i be sure i only append unique entries?
    I was thinking of trying to create a unique id for each record by joining the date and machineid fields. Is this necessary? How would i do this though.

    I did not know till just now how to do this, but it is quite easy to do in Access.

    1. Open your table in design view.

    2. Use CTRL-Click to select the two fields in your table you wish to be unique. (ie. Date and MachineId)

    3. Click on the icon that looks like a key on the Toolbar.



    (Like Xander wrote....)

    Now, if you try to append records that challenge the uniqueness of Date AND MachineID, they will not be entered. All other data will be entered.

    It's a lot simpler that writing the code but possibly less versatile.

Posting Permissions

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