Table data as criteria in Query?

[hatstand]

Is it possible to use data in a table as the criteria in a query.

 

[plog]

Yes.

The more specific your question, the more specific my answer.

 

[hatstand]

I take you point.

In my query I have selected my table and have added: Dlookup(“MyFieldName”,”MyTableName”) as my criteria.

This allows me to alter what my query uses as criteria. It works perfectly, but will only use the first record in the table as criteria.

Is there away to allow more than one record to be used?

 

[RainLover]

Instead of the approach you are trying to use you could do this much easier by including both Tables in the Query and setting up the Relationship between them to show all records in one table that equal the records in the other.

Hope I said that right.

 

[hatstand]

I see. I didn't realise I could do that. I would have to add a third table as the main table is used by a lot of people and the second table. The one with the criteria, is a linked table and Access won't allow me to modify it.

So if I add a third how do I create it so it's limited to my criteira?

 

[RainLover]

In the Query Grid add the tables that you need then right click on the join line and make your settings there.

 

[hatstand]

Sorry for the late reply.

Thanks very much RainLover. That worked perfectly and is a much better way of using Excel to set criteira. It's saved me so much hassle.

Thanks once again, it's much appreciated.

 

[RainLover]

Glad to see you got it.

Get used to using the Query Design Mode. It is a lot easeir than most other methods.

 

[hatstand]

Hi All,

As per the guidance above, I created a linked table, used it in a query to allow me to use Excel to set the criteira in my query. It works perfectly. But, I would now like to expand the criteira.

So if I add a new column to Excel it appears in the linked table. Fine so far.

My database has two tables:
TABLE 1: Dates / Part Numbers
TABLE 2: Months / Dates
LINKED TABLE: Months / Dates / Part Numbers

It works fine if I add a date or a part number or both and run the Query. But not if I add just a month teh query returns nothing. I think it's because it will only accept Dates and Part Numbers as they are directly linked in relationships. Is there a way of overcomming this?

Hope I've explained clearly enough.

 

[RainLover]

Does not post #6 answer this question.

You have given very little information to go on.

 

[hatstand]

Hi again,

It does I think. But I must be doing something wrong as the only way I can get it to work is to make a query using:

TABLE1: Month
TABLE2: Date /Part Number

Then create another query using the Linked table and the Query above. Am I right or over doing it?

 

[RainLover]

Can't really help without much more information.

Like the Table Structure for both tables and the SQL of the query.

 

[hatstand]

No problem. You have been more than enough help. I think I've cracked it by creating a query then using that query in another one. There maybe a better way, but it works. Thanks.

 

[RainLover]

It may be the correct solution.

In any case it does no harm.