Filtering "this" plus "others"

[SueBK]

If I have a table with three fields - Submission; Area; Contract #.

Each [Submission] can have multiple [Areas]. Some [Areas] share a [Contract] across submissions. eg:

Submission 1 - Area 1 - Contract A
Submission 1 - Area 2 - Contract B

Submission 2 - Area 3 - Contract A

Submission 3 - Area 4 - Contract A
Submission 3 - Area 5 - Contract B

--> Contract A is comprised of Sub 1, Area 1; Sub 2, Area 3; and Sub 3 Area 4.

My real live data comprises about 139 submissions, with about 233 Areas, and (at this stage) only 2 Contracts (covering 12 records). All clear as mud?

What I'd like to be able to query is:

Show me all [submission] where [Contract] is “X” PLUS all the other records for that subset of [Submission]. That is - if a submission has a particular Contract, I want to see ALL the records for that submission, not just the one (or two) with the particular Contract number.

On the dummy data above (for example) – the result of my query if based on “Contract A” would be all the records. If I searched “Contract B” I would get both records from Sub 1 and both from Sub 3.

Not sure my explanation is very clear.

 

[Brianwarnock]

I think that you can do this with two queries . The first selects all records for the contract, the output from this is then joined to the table on submission to pull all of the required records.

Brian

 

[SueBK]

Thanks Brian. I had a vague notion that two queries could do it.