Using IIF statement in query criteria

[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

 

[Bob Barrows]

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

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.

 

[Bob Barrows]

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

What are the possible values for FG_ind and O-ind?

 

[jgeniti]

Sorry - The data shifted after it posted. I tried to realign the data
below.
Thanks.

 

[jgeniti]

The only values for the FG_ind Column would be "F" or null and "1" or
null for the O_ind Field.

 

[Bob Barrows]

The only values for the FG_ind Column would be "F" or null and "1" or
null for the O_ind Field.

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

 

[John Spencer]

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

 

[jgeniti]

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.

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- Hide quoted text -

- Show quoted text -

 

[Bob Barrows]

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.

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.

 

[jgeniti]

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

 

[Bob Barrows]

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

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;

 

[jgeniti]

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.

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

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 -