0

I am trying to use the results of another query to use as a criteria for another. In my specific example, I might have four houses that are 'A', 'B', 'C', 'D' (the unique values of a field in a table called Homes).

I want to go through another query and say for each house type, what percent of residents (in Residents table) are married, which I want to do by using Count() to count the number for each Home type.

Do I need to loop through the results using VBA? Asking on a higher level, is there a way to use the results from a query as inputs into another - more than just limit the results of the new query to the results of the prior query?

Edit:

In semi-pseudo code:

For each (result of previous query) Do
New query WHERE field1 = (row of previous query)
End Do

What I am trying to ask, is there a way to accomplish this in Access using SQL? Or is this something that has to be done in VBA?

I know that if it can be done in SQL that would be the best performing and best practice, but I'm relatively inexperienced in SQL and online resources aren't always helpful because Access has it's own particular flavor of SQL.

HansUp
  • 95,961
  • 11
  • 77
  • 135
Alec
  • 4,235
  • 1
  • 34
  • 46
  • Are you able to write your own SQL or are you using Query Builder or something? – AJC Aug 05 '11 at 18:50
  • I am able to write my own SQL and VBA, though I am new to Access's flavor of VBA. I think I have not been clear - essentially I was to run a new query based on the results of a prior. I've edited the original to include a semi-psuedo code of what I intend. – Alec Aug 08 '11 at 13:06

3 Answers3

2

Since you are using VBA to run this, you can loop through your recordsets and yes you can use a value from one query in the next query. There are alot of resources out there to help.

VBA: Working with RecordSets

Looping through Record Sets

Code through all records

Community
  • 1
  • 1
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • I ended up just running a query using VBA, looping through the fields that I was interested in and inserting the results into a summary table. – Alec Aug 12 '11 at 14:20
0

To answer your general question, yes there is. You can do a nested query i.e. select column a from table a where column a = (select column b from table b where column b=x)

You can go as many levels deep as you want, but the caveat is the nested query can only return one column and with a specific answer set. You can also use select statements as your columns i.e

select (select column b from table b) col b from table a ..... Not the exact syntax but I would have to dig out some examples from an old project to find that.

Nested queries are useful, but for the level of precision you are looking for, a stored procedure or a view is probably a better option. Just for ease of use, I would look at creating a view of the data that you want and then querying from that to start with. More flexible than a nested query.

Brian - TechToolbox
  • 29
  • 1
  • subquery probably not needed: group by and join are enough – iDevlop Aug 05 '11 at 19:16
  • I understand that queries can be nested, but I don't think that achieves what I am looking for. If I understand what you are saying, I can build a query to select only 'A' type houses, and then run another query to get the statistics I want off of that. I would then repeat that process for each of the other house types. What I am actually looking for is - is there a way to systematically run a query for each of the undetermined number of unique results from a query designed to find such unique results? – Alec Aug 08 '11 at 13:02
0

You need to join two tables using a common column and then get your specific column from any of the table

SELECT A.REQUIRED_FIELD from TABLEA AS A
INNER JOIN TABLEB AS B ON A.FOREIGN_KEY=B.FOREIGN_KEY
WHERE CONDITION
Jayasurya
  • 79
  • 1
  • 3