SQL Query in Access 2003

  • Thread starter Thread starter Marine 1stSgt
  • Start date Start date
M

Marine 1stSgt

I am runing into an issue though that is really going to aid me in making my
counts for my Marines and Sailors. I have a database at work with personell
information to include rank, SSN, and such. What I am trying to do is write
a query in Access 2003 count my field of Rank in Groups. What I mean by this
is that I call "SNCO's" for those Marines who have the rank of SSgt, GySgt,
MSgt, 1stSgt, and MGySgt. I used the SQL below which counts fine (a numeric
value). The issue I am having is to add anadditional column that will count
Officers, such as Capt, Maj, LtCol. I have tried a couple of things and it
is not working. I do want to do this in SQL, for I am studying it in college
and I have tried adding additional where statement, but does not work. I
basically want to know how to add addition columns with the same information
I did below, which works for that one columnI want to have this all in one
query. Can anyone help this Marine 1stSgt out?

SELECT COUNT(Rank) AS SNCOs
FROM [Bravo DB]
WHERE rank='ssgt' Or rank='gysgt' Or rank='msgt' Or rank='1stsgt' Or
rank='mgysgt' Or rank='PO1' Or
rank='bmc';
 
Create a TableOfRanks like this (Not sure of Marine ranks - retired Army MSG
E-8)--
Grade Rank
E-5 ssgt
E-6 gysgt
E-7 msgt
E-8 1stsgt
E-9 mgysgt
E-4 PO1
E-3 bmc
O-1 2Lt
O-2 1Lt
O-3 Capt
O-4 Maj
O-5 LtCol

SELECT Sum(IIF(Left([Grade], 1) = "E", 1, 0)) AS SNCOs,
Sum(IIF(Left([Grade], 1) = "O", 1, 0)) AS Officers
FROM [Bravo DB] LEFT JOIN TableOfRanks ON [Bravo DB].[Rank] =
TableOfRanks.[Rank];

The TableOfRanks is used to translate the Rank to Grades.
 
TRANSFORM COUNT(*)
SELECT 'Total'
FROM [Bravo DB]
GROUP BY 'Total'
PIVOT rank



should do (in SQL view), but it will count each individual rank. You can
'regroup' those rank, with the help of another table:


GroupsOfRanks ' table Name
rank GroupOf ' fields
ssgt Sgt
gysgt Sgt
1stsge Sgt
....
Capt Capt
....
data sample



And then, rather use:

TRANSFORM COUNT(*)
SELECT 'Total'
FROM [Bravo DB] AS a
INNER JOIN GroupsOfRanks As b
ON a.rank=b.rank
GROUP BY 'Total'
PIVOT groupOf


and then, your counts will be for each different values in 'GroupOf'


Vanderghast, Access MVP
 
Do you think I would need to make another table just for this. I would think
there would be a quicker way. I appreciate the quick response.

KARL DEWEY said:
Create a TableOfRanks like this (Not sure of Marine ranks - retired Army MSG
E-8)--
Grade Rank
E-5 ssgt
E-6 gysgt
E-7 msgt
E-8 1stsgt
E-9 mgysgt
E-4 PO1
E-3 bmc
O-1 2Lt
O-2 1Lt
O-3 Capt
O-4 Maj
O-5 LtCol

SELECT Sum(IIF(Left([Grade], 1) = "E", 1, 0)) AS SNCOs,
Sum(IIF(Left([Grade], 1) = "O", 1, 0)) AS Officers
FROM [Bravo DB] LEFT JOIN TableOfRanks ON [Bravo DB].[Rank] =
TableOfRanks.[Rank];

The TableOfRanks is used to translate the Rank to Grades.

Marine 1stSgt said:
I am runing into an issue though that is really going to aid me in making my
counts for my Marines and Sailors. I have a database at work with personell
information to include rank, SSN, and such. What I am trying to do is write
a query in Access 2003 count my field of Rank in Groups. What I mean by this
is that I call "SNCO's" for those Marines who have the rank of SSgt, GySgt,
MSgt, 1stSgt, and MGySgt. I used the SQL below which counts fine (a numeric
value). The issue I am having is to add anadditional column that will count
Officers, such as Capt, Maj, LtCol. I have tried a couple of things and it
is not working. I do want to do this in SQL, for I am studying it in college
and I have tried adding additional where statement, but does not work. I
basically want to know how to add addition columns with the same information
I did below, which works for that one columnI want to have this all in one
query. Can anyone help this Marine 1stSgt out?

SELECT COUNT(Rank) AS SNCOs
FROM [Bravo DB]
WHERE rank='ssgt' Or rank='gysgt' Or rank='msgt' Or rank='1stsgt' Or
rank='mgysgt' Or rank='PO1' Or
rank='bmc';
Click to expand...
Click to expand...
 
Thanks for the help, I will give it a try.

vanderghast said:
TRANSFORM COUNT(*)
SELECT 'Total'
FROM [Bravo DB]
GROUP BY 'Total'
PIVOT rank



should do (in SQL view), but it will count each individual rank. You can
'regroup' those rank, with the help of another table:


GroupsOfRanks ' table Name
rank GroupOf ' fields
ssgt Sgt
gysgt Sgt
1stsge Sgt
...
Capt Capt
...
data sample



And then, rather use:

TRANSFORM COUNT(*)
SELECT 'Total'
FROM [Bravo DB] AS a
INNER JOIN GroupsOfRanks As b
ON a.rank=b.rank
GROUP BY 'Total'
PIVOT groupOf


and then, your counts will be for each different values in 'GroupOf'


Vanderghast, Access MVP


Marine 1stSgt said:
I am runing into an issue though that is really going to aid me in making
my
counts for my Marines and Sailors. I have a database at work with
personell
information to include rank, SSN, and such. What I am trying to do is
write
a query in Access 2003 count my field of Rank in Groups. What I mean by
this
is that I call "SNCO's" for those Marines who have the rank of SSgt,
GySgt,
MSgt, 1stSgt, and MGySgt. I used the SQL below which counts fine (a
numeric
value). The issue I am having is to add anadditional column that will
count
Officers, such as Capt, Maj, LtCol. I have tried a couple of things and
it
is not working. I do want to do this in SQL, for I am studying it in
college
and I have tried adding additional where statement, but does not work. I
basically want to know how to add addition columns with the same
information
I did below, which works for that one columnI want to have this all in one
query. Can anyone help this Marine 1stSgt out?

SELECT COUNT(Rank) AS SNCOs
FROM [Bravo DB]
WHERE rank='ssgt' Or rank='gysgt' Or rank='msgt' Or rank='1stsgt' Or
rank='mgysgt' Or rank='PO1' Or
rank='bmc';
Click to expand...
Click to expand...
 
The table would be easy to make.
You might also find it useful for other queries such as pulling data for all
Senior NCOs - Left([Grade],1)="E" and Right([Grade],1)>=5 for all E-5s and
above.

Marine 1stSgt said:
Do you think I would need to make another table just for this. I would think
there would be a quicker way. I appreciate the quick response.

KARL DEWEY said:
Create a TableOfRanks like this (Not sure of Marine ranks - retired Army MSG
E-8)--
Grade Rank
E-5 ssgt
E-6 gysgt
E-7 msgt
E-8 1stsgt
E-9 mgysgt
E-4 PO1
E-3 bmc
O-1 2Lt
O-2 1Lt
O-3 Capt
O-4 Maj
O-5 LtCol

SELECT Sum(IIF(Left([Grade], 1) = "E", 1, 0)) AS SNCOs,
Sum(IIF(Left([Grade], 1) = "O", 1, 0)) AS Officers
FROM [Bravo DB] LEFT JOIN TableOfRanks ON [Bravo DB].[Rank] =
TableOfRanks.[Rank];

The TableOfRanks is used to translate the Rank to Grades.

Marine 1stSgt said:
I am runing into an issue though that is really going to aid me in making my
counts for my Marines and Sailors. I have a database at work with personell
information to include rank, SSN, and such. What I am trying to do is write
a query in Access 2003 count my field of Rank in Groups. What I mean by this
is that I call "SNCO's" for those Marines who have the rank of SSgt, GySgt,
MSgt, 1stSgt, and MGySgt. I used the SQL below which counts fine (a numeric
value). The issue I am having is to add anadditional column that will count
Officers, such as Capt, Maj, LtCol. I have tried a couple of things and it
is not working. I do want to do this in SQL, for I am studying it in college
and I have tried adding additional where statement, but does not work. I
basically want to know how to add addition columns with the same information
I did below, which works for that one columnI want to have this all in one
query. Can anyone help this Marine 1stSgt out?

SELECT COUNT(Rank) AS SNCOs
FROM [Bravo DB]
WHERE rank='ssgt' Or rank='gysgt' Or rank='msgt' Or rank='1stsgt' Or
rank='mgysgt' Or rank='PO1' Or
rank='bmc';
Click to expand...
Click to expand...
Click to expand...
 
I will give it a try in the morning. You are right, it will not be that hard
to make it up. This should update when I link it to the other table when I
add of delete someone, correct.

KARL DEWEY said:
The table would be easy to make.
You might also find it useful for other queries such as pulling data for all
Senior NCOs - Left([Grade],1)="E" and Right([Grade],1)>=5 for all E-5s and
above.

Marine 1stSgt said:
Do you think I would need to make another table just for this. I would think
there would be a quicker way. I appreciate the quick response.

KARL DEWEY said:
Create a TableOfRanks like this (Not sure of Marine ranks - retired Army MSG
E-8)--
Grade Rank
E-5 ssgt
E-6 gysgt
E-7 msgt
E-8 1stsgt
E-9 mgysgt
E-4 PO1
E-3 bmc
O-1 2Lt
O-2 1Lt
O-3 Capt
O-4 Maj
O-5 LtCol

SELECT Sum(IIF(Left([Grade], 1) = "E", 1, 0)) AS SNCOs,
Sum(IIF(Left([Grade], 1) = "O", 1, 0)) AS Officers
FROM [Bravo DB] LEFT JOIN TableOfRanks ON [Bravo DB].[Rank] =
TableOfRanks.[Rank];

