By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 673 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

Sub Query

P: 21
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.
Jan 11 '08 #1
Share this Question
Share on Google+
4 Replies


Minion
Expert 100+
P: 108
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT * 
  2. FROM Table1
  3. WHERE A = <value>;
  4.  
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 -
Jan 11 '08 #2

P: 21
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
Jan 11 '08 #3

P: 8
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
Jan 14 '08 #4

P: 21
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.
Jan 21 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.