how to make a query out of a list of values?

[illustrator1]

How can i make query from a list of field values?
I have a database with a few 1000 records.
I have also an excel file with values of fields.
How can i make a query that shows just the records that are in my excel
file?

I made something like this:

SELECT *
FROM table1
WHERE ((
(table1.field1)="example1" Or
(table1.field1)="example2"
))
ORDER BY table1.field1;

The excel document has in this example 2 cells like this:
"example1"
"example2"

The problem with this is, there are 100's or 1000's of those fields in
the excel file, so access says the query is too big.
Also i have to place everything in the file, this isn't such a big
problem but maybe there is a way to just let i search in an external
file like my excel file.
My real problem is that i have to split everything up in a lot of small
queries and that takes too much time.

 

[Douglas J Steele]

Well, it's slightly shorter to use the IN operator:

SELECT *
FROM table1
WHERE field1 IN ("example1", "example2")
ORDER BY field1

Note, too, that since you've only got the one table, you don't need to
qualify it as table1.field1, and you can lose some of the parentheses.

However, that'll only gain you a little relief: if you've got a lot of ids
to check, you'd be better off having the list of desired ids in a separate
table, and join the two tables together. If your Excel spreadsheet is set up
appropriately, you should be able to link to it, and then join the two.
Otherwise, you'll have to create a new table in Access to store the values.

 

[John Vinson]

How can i make query from a list of field values?
I have a database with a few 1000 records.
I have also an excel file with values of fields.
How can i make a query that shows just the records that are in my excel
file?

Just to expand on Doug's answer, you can use File... Get External
Data... Link to link to your spreadsheet (you can import it as well,
if you wish, but for this only a link should be necessary).

You can then create a query joining your table to the linked Excel
table by the ID field; this will show you only those records which
match.

John W. Vinson[MVP]

 

[illustrator1]

John Vinson schreef:

Just to expand on Doug's answer, you can use File... Get External
Data... Link to link to your spreadsheet (you can import it as well,
if you wish, but for this only a link should be necessary).

You can then create a query joining your table to the linked Excel
table by the ID field; this will show you only those records which
match.

John W. Vinson[MVP]

Ok, this seems to work but now i'm trying this with different excel
files.
How can i show all the records which are listed in any of the excel
files?
Is there way to do this fast? Because i get this quite often, i don't
want to spend a lot of time every time programming some sql code.
Can you give enough details also? I'm new to ms access.
Thanks!

 

[John Vinson]

John Vinson schreef:
Ok, this seems to work but now i'm trying this with different excel
files.
How can i show all the records which are listed in any of the excel
files?
Is there way to do this fast? Because i get this quite often, i don't
want to spend a lot of time every time programming some sql code.
Can you give enough details also? I'm new to ms access.
Thanks!

How do you decide WHICH Excel file (or files) to check?

What you'll probably need to do is write VBA code using the
TransferSpreadsheet method (see the VBA help) to dynamically link to
whichever spreadsheet you want, using the same table named
(LinkedSheet maybe) each time. Then you could have one stored query
which would work with whichever spreadsheet you choose.

John W. Vinson[MVP]

 

[illustrator1]

How do you decide WHICH Excel file (or files) to check?

What you'll probably need to do is write VBA code using the
TransferSpreadsheet method (see the VBA help) to dynamically link to
whichever spreadsheet you want, using the same table named
(LinkedSheet maybe) each time. Then you could have one stored query
which would work with whichever spreadsheet you choose.

John W. Vinson[MVP]

I want to do something like this:

For example take names:

Excelfile1:
Will
Mike
Michael

Excelfile2:
Anna
Marc

Excelfile3:
Mike
Marc

There can be more than 3 excelfiles and they are a lot longer.
Notice some names can appear in different excelfiles.

So now for example i have a database with all the info about all the
persons.

I now use the FILE/GET EXTERNAL DATA/LINK TABLES
menuoption to import all 3 excelfiles, the excelfiles are in a good
format so i only have to press next and it imports fine.

Now i want to make a query that shows the records of all the persons
that appear in those 3 linked excelfiles so in this case for the
persons:
Will
Michael
Anna
Marc
Mike

How can i make this query?

 

[Douglas J. Steele]

I want to do something like this:

For example take names:

Excelfile1:
Will
Mike
Michael

Excelfile2:
Anna
Marc

Excelfile3:
Mike
Marc

There can be more than 3 excelfiles and they are a lot longer.
Notice some names can appear in different excelfiles.

So now for example i have a database with all the info about all the
persons.

I now use the FILE/GET EXTERNAL DATA/LINK TABLES
menuoption to import all 3 excelfiles, the excelfiles are in a good
format so i only have to press next and it imports fine.

Now i want to make a query that shows the records of all the persons
that appear in those 3 linked excelfiles so in this case for the
persons:
Will
Michael
Anna
Marc
Mike

Create a Union query:

SELECT Name
FROM ExcelFile1
UNION
SELECT Name
FROM ExcelFile2
UNION
SELECT Name
FROM ExcelFile3

etc.

 

[Larry Daugherty]

Use Append queries to append tables 2 and 3 to table1. You'll find
Append queries in Access Help.

HTH
--
-Larry-
--

John Vinson schreef: