Results 1 to 3 of 3
  1. #1
    Vaslo is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    23

    Better way to wipe and reinstate data for a weekly report than what I describe here?

    I started a similar discussion here for full disclosure, but I think my question wasn't interpreted correctly and now I have a better way of asking (I think.)
    https://www.accessforums.net/databas...ing-46934.html



    Basically as I mentioned, I have a sales report I'd like to manage in Access. There are a number of linked tables, and I need to pull data every week to update it. As I mentioned as well, referential integrity holds me back from simply overwriting the table with the new data. The easiest thing to do would be just a quick import each week, which just can't be done .

    My workaround is this: I have a table called "Weekly_Data" that feeds all my queries and reports. To update the data each week now, I think I will download the new data to another table - "Weekly_Data_Pull". I will run a delete query to clear the "Weekly_Data" table and then use an append query to just refill the data from the Weekly_Data_Pull. Not ideal, but not too bad. I have a feeling that there are better ways.

    Any suggestions or problems you see here? I don't know much at all about SQL, so I don't know how that works or how to even implement it. I plan to learn it in the long run but need to get some results going this week.

    As an aside - would love to do daily which would be my ultimate goal, but I'd need a fast process. Sometimes I'd have to update my other tables as well since Weekly data would have new cities, products, etc entering it - so it would be best to just update all of these each week (or each day!)

    Thanks.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    This is EXACTLY how I do it. I have local 'report' tables.
    I empty, run with my criteria, then report.

    Works great.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My thoughts....

    So from your previous discussion you know your structure needs work. Even given that, just deleting the data in the table "Weekly_Data", then importing (appending) new data won't work because of referential integrity.

    Let's just look at just 2 tables: "Weekly_Data" and "ProductInformation".
    So you delete the data in table "Weekly_Data", then start importing the new data.
    If the 2nd record to be imported has a new product that is not in the "ProductInformation" table, that record will not be added. There must be a record in the "ProductInformation" table first (due to referential integrity).

    But you could have a button (Import New Weekly Data) with VBA code behind it to:
    1) Delete the data from the table "Weekly_Data"
    2) Open the CSV file
    3) Read one record
    4) Check if the product is in the "ProductInformation" table.
    5) If the product is new, add it to the "ProductInformation" table.
    6) Do the same for that record for all fields involved with referential integrity.
    7) Append the record to the "Weekly_Data" table.
    8) Repeat steps 3 - 7 until all data (records) have been read from the CSV file.


    My $0.02

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

Similar Threads

  1. Creating Weekly report from Cumulative Totals
    By Sackface in forum Access
    Replies: 3
    Last Post: 03-14-2013, 11:17 AM
  2. Replies: 1
    Last Post: 10-18-2012, 12:01 PM
  3. How to wipe a form
    By Paul H in forum Forms
    Replies: 7
    Last Post: 10-12-2011, 03:07 PM
  4. i can't even think how to describe it...
    By emmahope206 in forum Queries
    Replies: 5
    Last Post: 05-08-2011, 01:41 PM
  5. Excel Import/Append Data (weekly)
    By MartinL in forum Import/Export Data
    Replies: 1
    Last Post: 08-12-2010, 06:14 PM

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