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
201 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 500 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
Industry Leaders: 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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

717 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