Sub Query

[sconard]

I have done this type of thing programmatically but never via a query.

I need to pull records from Table 1.
Table 1 has columns A,B,C,D

I need all the records that match A and then all the records that match the values of B, C, and D that were returned from the records that matched A.

Does this require a temp table and does Access allow? What is the syntax?

This seems like a simple "advanced" query but I cannot seem to find the combination.

[Minion]

I have done this type of thing programmatically but never via a query.

I need to pull records from Table 1.
Table 1 has columns A,B,C,D

I need all the records that match A and then all the records that match the values of B, C, and D that were returned from the records that matched A.

Does this require a temp table and does Access allow? What is the syntax?

This seems like a simple "advanced" query but I cannot seem to find the combination.

If you're trying to write a sql query for this I believe you're looking for a basic select query with a where clause. It would look something like:

SELECT * 
FROM Table1
WHERE A = <value>;
 

This will select all records and fields from Table1 where the value of column A is equal to a stated Value.

I'm not entirely sure how you wish to utilize this, but this should be a start in the right directions.

Hope this helps.

- Minion -

[sconard]

Thank you for the reply but the latter portion of the question is what is perplexing. I want to take the results of the query you wrote and match the b,c and d of those results to create the final set.

Lets call this query1
select a,b,c,d
from table1
where a = var

query1 will produce a number of rows. I want all those records from table1 that match the b,c,d values in the records from query1

[GabyFont]

I have to do something like that, and this works:
in the criteria of each field A, B, C, D I something like this, assuming that you have list boxes in a form so the user select it rather than entering the values:

like [forms]![yourFormName]![field A] & "%"

NOTE: instead of "%"you can try "*", in Access 2007 the asterisk was get me some trouble

this allow the user to fill all fields or fill only the one they needs, so if you only you fill A and B, you will get all the records of C and D that matches A and B, but also if you fill all fields (ABCD) it will only returns records that match that critera.
Hope it helps


GabyFont

[sconard]

The resolution was a subquery:

SELECT p.id_projects
FROM t_Projects AS p INNER JOIN t_projects AS p2 ON p.pin > p2.pin
WHERE p2.id_projects = 2;

Of course this is a simple example but this is what provided a "sub-query" of the same table.