Microsoft Access - Query to filter and count records

Asked By Aaron Stockton on 03-Dec-14 08:04 AM
I have a query that i'm putting together and I can get it to work well without filtering... but I want it to filter.

The query (for a report) currently have had 'rank' group by and then 'rank' count, this part counts the number of members in the organisation at each rank which is what I want it to do. The issue is, rather than pulling  the whole organisation, I also want to be able to filter it at a 'brigade' level. 

The brigade filter is linked to a combo box on the form, so when a brigade is chosen and the user clicks the button, a report is shown up with all ranks in the brigade with the number of members at each rank.
Robbe Morris replied to Aaron Stockton on 03-Dec-14 08:07 AM
What you describe is a hierarchical query to get all of the personnel under a given division:

Squad -> Platoon -> Brigade -> Division

Microsoft Access doesn't have this functionality built in like SQL Server or Oracle.  This was the closest I could find to a solution:

http://stackoverflow.com/questions/12096227/how-to-make-an-sql-query-to-build-a-tree
Aaron Stockton replied to Robbe Morris on 03-Dec-14 10:00 PM
Thanks Mate

At this stage we're going to leave out that component.

Thanks for your assistance.