Try to mitigate need of a "helper" query

[skydivetom]

Experts:

I need some assistance with "streamlining" a query process. Attached is a sample database which contains one table and two queries (i.e, 1 SELECT query and 1 CROSSTAB query).

Background:
- In my actual database, I have 100+ queries. These were developed over the course of the last few months.
- At this time, I'm performing some cleanup of my database and I'd like to reduce (where possible) the amount of queries (i.e., remove any "helper/input queries" as much as possible).

Background on table T01_Staffmembers:
- Field [All_Onboard] is Yes/No field. If checked, the staff member is onboard (i.e., "active"). If not checked, he/she is "inactive".
- Field [All_Type] has values = "Officer", "Enlisted", "Civilian", and "Contractor"

Process on my queries:
- I use "Query1_Input" to get a simple count on the four "Types" (Officer, Enlisted, etc.) further broken down by onboard (either "active" or "inactive").
- Next, I use "Query1_Input" as my source for the crosstab query ("Query2_Output").

... overall, this accounting works great and I get the correct results in my crosstab query.



**However**, as mentioned in my opening sentence, I'm interested in "streamlining" my database. That is, quite often, I have to use a "helper/input" query to get to my final results. So, in this case, I end up w/ two (2) queries to get my final result (illustrated in the crosstab qry).

My question: Is there any way I get mitigate the use of the input/helper query and get the same results of the cross-tab query via a single query? If so, what would that query look like?

EEH

[Gicu]

You don't need a helper query in this case, have a look at the updated file.

Cheers,

[skydivetom]

Gicu -- absolutely brilliant! Thank you so much for your help... very much appreciated.

[Gicu]

You're very welcome, good luck with your streamlining!
Cheers,