Query multiple criteria on the same Field

[Sara]

Hello,

I have tried this several ways and am stumped. I am trying to query the
following information:

Department
Total Filled Classified per Dept
Total Vacant Classified per Dept
Total Filled Unclassified per Dept
Total Vacant Unclassified per Dept

Right now I have all 4 of those in 4 separate queries because when I try to
put it all in one query it returns nothing. For my "Filled?" field I have the
criteria listed as "Yes" for filled, and "No" for Vacant. For the "Class"
field I have "Like U*" for Unclassified and "Not Like U*" for Classified. I
would like all of this information combined. I've been trying to create a
query to pull all that information. The problem is, not every department has
a vacant position in it, so for those depts that don't have a vacant, the
query leaves that result out. I want to ultimately create a report with this
information. I've tried just creating a report with the 4 separate queries
but it does the same thing -- leaves out depts that don't have both filled
and vacant positions in them. I tried using the Nz() formula but since it's
not technically returning a null value, it doesn't work.

Thanks in advance!!

 

[KARL DEWEY]

Close, try this ---
SELECT Dept, Sum(IIF([Filled]="Yes", 1, 0) AS [Fill],
Sum(IIF([Filled]="Yes", 0, 1) AS [Not Filled], Sum(IIF([Class] Like "U*" , 1,
0) AS [Unclassified], Sum(IIF([Class] Like "U*" , 0, 1) AS [Classified]
FROM YourTable
GROUP BY Dept;

 

[Sara]

I keep getting the error: Syntax Error (Missing Operator). Maybe I'm filling
in the names wrong...

The table's name is "Current Position Information"
The filled/unfilled field is called "Filled?"
The class field is called "CLASS"
The dept field is called "CUBS DEPT"

Here's how I did it:

SELECT [CUBS DEPT], Sum(IIF([Current Position Information].[Filled?]="Yes",
1, 0) AS [Filled],
Sum(IIF([Current Position Information].[Filled?]="Yes", 0, 1) AS [Not
Filled], Sum(IIF([Current Position Information].[CLASS] Like "U*" , 1,
0) AS [Unclassified], Sum(IIF([Current Position Information].[CLASS] Not
Like "U*" , 0, 1) AS [Classified]
FROM [Current Position Information]
GROUP BY [CUBS DEPT];

I even tried it without putting [Current Position Information] in front of
every field. I know I'm probably doing something wrong. I don't use SQL, I
usually just use Design View.

Close, try this ---
SELECT Dept, Sum(IIF([Filled]="Yes", 1, 0) AS [Fill],
Sum(IIF([Filled]="Yes", 0, 1) AS [Not Filled], Sum(IIF([Class] Like "U*" , 1,
0) AS [Unclassified], Sum(IIF([Class] Like "U*" , 0, 1) AS [Classified]
FROM YourTable
GROUP BY Dept;
--
KARL DEWEY
Build a little - Test a little

Hello,

I have tried this several ways and am stumped. I am trying to query the
following information:

Department
Total Filled Classified per Dept
Total Vacant Classified per Dept
Total Filled Unclassified per Dept
Total Vacant Unclassified per Dept

Right now I have all 4 of those in 4 separate queries because when I try to
put it all in one query it returns nothing. For my "Filled?" field I have the
criteria listed as "Yes" for filled, and "No" for Vacant. For the "Class"
field I have "Like U*" for Unclassified and "Not Like U*" for Classified. I
would like all of this information combined. I've been trying to create a
query to pull all that information. The problem is, not every department has
a vacant position in it, so for those depts that don't have a vacant, the
query leaves that result out. I want to ultimately create a report with this
information. I've tried just creating a report with the 4 separate queries
but it does the same thing -- leaves out depts that don't have both filled
and vacant positions in them. I tried using the Nz() formula but since it's
not technically returning a null value, it doesn't work.

Thanks in advance!!

 

[KARL DEWEY]

I left off closing parenthesis.
SELECT [CUBS DEPT], Sum(IIF([Current Position Information].[Filled?]="Yes",
1, 0)) AS [Filled],
Sum(IIF([Current Position Information].[Filled?]="Yes", 0, 1)) AS [Not
Filled], Sum(IIF([Current Position Information].[CLASS] Like "U*" , 1,0)) AS
[Unclassified], Sum(IIF([Current Position Information].[CLASS] Like "U*" ,
0, 1)) AS [Classified]
FROM [Current Position Information]
GROUP BY [CUBS DEPT];

Or in design view use these fields --

[CUBS DEPT]
Filled: IIF([Current Position Information].[Filled?]="Yes", 1, 0)
Not Filled: IIF([Current Position Information].[Filled?]="Yes", 0, 1)
Unclassified: IIF([Current Position Information].[CLASS] Like "U*" , 1,0)
Classified: IIF([Current Position Information].[CLASS] Like "U*" , 0, 1)
Table: [Current Position Information]
GROUP BY Sum Sum Sum Sum

--
KARL DEWEY
Build a little - Test a little

I keep getting the error: Syntax Error (Missing Operator). Maybe I'm filling
in the names wrong...

The table's name is "Current Position Information"
The filled/unfilled field is called "Filled?"
The class field is called "CLASS"
The dept field is called "CUBS DEPT"

Here's how I did it:

SELECT [CUBS DEPT], Sum(IIF([Current Position Information].[Filled?]="Yes",
1, 0) AS [Filled],
Sum(IIF([Current Position Information].[Filled?]="Yes", 0, 1) AS [Not
Filled], Sum(IIF([Current Position Information].[CLASS] Like "U*" , 1,
0) AS [Unclassified], Sum(IIF([Current Position Information].[CLASS] Not
Like "U*" , 0, 1) AS [Classified]
FROM [Current Position Information]
GROUP BY [CUBS DEPT];

I even tried it without putting [Current Position Information] in front of
every field. I know I'm probably doing something wrong. I don't use SQL, I
usually just use Design View.

Close, try this ---
SELECT Dept, Sum(IIF([Filled]="Yes", 1, 0) AS [Fill],
Sum(IIF([Filled]="Yes", 0, 1) AS [Not Filled], Sum(IIF([Class] Like "U*" , 1,
0) AS [Unclassified], Sum(IIF([Class] Like "U*" , 0, 1) AS [Classified]
FROM YourTable
GROUP BY Dept;
--
KARL DEWEY
Build a little - Test a little

Hello,

I have tried this several ways and am stumped. I am trying to query the
following information:

Department
Total Filled Classified per Dept
Total Vacant Classified per Dept
Total Filled Unclassified per Dept
Total Vacant Unclassified per Dept

Right now I have all 4 of those in 4 separate queries because when I try to
put it all in one query it returns nothing. For my "Filled?" field I have the
criteria listed as "Yes" for filled, and "No" for Vacant. For the "Class"
field I have "Like U*" for Unclassified and "Not Like U*" for Classified. I
would like all of this information combined. I've been trying to create a
query to pull all that information. The problem is, not every department has
a vacant position in it, so for those depts that don't have a vacant, the
query leaves that result out. I want to ultimately create a report with this
information. I've tried just creating a report with the 4 separate queries
but it does the same thing -- leaves out depts that don't have both filled
and vacant positions in them. I tried using the Nz() formula but since it's
not technically returning a null value, it doesn't work.

Thanks in advance!!

 

[Sara]

I got it to work, but it's not pulling it correctly. It has 5 columns: Dept,
Filled, Not Filled, Unclass, Class. I need it to be: Dept, Filled Class,
Filled Unclass, Not Filled Class, Not Filled Unclass. If I break apart the
code you gave me and do 4 separate queries, will it now combine correctly
since each of the four queries will have all depts?

I left off closing parenthesis.
SELECT [CUBS DEPT], Sum(IIF([Current Position Information].[Filled?]="Yes",
1, 0)) AS [Filled],
Sum(IIF([Current Position Information].[Filled?]="Yes", 0, 1)) AS [Not
Filled], Sum(IIF([Current Position Information].[CLASS] Like "U*" , 1,0)) AS
[Unclassified], Sum(IIF([Current Position Information].[CLASS] Like "U*" ,
0, 1)) AS [Classified]
FROM [Current Position Information]
GROUP BY [CUBS DEPT];

Or in design view use these fields --

[CUBS DEPT]
Filled: IIF([Current Position Information].[Filled?]="Yes", 1, 0)
Not Filled: IIF([Current Position Information].[Filled?]="Yes", 0, 1)
Unclassified: IIF([Current Position Information].[CLASS] Like "U*" , 1,0)
Classified: IIF([Current Position Information].[CLASS] Like "U*" , 0, 1)
Table: [Current Position Information]
GROUP BY Sum Sum Sum Sum

--
KARL DEWEY
Build a little - Test a little

I keep getting the error: Syntax Error (Missing Operator). Maybe I'm filling
in the names wrong...

The table's name is "Current Position Information"
The filled/unfilled field is called "Filled?"
The class field is called "CLASS"
The dept field is called "CUBS DEPT"

Here's how I did it:

SELECT [CUBS DEPT], Sum(IIF([Current Position Information].[Filled?]="Yes",
1, 0) AS [Filled],
Sum(IIF([Current Position Information].[Filled?]="Yes", 0, 1) AS [Not
Filled], Sum(IIF([Current Position Information].[CLASS] Like "U*" , 1,
0) AS [Unclassified], Sum(IIF([Current Position Information].[CLASS] Not
Like "U*" , 0, 1) AS [Classified]
FROM [Current Position Information]
GROUP BY [CUBS DEPT];

I even tried it without putting [Current Position Information] in front of
every field. I know I'm probably doing something wrong. I don't use SQL, I
usually just use Design View.

Close, try this ---
SELECT Dept, Sum(IIF([Filled]="Yes", 1, 0) AS [Fill],
Sum(IIF([Filled]="Yes", 0, 1) AS [Not Filled], Sum(IIF([Class] Like "U*" , 1,
0) AS [Unclassified], Sum(IIF([Class] Like "U*" , 0, 1) AS [Classified]
FROM YourTable
GROUP BY Dept;

 

[Sara]

I think I might've gotten it figured out. I'm going to create 4 separate
queries and then a 5th one to combine the 4. I accidentally left out a field
in my explanation so it got even more complicated (it's along the lines I
need the total of FTE ([TOTAL] field] of Unclassified Vacant positions in
each dept) and I really appreciate you helping me (it helped me learn the IIF
function!). This is what I did:

[CUBS DEPT]
Group By

Unclassified Filled: IIF([CLASS] Like "U*" And [Filled?]=Yes, [TOTAL], 0)