Lookup multiple values within same table Access

Question

Hello,

I am trying to figure out how to lookup multiple values from within the same table in Access. I want to query multiple item codes, say 50-100's, and return their corresponding records from the table. I don't have time to spend entering operators or hundreds of item codes individually into the query criteria. Is there a way I can copy and paste values and look them up from within my table?

Thank you!

Answer 1

Link to the Excel file, or import it into a temp table.

Create a query with your table of SKUs and that linked or imported table.

Create a join in SKU.

Where the SKU's match, they will be returned by the query.

You have a list of requested SKUs.

Answer 2

That's why I suggested you also consider a categorization strategy. Instead of listing 200 individual SKUs, group them in some logical way and select the groups.

Answer 3

Hello,

Thank you for your quick response. Our team uses a variety of item codes for our data maintenance. An example of my use case would if be someone asks us for information on 200 SKUs. I would like to look up these SKUS in Access and export the records returned from the table to an Excel file.

I did look up multiselect as you had mentioned, but I just wasn't sure if there was a better way to look up all of those codes, rather than selecting them one by one, especially if we are looking up 200.

Answer 4

Hello,

I am trying to figure out how to lookup multiple values from within the same table in Access. I want to query multiple item codes, say 50-100's, and return their corresponding records from the table. I don't have time to spend entering operators or hundreds of item codes individually into the query criteria. Is there a way I can copy and paste values and look them up from within my table?

Thank you!

1.

Let’s say I have a Table with an OrderID field of Data Type Number (you don’t give us much detail to go on).

This field is normally 5 numbers long.

2.

I want to sort those records so that I get:-

 - records between and including the number 11079

 - up to and including the number 11100

22 records should be returned.

Take the following actions:-

3.

Open the Table in Datasheet View then click on the first row and in its OrderID field.

4.

Now right click then hover the mouse over:-

Number Filters

 - then click on:-

Between . . .

 - so that the window called:-

Between Numbers

 - opens.

5.

Change the window called:-

Between Numbers

 - to read:-

Smallest: 11079

Largest: 11100

 - then click:-

OK

6.

You should now have the 22 records you wanted.

Does that give you what you want?

7.

Please note that you can perform a similar operation to that mentioned above for Text fields.

Answer 5

There are probably a couple of approaches that could work, but we'll need more details.

I would consider a multi-select list box on a form that displays all of the possible item codes. With a mult-select list box, you do have to select items, but that should be much less tedious and less risky than typing them in to a text box. Then, you can run a function that collects each of the selected items in to the parameter for the query. Check Ken Sheridan's OneDrive,where he has many sample databases. One of them probably includes this approach. One is called "MultiSelect" which sounds relevant.

Another possibility is that you can create, or identify, a hierarchy for the item codes so that instead of selecting each one individually, you could select categories that included groups of item codes.

What approach seems like it might work for you? We can probably offer sample code, once the actual picture is more clear.