Results 1 to 11 of 11
  1. #1
    TrulyVisceral is offline Total N00B
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Deimos
    Posts
    102

    Smile Have a button that deletes everything from table 1, and another moves table 2's content to it.


    This is just 1 of 2 things I need to finally finish this DB I'm working with.

    It's an Incident Management System.
    Basically, I just have 2 tables. One for the previous month, and one for the current.
    Of course, when the month ends, I need to get rid of the previous, and the currente then becomes it.
    I already have something that exports all the raw data to an Excel document, as well as the graphs, so no worries about backups there.

    Is it possible to have a couple buttons that do that?
    1)Delete previous month
    2)Move current to previous
    3)Delete current.

    Of course, if they could give a warning, that'd be swell.

    In the case of helping out with code, the tables are simply PastMonth and PresentMonth.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    This is not how data should be stored in Access.
    All the data should be stored in one table & queries used to determine the current data - in this case the current month
    Also, it goes against good practice to delete data EVEN if you are string backups in Excel

    So whilst you could run the following in turn using a procedure called from a single button click
    1. CurrentDB.Execute "DELETE * FROM PastMonth;", dbFailOnError
    2. CurrentDB.Execute "INSERT INTO PastMonth SELECT CurrentMonth.* FROM CurrentMonth;", dbFailOnError
    3. CurrentDB.Execute "DELETE * FROM CurrentMonth;", dbFailOnError

    Or use DoCmd.RunSQL instead of CurrentDB.Execute so you get warnings

    However I really do NOT recommend it whichever method you use
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    TrulyVisceral is offline Total N00B
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Deimos
    Posts
    102
    Quote Originally Posted by ridders52 View Post
    Stuff
    That's kinda what I need to do though.
    Once the month is over, it gets moved to the past month and whatever was there gets deleted.
    I originally had something like a table for each month, and then after the year is over, THEN everyhting gets moved somewhere, but it got super complicated.
    It is not going to get used again, but just to be kept somewhere else to keep track of older incidents.
    And I need a table for previous, and one for current, since the graphs are going to get compared to each other, to see how the months go.

    IDK what I'm writing anymore. Just saying that this is how it's got to go. Unless someone got a much better system?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    'Got to' is perception. I agree with Colin - don't 'move' data, apply filters. Even for previous and current. Your life will actually be much simpler.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do you ever have the need, or has anyone considered statistics/counts:
    -this year vs last year
    -this month this year to this month last year

    Perhaps your table structure makes it "super complicated".
    If your design allowed for year and month(reportingDate), then an appropriate query could select any particular range of data based on "yourDateCritieria".

    It isn't common to delete data monthly ---not unheard of -- but it all depends on the requirement and usage of the data.
    Just a few thoughts to consider, and reject from a point of knowledge rather than --jeez we never thought of that.

    I'm not sure what an Incident is in your context, but in other contexts I can think of you would keep statistics:
    -temperature measurements,
    -population of city/prov/country,
    -crimes by type by city/country,....

  6. #6
    TrulyVisceral is offline Total N00B
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Deimos
    Posts
    102
    Quote Originally Posted by orange View Post
    Legit concern
    No, it's never this year vs last year. It's always this month and last, in fact, the excel sheets I was given to work with only went back to November 2017, up to February 2018 (I started this on March). I asked my supervisor specifically what he wanted, said this was alright.

    Also, the incidents are system failures. Specifically, dealing with banking systems. ATM, POS, Phone lines, and a bunch more.
    Even more specifically. What the failure was, how long it lasted, what the solution was (if it was solved, that is), and a bunch of other stuff. 34 columns Specifically.

    Believe me, I found this quite weird as well, but... well, read the first line again. And a lot of times, the operator doesn't write all the data, in fact, things that seem quite important (like, for example, the time the incident was closed, necessary for one of the graphs) are just missing.

    YES, I DOUBLE POSTED, I'M SORRY, I DON'T KNOW WHAT HAPPENED.

    Should I explain how this whole ordeal works? With the incidents and whatnot? Just for context ya know.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    No need for more details. You are working with the project sponsor and have clarified the scope.

    I'll delete post #6 (the first of your dups).
    Good luck and thanks for getting back.

  8. #8
    TrulyVisceral is offline Total N00B
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Deimos
    Posts
    102
    Quote Originally Posted by orange View Post
    No need for more details. You are working with the project sponsor and have clarified the scope.

    I'll delete post #6 (the first of your dups).
    Good luck and thanks for getting back.
    So... I do it then? It feels like I'm commiting a sin with how y'all've described it.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I deleted the duplicate post. You did well --you told us what the project sponsor wants and you are working closely with the sponsor.

    Do you have other specific questions?

  10. #10
    TrulyVisceral is offline Total N00B
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Deimos
    Posts
    102
    Quote Originally Posted by orange View Post
    Do you have other specific questions?
    Well, sort of.
    The graphs in forms update with the info in the tables, and that's fine.
    But it seems the ones in the reports don't, once made, that's how they stay. Kind of annoying that.
    Maybe I should start a new thread for that though?

    Regardless, I gotta go for now. My shift is over. Be back l8r.

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    But it seems the ones in the reports don't, once made, that's how they stay.
    A form can be refreshed so the "graph" can change.

    A report is like a snapshot -- a photograph
    It simply presents the data --it is not active.

    If you rerun a report design using new data, that new data will be reflected in the report.

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

Similar Threads

  1. Table content and relationships help
    By RichardR in forum Database Design
    Replies: 5
    Last Post: 10-22-2016, 02:04 AM
  2. Default Value from Table Content
    By jaimefry3 in forum Access
    Replies: 6
    Last Post: 03-07-2016, 11:49 AM
  3. auto refresh the table content
    By mohammad8065 in forum Access
    Replies: 1
    Last Post: 02-27-2012, 01:54 PM
  4. VBA Doesnt add content of subform to table
    By shabbaranks in forum Programming
    Replies: 14
    Last Post: 12-16-2011, 03:47 AM
  5. Replies: 1
    Last Post: 12-01-2010, 11:01 AM

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