Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

copying from one table to another in ms access 2007

Posted on 2013-02-06
6
Medium Priority
?
422 Views
Last Modified: 2013-02-06
Hi.
If I have a database table with 20 records and want to copy a few records that have been checked off to second existing table (using a check field in the first table) how is this easily accomplished. I assume you have to do it in VBA as I am using access 2007.
e g
TABLE 1
record 1
record 2 (checked)
record 3
record 4
record 5 (checked)

TABLE 2
(contains 50 records)

simply take record 2 and 5 and copy them into TABLE 2. Then uncheck the two records.

I am not sure if I have to reopen the tables in VBA? I have a check field in TABLE 1
0
Comment
Question by:PawloA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 85
ID: 38861115
You can do this:

Currentdb.Execute "INSERT INTO [Table 2] (Col1, Col2, Col3) SELECT Col1, Col2, Col3 FROM [Table 1] WHERE MyCheckBoxField=True"

Currentdb.Execute "UPDATE [Table 1] SET MyCheckBoxField=False"

Obviously you'd have to change the columns and field names to match your own.

You don't have to open the tables.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 38861125
You could make a select query containing the checked items from Table 1, and then make an append query to append all but the key field for these recods to Table 2.  Then an update query to set the Yes/No field in Table 1 to False (unchecking these records).  After testing these queries, you could run the append and update query from VBA code, using OpenQuery.
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 38861128
That will be two queries:  INSERT and UPDATE

INSERT INTO Table2 (column1, column2, the_check)
SELECT column1, column2, the_check
FROM Table1
WHERE Table1.the_check = True

... then ...

UPDATE Table1
SET the_check = False
WHERE the_check = True
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 7

Expert Comment

by:Steve
ID: 38861132
You could do it with an append query. No vba needed.
0
 
LVL 1

Author Closing Comment

by:PawloA
ID: 38861635
actually it would be append instead of insert I believe
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38861650
Thanks for the grade, although I believe LSMConsulting had the same answer, and posted it before I did, so really he should be awarded at minimum half the points, probably more.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question