I have a single table called "Combined" it lists individual members, unique member number, the office name, office number they're associated with, a billing code and status, among many other informational fields. My goal is to create a Query and Report showing all active Offices and then all members regardless of status associated with the said active office.

I've been successful creating and running a Query called "Active Offices". The Member who is the head of an office has a bill code of "R00". Active offices have a status code of "A" for Active (any other status code equals "inactive"). I'm unsure of which fields to "join" and which relationship to define between the Combined Table and The Active Office Query. All members (outside of Office Head) have bill codes of "M00". What expression would I use in a new query that listed all members in each "Active Office"? The next question I have is will the Query I'm trying to now create, allow me to use it to generate a report (the data from the "Combined Table" is the data that supplies the "Active Offices Query". If not is there a better way to accomplish the goal. Thanks... in advance!