Improve company productivity with a Business Account.Sign Up

x
?
Solved

How to populate a new table with values from another table based on a specific values for a field in MS ACESS

Posted on 2011-03-19
9
Medium Priority
?
206 Views
Last Modified: 2012-05-11
Hello,
I have a 90,000 db that I want to trim down to a smaller version based on the cashierid...I have 10 cashierids that I want to move all records assocaited to them in to a new table...

e.g.

pseudo code....
SELECT * into smallerTable
FROM t2
WHERE cash_id is "suzy1123" or "barb2523"

obviously I am not sure of the code...help appreciated
0
Comment
Question by:groovymonkey
  • 4
  • 3
  • 2
9 Comments
 
LVL 2

Accepted Solution

by:
michael_madsen earned 2000 total points
ID: 35172621
try this:

INSERT INTO smallerTable (cash_id, otherfield1, otherfield2)
SELECT cash_id, otherfield1, otherfield2
FROM t2
WHERE cash_id='suzy1123' or 'barb2523'

you need to substitute otherfield1 and otherfield2 with the actual fieldnames from your tables.
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35172693
If you have the table already created then just Select and Insert, as follows:

INSERT INTO Table2 (Field1, Field2)
SELECT Field1, Field2
FROM Table1 WHERE (Field1 = "suzy1123") or (Field1 = "barb2523") ;
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35172712
Groovymonkey,
Please ignore my post. It is already provided by Michael_mad's.

Sorry Michael_mad, did not refresh before post.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 

Author Comment

by:groovymonkey
ID: 35172727
I am getting a synatx error for the query
0
 

Author Comment

by:groovymonkey
ID: 35172804
Owh and this it so go in to a new table
0
 
LVL 2

Expert Comment

by:michael_madsen
ID: 35175244
Could you provide the actual field names of the tables?
0
 

Author Comment

by:groovymonkey
ID: 35175299
yes will do...will post tomorrow as I am not at the cpu that has the db on it
0
 

Author Comment

by:groovymonkey
ID: 35180754
table name is t2...fields are cashierID and itemId

so for this table it would be the following...although I do not have a Table2 created

INSERT INTO Table2 (itemId, casheirId)
SELECT itemId, casheirId
FROM t2 WHERE (cashierId= "suzy1123") or (cashierId= "barb2523") ;
0
 
LVL 2

Expert Comment

by:michael_madsen
ID: 35181284
Yes, that's right.

You need to create the other table (Table2 or what you want to call it) with the same two fields: "itemId" and "casheirId".
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Through the video, you can check the migration process of Outlook PST file to PDF. Kernel for Outlook to PDF tool can convert Outlook emails with all attributes like Subject, To, From, Cc, Bcc and other folders such as Inbox, Outbox, Sent Items, Jun…

595 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