Query to select multiple items from table

[NightWalker]

I am needing direction on what would be the thought process to make this query to work.

I am trying to write a query to select multiple serial numbers from my table tbl_Tested343s. Normally I get the results of the tests based on date and load tested. However an Engineer came to me today with a list of 14 serial numbers and no idea when they were tested. he wanted the results. currently I had to go directly to the table and search for the serial number manually and write the results down.

What I would like to do is be able to have some kind of for where I can enter any number of serial numbers and click a search button that would send all of the info to a report for printing or exporting to a pdf to email to the engineer.


Just looking for a little direction here and I will run with it from there. May have questions later.


Thank you all for your help.

Walker

[ItsMe]

You can concatenate each serial number onto a WHERE clause of a SELECT statement using the OR condition. However, doing so in VBA, you may find the character limit to your string if you add too many serial numbers.

Another approach would be to append the serial numbers to a temp table and then JOIN that column to the production table.

[ssanfu]

You could create a query on your table, adding the fields you want to see.
Under the SerialNumber field, on the Criteria rows, add the S/N's. Switch to SQL View to see what the SQL looks like.

More advanced, you could have a form with text box controls named consecutively, looping through the controls to build a WHERE string (VBA).
Append the returned records to a temp table. (don't recommend using Make table queries).
Once the S/Ns are in the temp table, a report based on a query could print the results.


Edit: Drat....Itsme beat me again!!

(I do like the temp table JOINed to the production table idea)

[orange]

I think ItsMe's second option where you create a new table; populate it with the serial numbers; then join to production is the way to go.
Good luck.

Oops: I see Steve has responded too.

[ItsMe]

Hello Steve, hello Orange.

[CJ_London]

another way to quickly get to a query basis direct from the table is to open the table then under the home tab, click on the advanced icon on the ribbon then the advanced filter/sort option which will open a query window. drag down your serial number field to the query grid then enter the serial numbers, one per row (up to 10) or use IN (1,3,4,6). Then click the Toggle Filter option to see the filter list. Toggle again or use the filter option at the bottom on the navigation bar to unfilter. Nice thing about this is if you save the table when prompted it will save the filter so when your engineer comes back and says - 'I've lost it, can you do it again', it's still there - open the table and click filter toggle. Not so good is leaving filters (and sorts) on tables - I believe it can have an impact on performance although I've not noticed it

[NightWalker]

Thank you all for the possible directions. I have made a temp table with only the SerialNumber field. I made a form to add serial numbers from a txtbox to that temp table. The form also has a listbox that displays all the serial numbers entered (gives engineer warm and fuzzy to see that). I am working on a query to select the data from the main table where the serial numbers match. I am stuck there right now, but thank you all for where to start with this one.