Results 1 to 3 of 3
  1. #1
    kilo21 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Posts
    7

    Unhappy Filter data into multiple tables?

    Hi,

    I usually import rows of data into a blank table in Access, once every day for further processing. Unfortunately this data may also contain invalid rows and/or the ones that I have processed before. I want to sort the data here and move them into 3 other tables; Accumulated, Latest and Rejected.

    Accumulated contains list of rows that have been processed at one time. Latest contains rows which are not found in Accumulated, meaning data that would be transferred to this table are fresh for that day. Rejected table shows rows which do not meet a criterion, that is having 3 or 9-digit ID number instead of the correct 6-digit.

    To save time, how do I create a macro or button that would ask Access to automatically scan the rows in the blank table, sort and then move them into the 3 different tables depending on what they represent?



    Highly appreciated. Thank you in advance.
    Last edited by kilo21; 06-08-2012 at 04:31 AM. Reason: grammatical error fixed

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    automatically scan the rows in the blank table
    The 'blank' table??

  3. #3
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Once you have imported the data - can you not just use a query for the 'Latest' data?

    I have several databases that run periodically and import data into Tables.
    What I usually do is have queries that filter out the data I need.
    In my case most of the data goes into Reports that get written to the Network - but you could - if you really needed to - convert the 'Latest' query into an 'Append Query' that will put data into whatever table you specify.

    You could create a Macro that:
    Deletes old data from your Import Table ['blank' table?].
    Imports the new data into the blank table [which has been emptied in the previous step].
    Deletes the data from the 'Latest' Table.
    Runs the Append query to put the 'Latest' data into the 'Latest' Table [which has been emptied in the previous step].

    It should be pretty easy to do - unless you have complex other criteria.

    Let me know if you have questions.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-12-2013, 12:53 AM
  2. Replies: 4
    Last Post: 01-31-2012, 12:42 PM
  3. Replies: 1
    Last Post: 11-19-2011, 10:36 PM
  4. Compare Data in Multiple Tables
    By Access_newbie in forum Queries
    Replies: 7
    Last Post: 07-26-2011, 11:35 AM
  5. Linking data fromp multiple tables
    By shanej100 in forum Access
    Replies: 4
    Last Post: 02-23-2011, 12:11 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums