query criteria needs to be a column in another query. (1 Viewer)

[Dranoweb]

Hi,

Trying to apply a column in a query as the criteria in another query.

is there an expression i can use such as:

=[column]![query]

 

[Galaxiom]

Normally you would join the queries on the field you want to use as the criteria.

 

[Dranoweb]

Worked it out in the end:

"ID1" is the key field in the query
"mastertable fortnight" is the query based on the table "Mastertable"

In (SELECT [ID1] FROM [mastertable fortnight])

This filters all records in the query that match the numeric value of ID1.

 

[pbaldy]

Glad you found a solution. For the record, while your way works, it will prove slower as the amount of data increases. Galaxiom's suggestion would be more efficient:

SELECT ...
FROM TableName INNER JOIN [mastertable fortnight] ON TableName.ID1 = [mastertable fortnight].ID1

 

[Dranoweb]

Glad you found a solution. For the record, while your way works, it will prove slower as the amount of data increases. Galaxiom's suggestion would be more efficient:

SELECT ...
FROM TableName INNER JOIN [mastertable fortnight] ON TableName.ID1 = [mastertable fortnight].ID1

I need to clarify, due to so unavoidable circumstances, I'm writing the database in 2007, for a 2003 system.

While this is quicker, for some reason it does not appear to work when database is loaded on the 2003 system.

as this is for a government institution, there may be some specific quirks about their installation that are non-standard too.

for now I'll be forced to stick with what works, and streamline it later; as time is short.

 

[pbaldy]

Really? There's nothing version specific there; that's basic SQL, and should work in any version. I use that type of join in 2000. Anyway, you have it working, which is the main thing.

 

[Dranoweb]

I'll have to double check, is most likely what has happened.

At present I don't have access to the database to check. Last day of work and it's a cleanup day.

I'll follow up on this and reply back.
It's always good to get coding clean and streamlined.