create a new table from an old table with a list?

[thewhtridr]

So, I dont even know if access does what I am asking but I thought I
would ask....
this is a situation I run across fairly often and I have been looking
for an answer.

So here is the problem:

I have a table of data, usually fairly large, and a list (be it in
excel or a text file or somewhere), the list is a list of records in
that database. I need to make a new table of data containing only
those records that appear on the list. Is there a way to do that in
access or any other software, any suggestions would be appreciated.

(e-mail address removed)

 

[tina]

assuming that the "list" is a list of unique values that can be matched to
specific records in the data table, then yes, you can easily do it in
Access. you can import both the list and the data table into new tables in
an Access database (or link them, rather than importing). then create a new
query in Access, adding both tables to it, and linking the tables (within
the query) on that unique field. pull the fields you want to see from
either/both tables into the query design grid. then on the menu bar, click
Query | Make Table, and follow the prompts to create a new table of the
matched records.

ht

 

[Granny Spitz via AccessMonster.com]

I need to make a new table of data containing only
those records that appear on the list. Is there a way to do that in
access or any other software

You've come to the right place, hon! Relational databases are perfect for
finding "related" records. If your data in the "list" is stored atomically,
that is, there's *only one value* in each column in each row, then you'll
have an easy time of it. If the data in the list is just rows of
disorganized data, then it's a job you'll want to hand off to your least
favorite cubicle dweller.

You haven't described the structure of your data, but I'll try to give you an
example that's easy enough to follow. Say your boss needs a list of all the
customers in the sales region you're responsible for, the NorthEast region of
the U.S. You have a table of all the customers in the U.S., and you have a
text file with a list of the states in each region. It looks like this:

Region,State
NorthEast,New York
NorthEast,Pennsylvania
NorthEast,New Jersey
NorthEast,Connecticut
NorthEast,Rhode Island
MidAtlantic,Maryland
MidAtlantic,Delaware
MidAtlantic,Virginia
MidAtlantic,North Carolina
etc.

Create a table link to this text file, then create a new query and add this
table and add your other table of customer records, which includes a column
named "ST" for each customer's state. In the diagram, drag the "State" item
in your linked table over to the "ST" item in your customer table to create a
link (relation) in your query, then drag the other items (columns) down into
the lower grid from each table that you want to show in the resulting query.
Make sure that the Region column in your linked table is included in the grid,
and in the grid Criteria for the Region column, type "NorthEast" so that only
the records for customers in the NorthEast region will show up in the query.
Run the query to see if you have the records and columns you want.

When you're satisfied, go back to design view and select Query | Make-Table
Query... in the menu. Name the new table that the make table query will
create, then run this query and it will create the new table for you with the
records you wanted.