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
197 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 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

776 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