Using IIF statement in query criteria

  • Thread starter Thread starter jgeniti
  • Start date Start date
J

jgeniti

Can someone please help me with the logic behind the query I'm trying
to run.

I am trying to return 1 record per ProjNum using the following
criteria.

If FG_ind = F then return that row. (There is never more then one F
per project number)
If there is no F then I need to get the last modified record
(Maint_date) with a "1" in the O_ind field.

I tried creating a query and then sorting by Proj #, then FG_ind(Desc)
and then Maint_date(Desc). When I run the query the correct row is
always on top for each project, but when I try to group them so I can
try to get the first record it allways returns more then one row
because I have sever non-distinct fields that are in the query. I
thought maybe there was a way to add some logic to the criteria of the
FG_ind field in the query.

Any help would be appreciated.

EstNum ProjNum FG_ind O_ind Maint_date Year
1 25 1 1/1/2010 2010
2 25 1 11/5/2008 2010
3 25 F 1 5/6/2009 2010
4 57 1 2/2/2010 2010
5 57 1 5/5/2010 2010
 
jgeniti said:
Can someone please help me with the logic behind the query I'm trying
to run.

I am trying to return 1 record per ProjNum using the following
criteria.

If FG_ind = F then return that row. (There is never more then one F
per project number)
If there is no F then I need to get the last modified record
(Maint_date) with a "1" in the O_ind field.

I tried creating a query and then sorting by Proj #, then FG_ind(Desc)
and then Maint_date(Desc). When I run the query the correct row is
always on top for each project, but when I try to group them so I can
try to get the first record it allways returns more then one row
because I have sever non-distinct fields that are in the query. I
thought maybe there was a way to add some logic to the criteria of the
FG_ind field in the query.

Any help would be appreciated.

EstNum ProjNum FG_ind O_ind Maint_date Year
1 25 1 1/1/2010 2010
2 25 1 11/5/2008 2010
3 25 F 1 5/6/2009 2010
4 57 1 2/2/2010 2010
5 57 1 5/5/2010 2010
Click to expand...


None of your sample data seems to contain a value for the O_ind field.
Is that correct or am I misreading it?
You did not indicate which of these rows should be returned by the
query.
 
jgeniti said:
Can someone please help me with the logic behind the query I'm trying
to run.

I am trying to return 1 record per ProjNum using the following
criteria.

If FG_ind = F then return that row. (There is never more then one F
per project number)
If there is no F then I need to get the last modified record
(Maint_date) with a "1" in the O_ind field.

I tried creating a query and then sorting by Proj #, then FG_ind(Desc)
and then Maint_date(Desc). When I run the query the correct row is
always on top for each project, but when I try to group them so I can
try to get the first record it allways returns more then one row
because I have sever non-distinct fields that are in the query. I
thought maybe there was a way to add some logic to the criteria of the
FG_ind field in the query.

Any help would be appreciated.

EstNum ProjNum FG_ind O_ind Maint_date Year
1 25 1 1/1/2010 2010
2 25 1 11/5/2008 2010
3 25 F 1 5/6/2009 2010
4 57 1 2/2/2010 2010
5 57 1 5/5/2010 2010
Click to expand...

What are the possible values for FG_ind and O-ind?
 
The only values for the FG_ind Column would be "F" or null and "1" or
null for the O_ind Field.
 
jgeniti said:
The only values for the FG_ind Column would be "F" or null and "1" or
null for the O_ind Field.
Click to expand...

Assuming you want EstNum 3 and 5, this works:
SELECT EstNum, ProjNum, FG_Ind, O_ind, Maint_Date, ProjYear
FROM Projects
WHERE FG_Ind="F"
UNION ALL
SELECT p.EstNum, p.ProjNum, p.FG_Ind, p.O_ind, p.Maint_Date, p.ProjYear
FROM Projects As p INNER JOIN (
SELECT Projects.ProjNum, Max(Projects.Maint_Date) As MaxMaint
FROM Projects
WHERE Projects.ProjNum In (select projnum from projects group by projnum
having max(FG_ind) is null ) AND Projects.O_ind="1"
GROUP BY ProjNum) As q ON p.ProjNum=q.ProjNum and p.Maint_date
=q.MaxMaint

But if the data looks like this:

So that there is a tie, then you will get rows 3-5
 
You can use a coordinated sub-query to return the desired results. Assumption
is that EstNum is unique (possibly the primary key).

SELECT EstNum, ProjNum, FG_Ind, O_Ind, Maint_Date, [Year]
FROM [TheTable]
WHERE EstNum in
(SELECT TOP 1 estNum
FROM [TheTable] as Temp
WHERE Temp.ProjNum = TheTable.ProjNum
ORDER BY FG_Ind DESC, Maint_Date Desc)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks for the help Bob and John,
Here is my delema - The query John wrote works for me as long as I put
O_ind = 1 in the criteria, but it takes a lot longer to run. The query
Bob did runs quicker but still seems to be returning more then one row
for any records that have more then one O_ind. I was trying to not
write too much in my original post and I think I did more harm by not
giving the full details. I should also say that I did try to put some
extra criteria in Bob's query, so I might have screwed up the logic.
Here is the business rules that I'm trying to follow in order of what
record I should choose.

1st I want any record that has an "F" in the fg_ind field - No project
can have more then one record with an F assigned to it so that's the
easy part.
If there is no record with an "F", I want the one with the 1 in the
o_ind field. There can be multiple 1's per project so I need the one
that was revised last (maint_date).
All records with and F will always have a 1.
If the record doesn't have at least a 1 in the o_ind field then it
should be ignored completely.


Here are the actual query that I altered from Bob. You will notice
that I used the actual field and table names instead of the shortened
versions from my original post. I changed the original WHERE statement
from "fg_ind = F" to "official_ind = 1 AND line_year = 2012" because
I was only getting record with an F and no records with just the
official_ind = 1. When I run this I will get all records with a 1 not
the last modified one.

Thank you again for your help

SELECT Estimate_id, Proj_no, FG_Ind, Official_ind, Maint_Date,
Line_year
FROM Dbo_product_cost_summary
WHERE official_ind = 1 AND line_year = 2012
UNION ALL SELECT p.Estimate_id, p.Proj_no, p.FG_Ind, p.Official_ind,
p.Maint_Date, p.Line_year
FROM Dbo_product_cost_summary As p INNER JOIN (
SELECT Dbo_product_cost_summary.Proj_no,
Max(Dbo_product_cost_summary.Maint_Date) As MaxMaint
FROM Dbo_product_cost_summary
WHERE Dbo_product_cost_summary.Proj_no In (select proj_no from
dbo_product_cost_summary group by proj_no
having max(FG_ind) is null ) AND
Dbo_product_cost_summary.Official_ind=1
GROUP BY Proj_no) As q ON p.Proj_no=q.Proj_no and p.Maint_date
=q.MaxMaint;


You can use a coordinated sub-query to return the desired results.  Assumption
is that EstNum is unique (possibly the primary key).

SELECT EstNum, ProjNum, FG_Ind, O_Ind, Maint_Date, [Year]
FROM [TheTable]
WHERE EstNum in
    (SELECT TOP 1 estNum
    FROM [TheTable] as Temp
    WHERE Temp.ProjNum = TheTable.ProjNum
    ORDER BY FG_Ind DESC, Maint_Date Desc)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County


Can someone please help me with the logic behind the query I'm trying
to run.
Click to expand...
I am trying to return 1 record per ProjNum using the following
criteria.
Click to expand...
If FG_ind = F then return that row. (There is never more then one F
per project number)
If there is no F then I need to get the last modified record
(Maint_date) with a "1" in the O_ind field.
Click to expand...
I tried creating a query and then sorting by Proj #, then FG_ind(Desc)
and then Maint_date(Desc). When I run the query the correct row is
always on top for each project, but when I try to group them so I can
try to get the first record it allways returns more then one row
because I have sever non-distinct fields that are in the query. I
thought maybe there was a way to add some logic to the criteria of the
FG_ind field in the query.
Click to expand...
Any help would be appreciated.
Click to expand...
EstNum     ProjNum FG_ind  O_ind   Maint_date   Year
1  25              1       1/1/2010           2010
2  25              1       11/5/2008          2010
3  25      F       1       5/6/2009          2010
4  57              1       2/2/2010           2010
5  57              1       5/5/2010           2010- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
jgeniti said:
Here are the actual query that I altered from Bob. You will notice
that I used the actual field and table names instead of the shortened
versions from my original post. I changed the original WHERE statement
from "fg_ind = F" to "official_ind = 1 AND line_year = 2012" because
I was only getting record with an F and no records with just the
official_ind = 1. When I run this I will get all records with a 1 not
the last modified one.
Click to expand...

That's the problem. The first half of this union query is supposed to
return only projects that have an "F" FG_ind. The second half of the
union is only supposed to return projects that do not have an "F"
FG_ind. The query I posted worked perfectly for the sample records you
provided. In order to revise it to fix the problem you are seeing, you
need to show us the data for which the incorrect results are returned.
 
Here are two examples of multiple records being returned per project
number. Ideally this should only return Estimate ID # 39839 and 39886.
If I leave the filter for the first half of the query to fg_ind = "F"
then the query will only return records with an "F". I also need to
see the projects that don't have an "F" yest, but do at least have a 1
in the official_ind field.

Estimate_id Proj_no FG_Ind Official_ind
Maint_Date Line_year
39955 211843 1
10/15/2010 1:21:59 PM 2012
39839 211843 1
10/15/2010 1:24:34 PM 2012
39886 212767 F 1
10/5/2010 8:38:54 AM 2012
39713 212767 1
9/27/2010 4:42:15 PM 2012
 
jgeniti said:
Here are two examples of multiple records being returned per project
number. Ideally this should only return Estimate ID # 39839 and 39886.
If I leave the filter for the first half of the query to fg_ind = "F"
then the query will only return records with an "F". I also need to
see the projects that don't have an "F" yest, but do at least have a 1
in the official_ind field.

Estimate_id Proj_no FG_Ind Official_ind
Maint_Date Line_year
39955 211843 1
10/15/2010 1:21:59 PM 2012
39839 211843 1
10/15/2010 1:24:34 PM 2012
39886 212767 F 1
10/5/2010 8:38:54 AM 2012
39713 212767 1
9/27/2010 4:42:15 PM 2012
Click to expand...

Here are the records I get when I run my query unaltered:

EstNum ProjNum FG_Ind O_ind Maint_Date ProjYear
3 25 F 1 5/6/2009
2010
39886 212767 F 1 10/5/2010 8:38:54 AM 2012
5 57 1 5/5/2010
2010
39839 211843 1 10/15/2010 1:24:34 PM 2012

It seems to be returning the correct records ... I'm not sure how to
reproduce your results. Here is the SQL that will build the table I'm
using:
CREATE TABLE [Projects] ([EstNum] LONG, [ProjNum] LONG, [FG_Ind]
TEXT(50), [O_ind] TEXT(50), [Maint_Date] DATETIME, [ProjYear] LONG);

CREATE INDEX [EstNum] On [Projects] ([EstNum])

CREATE UNIQUE INDEX [PrimaryKey] On [Projects] ([EstNum], [ProjNum])
WITH PRIMARY

And here is the query again:
SELECT EstNum, ProjNum, FG_Ind, O_ind, Maint_Date, ProjYear
FROM Projects
WHERE FG_Ind="F"
UNION ALL
SELECT p.EstNum, p.ProjNum, p.FG_Ind, p.O_ind, p.Maint_Date, p.ProjYear
FROM Projects As p INNER JOIN (
SELECT Projects.ProjNum, Max(Projects.Maint_Date) As MaxMaint
FROM Projects
WHERE Projects.ProjNum In (select projnum from projects group by projnum
having max(FG_ind) is null ) AND Projects.O_ind="1"
GROUP BY ProjNum) As q ON p.ProjNum=q.ProjNum and p.Maint_date
=q.MaxMaint;
 
I found out what the problem was. I had to use max(fg_ind) = ""
instead of max(fg_ind) is null. This gave me the desired results.
Thank you guys again for your help. It is greatly appreciated.


jgeniti said:
Here are two examples of multiple records being returned per project
number. Ideally this should only return Estimate ID # 39839 and 39886.
If I leave the filter for the first half of the query to fg_ind = "F"
then the query will only return records with an "F". I also need to
see the projects that don't have an "F" yest, but do at least have a 1
in the official_ind field.
Click to expand...
Estimate_id              Proj_no    FG_Ind  Official_ind
Maint_Date                      Line_year
39955                 211843                     1
10/15/2010 1:21:59 PM           2012
39839                 211843                     1
10/15/2010 1:24:34 PM           2012
39886                 212767      F             1
10/5/2010 8:38:54 AM             2012
39713                 212767                     1
9/27/2010 4:42:15 PM            2012
Click to expand...

Here are the records I get when I run my query unaltered:

EstNum ProjNum FG_Ind O_ind Maint_Date ProjYear
3             25         F         1     5/6/2009
2010
39886     212767   F         1     10/5/2010 8:38:54 AM2012
5             57                    1    5/5/2010
2010
39839     211843              1     10/15/2010 1:24:34 PM 2012

It seems to be returning the correct records ... I'm not sure how to
reproduce your results. Here is the SQL that will build the table I'm
using:
CREATE TABLE [Projects] ([EstNum] LONG, [ProjNum] LONG, [FG_Ind]
TEXT(50), [O_ind] TEXT(50), [Maint_Date] DATETIME, [ProjYear] LONG);

CREATE  INDEX [EstNum] On [Projects] ([EstNum])

CREATE  UNIQUE  INDEX [PrimaryKey] On [Projects] ([EstNum], [ProjNum])
WITH PRIMARY

And here is the query again:
SELECT EstNum, ProjNum, FG_Ind, O_ind, Maint_Date, ProjYear
FROM Projects
WHERE FG_Ind="F"
UNION ALL
SELECT p.EstNum, p.ProjNum, p.FG_Ind, p.O_ind, p.Maint_Date, p.ProjYear
FROM Projects As p INNER JOIN (
SELECT Projects.ProjNum, Max(Projects.Maint_Date) As MaxMaint
FROM Projects
WHERE Projects.ProjNum In (select projnum from projects group by projnum
having max(FG_ind) is null ) AND Projects.O_ind="1"
GROUP BY ProjNum) As q ON p.ProjNum=q.ProjNum and p.Maint_date
=q.MaxMaint;

--
HTH,
Bob Barrows- Hide quoted text -

- Show quoted text -
Click to expand...
 
Back
Top