Collect the rows that match a list of numbers

[MMTneedsHelp]

I hope someone can help me with this. I have a massive table that has all
kinds of freight information. I have an xls file that has 300 rows of
different freight numbers. I would need to collect the rows that has these
numbers in their row/column. Is it possible to make a query that use so many
values from a separate file as a criteria? If neccessary, I don´t mind making
a new table from the xls file.. I guess a sort of "look up criterias" thing
is in order...

 

[Dale Fye]

You can either link or import the Excel file into Access.

Once you have done that, create a new query.

Then add the "massive" Access table and the table you imported/linked from
Excel to the top of the query grid.

Next, find the field in the Access table that is supposed to match the
freight numbers from the Excel table. Click on that field (hold it down),
and drag it across to the field in the Excel table that it is supposed to
match. This is called an inner join, and maps records from one table to
records in another table. When you run a query with this type of join, you
will only get records back which have matching values in both tables.

Now, identify those fields from the "massive" Access table that you want to
display and add them to the grid.

Now, run your query.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[John Spencer (MVP)]

If possible, set up a link to the Excel sheet or import the data from the
Excel sheet into a table in Access.

Then you can add your table and the excel sheet into the same query and
establish a join between the table field and the Excel data.

If you need more help on setting up the query, post back.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[MMTneedsHelp]

Thank you, it solved it. I was thinking way too complicated to solve this.

The real issue with this is that I would need to make a really easy user
interface to automatically do the process. I´m trying out to do it with a
macro, but it seems a bit too complex to send all the info automatically to
get through the import wizard...

The best thing would be to tell the user that, simply put this new file in
this folder every monday, open the access form and hit the button.