Query with Complicated Requirements

[Briana]

Hi


The end goal is to create a report that you type in the Household Name and all the past transactions of the stocks traded in that year are listed by account. Right now it is set up so that I have a query that searches for all the stock sales for the year if I type in a Household Name. When the report pops up I see that the customer sold Company A in one account and Company B in another account. Then I take that information and use the company names of the sold stock as the criteria to find all the transactions relating to that stock from past years--- both bought and sold. Is there a way to tell access to first find the companies sold for each account for a household and then find all the transactions having to do with that company and account in the past.

Example:
SOlD IN 2012
AT&T SOLD 300 Shares
DUK SOLD 200 Shares

AT&T PAST Transactions
Bought 200 Shares of AT&T
Sold 50 Shares of AT&T
Bought 400 Shares of AT&T

DUK PAST TRANSACTIONS
Bought 200 Shares of DUK
Sold 50 Shares of DUK
Bought 400 Shares of DUK

Anything that combines them would be great.

Thanks in advance!!!!

[June7]

Maybe. Something like:

SELECT * FROM transactions WHERE companyID IN (SELECT companyID FROM table WHERE household=[input HH ID]);

http://allenbrowne.com/subquery-01.html