Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Posts
    8

    Unanswered: Separating out new and unique data from two tables

    Hi all!

    I have a quick problem. I am using Access 97

    Every week by e-mail from a supplier one I get an excel file with 7000 or so data entries (special offers).

    I import this into Access and then merge it with Word to produce a printed catalogue. A lot of this data duplicates week from week and the manual job of sorting through and deleting out previous entries all 7000 entries is getting too much (and is not reliable - I'm only human after all)!!

    Is there a way (probably with a query) that I could automatically compare the latest table with the previous week' s table and produce a new table that only contains the changes (i.e. only the new data that has been added since the previous week).

    This would make my job soooo much easier!!!

    A primary key (?) could be the bar code or unique product reference.

    Any advice would be greatly appreciated.

    Cheers!

    Jonti

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Does the excel spreadsheet only contain changed or new products or does it include everything?

    If it's everything - run a delete query on the table to nuke all the data - then insert all the new data.
    Code:
    'In VBA this is the command
    doCmd.RunSQL "DELETE FROM <yourtable>"
    If it's changed: Update query
    If it's new: Insert query

    Orrr you could include dates in your spreadsheet and then use your database to pick up only unique entries maximum date record.

    -GeorgeV
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Posts
    8

    I should have said that I'm not to too proficient in Access...

    Thank you very much for your further advice.

    The 7000 data entries are uniform with say 7 fields (bar code, description, catalogue number, price, date offer expires etc).

    The excel spreadsheet contains all the data (7000+ entries) and much of the data is repeated each week. The offers are time sensitive and only get deleted off the spreadsheet (or table) when the special offer date has expired. New titles get added seemingly at random (it is not just a case of seeing what titles have the longest offer expiry date, some are offered for only a very short time).

    So it would seem that the date is not a unique identifying factor, as new titles are added with similar dates to items already on the list (I hope that makes sense).

    The barcode or catalogue number would, I think, be the only unique identifyer.

    I am afraid that I don't know how to run the script that you have suggested.

    Can you give me a 'dummies' guide as to what I should do.

    Many thanks

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I was referring to the date as the "date of import" rather than promotion - this would allow you to pick out the most recently imported data etc.

    However, from what you are saying it sounds like - if you delted all the data in your table and ran the import - you would get the information you need?
    Correct?
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Posts
    8

    I think we're getting there...

    Thank you again...

    How would I go about doing this?

    (sorry for being so dumb)

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    No apologies necessary

    Firstly, I'd just like to ask how you are running this import?
    Is it programatically using VBA/Macro or is it using the menus in access (I forget where the "import..." is hidden).

    Depending on how you do this - we can create you a solution to work alongside with little or no problem (famous last words)!

    - GeorgeV
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Posts
    8

    I do it like this...

    In Access:

    File>Get External data>Import

    I then select the file (excel) and import it as a table (no primary key selected in the import process).


    Thanks once again

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ahh I see.

    Well, what is your level of coding?

    I have two solutions - one that involves some very complex VBCode that automates this process behind one button (including the file dialog so you can pick the file).

    The other is much easier and requires you to make a form with a button that deletes all the data from the current table and then you run the import as you normally would.

    Which would you prefer?
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Posts
    8
    My level of coding is almost non-existant...

    I think the easier option would be best although the first one sounds very good...

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Create a form in design view and add a command button.
    You will be prompted by the Command Button Wizard - click cancel.
    Right-click the button and chose "Build Event..." >> "Code Builder" - click OK.
    You are now in the Visual Basic editor.

    Copy the following code into the Sub.
    Code:
    doCmd.RunSQL "DELETE FROM <Insert Your Table Name Here>"
    'Do not forget the quotes around the SQL statement
    Save the code and quit the VB editor.
    Save the form.
    Open the form and hit the button.
    Go check your table - it will now be empty!

    Hope this helps - GeorgeV
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Posts
    8
    Many thanks for all your help...

    I'll give this a try

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    No problemo

    Let me know how this goes and feel free to ask any more questions!
    George
    Home | Blog

Posting Permissions

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