Query to return multiple records

[Blake]

Hello,

I have a database at work that keeps an inventory of cameras by
location. Every week I get a repair estimate sheet that lists cameras
by serial number that need to be repaired. This list is sent to me in
excel. I am trying to make it where I can just copy the column of
serial numbers from the excel list sent to me and paste it directly
into the database to return my information for those cameras. I would
like to do this in a datasheet view where I paste the column, it looks
them up and returns the information I need(warranty dates) by serial
number. Any help would be greatly appreciated. Thanks.

Blake

 

[Joan Wild]

You could import the Excel worksheet, and then create a query based on this
table and your camera table. Join the two on the serial number column. I'd
double click the join line and choose to show all records in the Excel table
and only those from you camera table that match. This will ensure you also
see serial numbers that aren't in your database.

Add whatever fields you'd like to see to the grid.

 

[Blake]

Thanks for the help... Is there no way to do this simply by making it
so I can just paste multiple values into a query? I just want to be
able to say... copy from the serial number column like 5 serial
numbers... then paste them directly into the query.... Or maybe this
should be done with a filter... I am just trying to find a way to
filter more than one record at a time. Thanks.

 

[Joan Wild]

I think what I suggested is easier, but you can copy the contents of each
cell into the criteria row of your query. You can't copy the Excel column
though, you'd have to copy each value, one at a time. Another possibility
is to convert the column to text in Excel - put the values in a single cell,
separated by commas. Then you can copy this cell into the criteria cell in
the query grid under serial number. Then edit what you pasted to have In(
at the start and ) at the end.

In(......)
I think doing the join would be easier, as you'd have the query setup and
could just delete the imported table, import the next file, and run the
query.

 

[Blake]

Thank you so much... I am still learning how to use everything. So I
can link the spreadsheet and then just change the data in that one
spreadsheet each week? I get these updates from our repair people once
a week, so we are talking about 20 cameras a week... Thanks.

 

[Joan Wild]

If you just link to the spreadsheet, you don't have to change any data in
the spreadsheet.

 

[Blake]

Joan,

Thanks for the help. I made a table for lookup values and linked it to
my main table. I then made a query to search out that data. It works
like a charm... each week I just paste the column from the email into
that lookup table and then run the query and it pulls up the info I
need. Thanks again for your help.

Blake