can i use a column as query?

[Erik Murk]

Hi,
is it possible to use a column as query?
Normally, with a multiselectquery you type in several criteria(postal code) like;
3311 aa
1245 bb
1234 bc

which is all very well, but what if you have 100 postal codes (dutch postal codes and without a logical order).
In this case it would be nice if you could use a table with one column (the postal codes) to use as criteria.

Problem is that i dont know how ((

anyone?

 

[Michael J Ross]

If I understand you right, create a one column table with the post codes you want stored in it, then create a query joining the table you want to query on with the table you have you created using an inner join.

This will select only those records from the table you are querying where an entry exists in your list of post codes.

 

[Erik Murk]

If I understand you right, create a one column table with the post codes you want stored in it, then create a query joining the table you want to query on with the table you have you created using an inner join.

This will select only those records from the table you are querying where an entry exists in your list of post codes.

thank you, but how exactly do i do this?

 

[Ron_dK]

Why don't you simply make a query based on the table that has the postal code field.
In doing so, you can sort the data ascending , descending or add a criteria.

Hth

 

[Erik Murk]

Why don't you simply make a query based on the table that has the postal code field.
In doing so, you can sort the data ascending , descending or add a criteria.

Hth

That is exactly what id like to do, but the problem (for me) is how...

 

[neileg]

That is exactly what id like to do, but the problem (for me) is how...

Michael told you. Create a table with the codes you want to query. Join this table with your original data in a query using an inner join. The query will return those records that match the codes in your new table.

 

[Erik Murk]

Michael told you. Create a table with the codes you want to query. Join this table with your original data in a query using an inner join. The query will return those records that match the codes in your new table.

Not sure i fully understand, but im going to try; thanks!

 

[Michael J Ross]

Take a look at the attached screenshot it may help.

In my example i have my postcodes in the table postcodes and am only wanting to find records in my addresses table where the postcodes match, so I have joined the tables using an inner join on the post code field. (To join drag from the post code field in the left table to the post code field in the right).

 

[Erik Murk]

Take a look at the attached screenshot it may help.

In my example i have my postcodes in the table postcodes and am only wanting to find records in my addresses table where the postcodes match, so I have joined the tables using an inner join on the post code field. (To join drag from the post code field in the left table to the post code field in the right).

i see! this looks promising

ill let you know if it works wednesday, but thanks already!

 

[The_Doc_Man]

There is also the syntax using the IN keyword, which you can look up under the SQL portion of Access Help. It is an operator of sorts, similar syntactically (but not semantically) to [AB] > [CD] - i.e. it is a relative operator, just a different type of relation.

In general, you say something like SELECT [X],... FROM [Z] ... WHERE .... AND [AB] NOT IN [CD]

where [AB] contains the field you wanted to test and [CD] is the table of codes you wanted to check against. But go to the Access Help first - it has been AGES since I used something like this. I recall there are also pitfalls. Also, you ABSOLUTELY cannot update table [CD] dynamically while you are also using it for lookups. (A little matter of table locking.)

 

[Erik Murk]

i see! this looks promising

ill let you know if it works wednesday, but thanks already!

Well, i tried..
Seem to need a step by step;
do i first make a relation between tables or is there a way to make this join in the query itself and if yes, how?

 

[Erik Murk]

Well, i tried..
Seem to need a step by step;
do i first make a relation between tables or is there a way to make this join in the query itself and if yes, how?

Never mind! I finally found out...
Thanks, works like a charm))

 

[Erik Murk]

There is also the syntax using the IN keyword, which you can look up under the SQL portion of Access Help. It is an operator of sorts, similar syntactically (but not semantically) to [AB] > [CD] - i.e. it is a relative operator, just a different type of relation.

In general, you say something like SELECT [X],... FROM [Z] ... WHERE .... AND [AB] NOT IN [CD]

where [AB] contains the field you wanted to test and [CD] is the table of codes you wanted to check against. But go to the Access Help first - it has been AGES since I used something like this. I recall there are also pitfalls. Also, you ABSOLUTELY cannot update table [CD] dynamically while you are also using it for lookups. (A little matter of table locking.)

thanks, but a bit out of my league...