The TableOfRanks is used to translate the Rank to Grades.

:

I am runing into an issue though that is really going to aid me in making my
counts for my Marines and Sailors. I have a database at work with personell
information to include rank, SSN, and such. What I am trying to do is write
a query in Access 2003 count my field of Rank in Groups. What I mean by this
is that I call "SNCO's" for those Marines who have the rank of SSgt, GySgt,
MSgt, 1stSgt, and MGySgt. I used the SQL below which counts fine (a numeric
value). The issue I am having is to add anadditional column that will count
Officers, such as Capt, Maj, LtCol. I have tried a couple of things and it
is not working. I do want to do this in SQL, for I am studying it in college
and I have tried adding additional where statement, but does not work. I
basically want to know how to add addition columns with the same information
I did below, which works for that one columnI want to have this all in one
query. Can anyone help this Marine 1stSgt out?

SELECT COUNT(Rank) AS SNCOs
FROM [Bravo DB]
WHERE rank='ssgt' Or rank='gysgt' Or rank='msgt' Or rank='1stsgt' Or
rank='mgysgt' Or rank='PO1' Or
rank='bmc';
Click to expand...
Click to expand...
Click to expand...
 
One more question, what would you suggest on linking this table of ranks to
go update when I drop or add someone on my main table.
 
If you enforce the Relational Data Integrity, you won't be able to enter a
rank which is not in the table of reference. Use the Relationships button,
from the toolbar (Access 2003) to edit your Relational Data Integrity in
your database.

To make the job easier, to add someone, use a form, and a COMBO BOX having
the table of reference as list of possible values, and LIMIT TO LIST any
input in that combo box.
You can have another form to add a new rank in the reference table, and
would have to add a new rank there before being able to use that new rank in
your 'main' table.

Vanderghast, Access MVP

Marine 1stSgt said:
One more question, what would you suggest on linking this table of ranks
to
go update when I drop or add someone on my main table.

Marine 1stSgt said:
I am runing into an issue though that is really going to aid me in making
my
counts for my Marines and Sailors. I have a database at work with
personell
information to include rank, SSN, and such. What I am trying to do is
write
a query in Access 2003 count my field of Rank in Groups. What I mean by
this
is that I call "SNCO's" for those Marines who have the rank of SSgt,
GySgt,
MSgt, 1stSgt, and MGySgt. I used the SQL below which counts fine (a
numeric
value). The issue I am having is to add anadditional column that will
count
Officers, such as Capt, Maj, LtCol. I have tried a couple of things and
it
is not working. I do want to do this in SQL, for I am studying it in
college
and I have tried adding additional where statement, but does not work. I
basically want to know how to add addition columns with the same
information
I did below, which works for that one columnI want to have this all in
one
query. Can anyone help this Marine 1stSgt out?

SELECT COUNT(Rank) AS SNCOs
FROM [Bravo DB]
WHERE rank='ssgt' Or rank='gysgt' Or rank='msgt' Or rank='1stsgt' Or
rank='mgysgt' Or rank='PO1' Or
rank='bmc';
Click to expand...
Click to expand...
 
Thanks, this makes perfect sense

vanderghast said:
If you enforce the Relational Data Integrity, you won't be able to enter a
rank which is not in the table of reference. Use the Relationships button,
from the toolbar (Access 2003) to edit your Relational Data Integrity in
your database.

To make the job easier, to add someone, use a form, and a COMBO BOX having
the table of reference as list of possible values, and LIMIT TO LIST any
input in that combo box.
You can have another form to add a new rank in the reference table, and
would have to add a new rank there before being able to use that new rank in
your 'main' table.

Vanderghast, Access MVP

Marine 1stSgt said:
One more question, what would you suggest on linking this table of ranks
to
go update when I drop or add someone on my main table.

Marine 1stSgt said:
I am runing into an issue though that is really going to aid me in making
my
counts for my Marines and Sailors. I have a database at work with
personell
information to include rank, SSN, and such. What I am trying to do is
write
a query in Access 2003 count my field of Rank in Groups. What I mean by
this
is that I call "SNCO's" for those Marines who have the rank of SSgt,
GySgt,
MSgt, 1stSgt, and MGySgt. I used the SQL below which counts fine (a
numeric
value). The issue I am having is to add anadditional column that will
count
Officers, such as Capt, Maj, LtCol. I have tried a couple of things and
it
is not working. I do want to do this in SQL, for I am studying it in
college
and I have tried adding additional where statement, but does not work. I
basically want to know how to add addition columns with the same
information
I did below, which works for that one columnI want to have this all in
one
query. Can anyone help this Marine 1stSgt out?

SELECT COUNT(Rank) AS SNCOs
FROM [Bravo DB]
WHERE rank='ssgt' Or rank='gysgt' Or rank='msgt' Or rank='1stsgt' Or
rank='mgysgt' Or rank='PO1' Or
rank='bmc';
Click to expand...
Click to expand...
Click to expand...
 
Back
Top