?
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
?
202 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
[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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

800 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