Results 1 to 3 of 3
  1. #1
    willster88 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    1

    Copying records from one table to another

    Hi



    I'm currently making a database for a construction company. The company wants a database of snags (things that go wrong in the houses they build, boilers not working etc), which is what I've made below.

    In the attached database, you'll see three tables: all snags, active snags and closed snags. Records will be saved saved in the "all snags" table by means of a form.

    What I want to happen is that when the user saves a new snag, all the data is copied to the "active snags" table in a new record as well as being saved in the "all snags" table: essentially the same records exist in both tables.

    Also, when the user goes back and changes the record and clicks the yes/no box to yes in the "Completed?" field, the database should automatically delete the record from the "active snags" table, but create a new record with exactly the same data in it in the "closed snags" table, so now the same record exists in the "all snags" table and the "closed snags" table.

    You might ask what the purpose of this is. It is because the managers will very rarely be going back through the closed snags, 99% of the time they'll want to be looking at just the active snags. Doing the database this way means that they can be shown the table with just the active snags in, apart from when they want to see the closed ones.

    I'm very new to this sort of thing, so please try to make answers as simple as possible!
    Thanks in advance,
    Will

    (Access 2010, 64 bit)

    Snag Management.zip

  2. #2
    Snayjay is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Location
    Gulf of Mexico
    Posts
    10

    Active 'Yes/No'

    Wouldn't it be easier to just create a Boolean field "ACTIVEREC' and filter your records by it? One table, less records. (Edit) Sorry after I replied, I read you said you were new. Let me explain a bit more.

    In your table (sorry didn't look at your example, I'm at work and cannot download it). You should add another field, call it "ACTIVEREC" and make it "yes/no". When you are on your forms and you do your record source and I'm assuming you are doing a query. Just put -1 in the criteria under ACTIVEREC to view active records and 0 in ACTIVEREC to view the non-active records. Of course that also means you have to make a spot on some form to indicate and update the table to reflect when a record becomes inactive.

    Sorry if I've confused you, maybe someone else who can download your example can elaborate on it.
    Last edited by Snayjay; 03-22-2014 at 12:20 PM. Reason: More information

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    +1 for a single table with a status field. Easy to use a query or filter to view open, closed or all as desired, with the same form if you want.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 2
    Last Post: 01-02-2014, 03:05 PM
  2. Copying records
    By Lowell in forum Access
    Replies: 3
    Last Post: 09-07-2012, 12:26 AM
  3. Replies: 5
    Last Post: 12-08-2011, 10:52 AM
  4. Replies: 1
    Last Post: 06-08-2011, 02:58 AM
  5. Replies: 6
    Last Post: 03-22-2011, 08:22 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