SQL Subquery Problem

I am trying to extract data from two tables in the same database and simply display a COUNT total. The code is as follows:

BARRACK ELIGIBLE TROOPERS COMPLETED SURVEYS Barrack A Waterloo 29 26 Barrack D Bel Air 50 26 Barrack F North East 47 26 Barrack L Forestville 33 26 Barrack M JFK Memorial Highway 46 26 Barrack Q College Park 29 26 Barrack R Golden Ring 40 26 STATEWIDE TOTALS 274 26

1) What fields from what tables are you wanting to count?
2) What are the relationships between the two tables?
3) What database are you using?

give pseudocode for what you want the query to do and what you expect the return results to be.

I am trying to count:

1 - the number of personnel assigned to each area who meet the inclusionary criteria from one table and
2 - the number of people who entered data into a second table

The two tables both contain the person's ID Number and assignment. So I need the query to pull all the people out of the first table who match the rank and assignment criteria (count #1) and then pull the number of people who have entered data into the second table (count #2 - they can only enter data once). This will allow me to determine how many people still need to complete the assignment. The results I am hoping to retrieve would look something like this (dots added since spaces are removed when I post):

Assignment..........Eligible..............Complete d
#1........................25...................... 12
#2........................48...................... 22
#3........................18...................... 18
etc.
The database is Access 2002.

SELECT T.Assignment, COUNT(T.User_Name) AS Troopers_Number, (SELECT COUNT(*) FROM tbl_twelve_hours Where tbl_personnel.assignment = T.Assignment) AS Completed_Surveys FROM tbl_personnel AS T WHERE T.Region='II' AND (T.Rank='Trooper' OR T.Rank='TFC' OR T.Rank='Corporal' OR T.Rank='Sergeant') GROUP BY T.Assignment

Queries DON'T Loop
S-

It worked with one minor adjustment. I had to change
"WHERE tbl_personnel.Assignment=T.Assignment...."
to
"WHERE Assignment=T.Assignment...." as shown below.

strSQL2 = "SELECT T.Assignment, COUNT(T.User_Name) AS Troopers_Number, (SELECT
COUNT(*) FROM tbl_twelve_hours WHERE assignment = T.Assignment) AS

Completed_Surveys FROM tbl_personnel AS T WHERE T.Region='II' AND

(T.Rank='Trooper' OR T.Rank='TFC' OR T.Rank='Corporal' OR T.Rank='Sergeant') GROUP

BY T.Assignment"



I greatly appreciate your time and assistance.....Doug