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
195 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
 

Author Comment

by:groovymonkey
ID: 35172727
I am getting a synatx error for the query
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
'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 …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now