Multiple Criteria Query

[kohai]

Hi,

I am trying to build a query that calculates Price/EPS.

I'm creating a P/E field where the "E" is built from a complex check if all
4 quarters are available (these are the qecq1,qecq2,qecq3,qecq4).

If all 4 are not available, I have another less desirable field that I could
use for the E which is "fyntm"

If neither are avail, the field is Null.

PE: [eps_history].[Price]/(IIf((IIf(Not
IsNull([eps_history].[qecq1]),1,0)+IIf(Not
IsNull([eps_history].[qecq2]),1,0)+IIf(Not
IsNull([eps_history].[qecq3]),1,0)+IIf(Not
IsNull([eps_history].[qecq4]),1,0))<4,IIf(Not
IsNull([eps_history].[fyntm]),[eps_history].[fyntm],Null),([eps_history].[qecq1]+[eps_history].[qecq2]+[eps_history].[qecq3]+[eps_history].[qecq4])))

Since I have a "group by" date and a sub-group, I am taking the average for
each group on each day.

I then have a 2 where clauses, price not null and the eps portion above not
null (everything after the / on the 1st line)

This all works ok. My problem lies that I am trying to get only those PE's
that are <= X, where X may be 100 or 50 etc.

If I add another where clause of the entire PE calculation, it tells me that
there is division by zero and doesn't work. It seems that the previous where
clause of EPS not null and >0 doesn't affect this column where I then need to
get a P/E >0 and less than X.

The entire lengthy SQL is below. I know this is a long question, but I hope
that someone who knows SQL more than I can help me fix this messy thing.

SELECT EPS_History.PEDATE, [PORT_DailyData].Sect,
[eps_history].[Price]/(IIf((IIf(Not
IsNull([eps_history].[qecq1]),1,0)+IIf(Not
IsNull([eps_history].[qecq2]),1,0)+IIf(Not
IsNull([eps_history].[qecq3]),1,0)+IIf(Not
IsNull([eps_history].[qecq4]),1,0))<4,IIf(Not
IsNull([eps_history].[fyntm]),[eps_history].[fyntm],Null),([eps_history].[qecq1]+[eps_history].[qecq2]+[eps_history].[qecq3]+[eps_history].[qecq4])))
AS AvgPE, EPS_History.Price, (IIf((IIf(Not
IsNull([eps_history].[qecq1]),1,0)+IIf(Not
IsNull([eps_history].[qecq2]),1,0)+IIf(Not
IsNull([eps_history].[qecq3]),1,0)+IIf(Not
IsNull([eps_history].[qecq4]),1,0))<4,IIf(Not
IsNull([eps_history].[fyntm]),[eps_history].[fyntm],Null),([eps_history].[qecq1]+[eps_history].[qecq2]+[eps_history].[qecq3]+[eps_history].[qecq4]))) AS Eps
FROM PORT_DailyData INNER JOIN EPS_History ON ([PORT_DailyData].CUSIP =
EPS_History.CUSIP) AND ([PORT_DailyData].Date = EPS_History.PEDATE)
WHERE ((Not (EPS_History.Price) Is Null) AND (Not ((IIf((IIf(Not
IsNull([eps_history].[qecq1]),1,0)+IIf(Not
IsNull([eps_history].[qecq2]),1,0)+IIf(Not
IsNull([eps_history].[qecq3]),1,0)+IIf(Not
IsNull([eps_history].[qecq4]),1,0))<4,IIf(Not
IsNull([eps_history].[fyntm]),[eps_history].[fyntm],Null),([eps_history].[qecq1]+[eps_history].[qecq2]+[eps_history].[qecq3]+[eps_history].[qecq4]))))
Is Null And ((IIf((IIf(Not IsNull([eps_history].[qecq1]),1,0)+IIf(Not
IsNull([eps_history].[qecq2]),1,0)+IIf(Not
IsNull([eps_history].[qecq3]),1,0)+IIf(Not
IsNull([eps_history].[qecq4]),1,0))<4,IIf(Not
IsNull([eps_history].[fyntm]),[eps_history].[fyntm],Null),([eps_history].[qecq1]+[eps_history].[qecq2]+[eps_history].[qecq3]+[eps_history].[qecq4]))))>0))
ORDER BY EPS_History.PEDATE, [PORT_DailyData].Sect;


Thank you .
Kohai

 

[Gary Walter]

Hi Kohai,

I wasn't going to respond, but since nobody has...

I would move all this to user-defined function(s)
(maybe one for PE and one for EPS?).

Feed it (them) Price, the quarter fields, and fyntm,
and do all logic and computation in the function.
Handle divide-by-zero there.

Life will be alot simpler plus SQL will be easier to read.

One man's opinion..

Good Luck,

gary

I am trying to build a query that calculates Price/EPS.

I'm creating a P/E field where the "E" is built from a complex check if
all
4 quarters are available (these are the qecq1,qecq2,qecq3,qecq4).

If all 4 are not available, I have another less desirable field that I
could
use for the E which is "fyntm"

If neither are avail, the field is Null.

PE: [eps_history].[Price]/(IIf((IIf(Not
IsNull([eps_history].[qecq1]),1,0)+IIf(Not
IsNull([eps_history].[qecq2]),1,0)+IIf(Not
IsNull([eps_history].[qecq3]),1,0)+IIf(Not
IsNull([eps_history].[qecq4]),1,0))<4,IIf(Not
IsNull([eps_history].[fyntm]),[eps_history].[fyntm],Null),([eps_history].[qecq1]+[eps_history].[qecq2]+[eps_history].[qecq3]+[eps_history].[qecq4])))

Since I have a "group by" date and a sub-group, I am taking the average
for
each group on each day.

I then have a 2 where clauses, price not null and the eps portion above
not
null (everything after the / on the 1st line)

This all works ok. My problem lies that I am trying to get only those
PE's
that are <= X, where X may be 100 or 50 etc.

If I add another where clause of the entire PE calculation, it tells me
that
there is division by zero and doesn't work. It seems that the previous
where
clause of EPS not null and >0 doesn't affect this column where I then need
to
get a P/E >0 and less than X.

The entire lengthy SQL is below. I know this is a long question, but I
hope
that someone who knows SQL more than I can help me fix this messy thing.

SELECT EPS_History.PEDATE, [PORT_DailyData].Sect,
[eps_history].[Price]/(IIf((IIf(Not
IsNull([eps_history].[qecq1]),1,0)+IIf(Not
IsNull([eps_history].[qecq2]),1,0)+IIf(Not
IsNull([eps_history].[qecq3]),1,0)+IIf(Not
IsNull([eps_history].[qecq4]),1,0))<4,IIf(Not
IsNull([eps_history].[fyntm]),[eps_history].[fyntm],Null),([eps_history].[qecq1]+[eps_history].[qecq2]+[eps_history].[qecq3]+[eps_history].[qecq4])))
AS AvgPE, EPS_History.Price, (IIf((IIf(Not
IsNull([eps_history].[qecq1]),1,0)+IIf(Not
IsNull([eps_history].[qecq2]),1,0)+IIf(Not
IsNull([eps_history].[qecq3]),1,0)+IIf(Not
IsNull([eps_history].[qecq4]),1,0))<4,IIf(Not
IsNull([eps_history].[fyntm]),[eps_history].[fyntm],Null),([eps_history].[qecq1]+[eps_history].[qecq2]+[eps_history].[qecq3]+[eps_history].[qecq4])))
AS Eps
FROM PORT_DailyData INNER JOIN EPS_History ON ([PORT_DailyData].CUSIP =
EPS_History.CUSIP) AND ([PORT_DailyData].Date = EPS_History.PEDATE)
WHERE ((Not (EPS_History.Price) Is Null) AND (Not ((IIf((IIf(Not
IsNull([eps_history].[qecq1]),1,0)+IIf(Not
IsNull([eps_history].[qecq2]),1,0)+IIf(Not
IsNull([eps_history].[qecq3]),1,0)+IIf(Not
IsNull([eps_history].[qecq4]),1,0))<4,IIf(Not
IsNull([eps_history].[fyntm]),[eps_history].[fyntm],Null),([eps_history].[qecq1]+[eps_history].[qecq2]+[eps_history].[qecq3]+[eps_history].[qecq4]))))
Is Null And ((IIf((IIf(Not IsNull([eps_history].[qecq1]),1,0)+IIf(Not
IsNull([eps_history].[qecq2]),1,0)+IIf(Not
IsNull([eps_history].[qecq3]),1,0)+IIf(Not
IsNull([eps_history].[qecq4]),1,0))<4,IIf(Not
IsNull([eps_history].[fyntm]),[eps_history].[fyntm],Null),([eps_history].[qecq1]+[eps_history].[qecq2]+[eps_history].[qecq3]+[eps_history].[qecq4]))))>0))
ORDER BY EPS_History.PEDATE, [PORT_DailyData].Sect;


Thank you .
Kohai

 

[Gary Walter]

One more thought...

I probably would empty a "report table"
then feed it new data using just "fGetEPS"
(something like

CurrentDB.Execute "DELETE * FROM tblReport", dbFailOnError
strSQL = "SELECT EPS_History.PEDATE, " _
& "[PORT_DailyData].Sect, [eps_history].[Price], " _
& "fGetEPS([qecq1], [qecq2], [qecq3], [qecq4], [fytm])AS Eps " _
& "FROM PORT_DailyData INNER JOIN EPS_History " _
& "ON ([PORT_DailyData].CUSIP = EPS_History.CUSIP) " _
& "AND ([PORT_DailyData].Date = EPS_History.PEDATE) " _
& "INTO tblReport " _
& "WHERE EPS_History.Price Is Not Null;"
CurrentDB.Execute strSQL, dbFailOnError


then, do what you must using "tblReport."

 

[Gary Walter]

sorry...gave you initial "make table" query
which would work if you "dropped" instead of "deleted"
tblReport in first stmt (if it existed).....
but I believe consensus of this newsgroup is
that it is better to "insert"

CurrentDB.Execute "DELETE * FROM tblReport", dbFailOnError
strSQL = "INSERT INTO tblReport (PEDATE, Sect, Price, Eps) " _
& "SELECT EPS_History.PEDATE, " _
& "[PORT_DailyData].Sect, [eps_history].[Price], " _
& "fGetEPS([qecq1], [qecq2], [qecq3], [qecq4], [fytm]) AS Eps " _
& "FROM PORT_DailyData INNER JOIN EPS_History " _
& "ON ([PORT_DailyData].CUSIP = EPS_History.CUSIP) " _
& "AND ([PORT_DailyData].Date = EPS_History.PEDATE) " _
& "WHERE EPS_History.Price Is Not Null;"
CurrentDB.Execute strSQL, dbFailOnError

 

[kohai]

Gary,

Thank you so much for responding. I know this was an ugly one.

I was able to get it to work by having another where clause for the entire
PE calculation and used a "Between X and Y". This resolved my divide by zero
problem.

I can work on writing the function, I'm just not too sure how to call it
from the query, but based on one of your examples below I see that shouldn't
be too difficult.

Thanks again for getting back to my question.

Regards,
Kohai

sorry...gave you initial "make table" query
which would work if you "dropped" instead of "deleted"
tblReport in first stmt (if it existed).....
but I believe consensus of this newsgroup is
that it is better to "insert"

CurrentDB.Execute "DELETE * FROM tblReport", dbFailOnError
strSQL = "INSERT INTO tblReport (PEDATE, Sect, Price, Eps) " _
& "SELECT EPS_History.PEDATE, " _
& "[PORT_DailyData].Sect, [eps_history].[Price], " _
& "fGetEPS([qecq1], [qecq2], [qecq3], [qecq4], [fytm]) AS Eps " _
& "FROM PORT_DailyData INNER JOIN EPS_History " _
& "ON ([PORT_DailyData].CUSIP = EPS_History.CUSIP) " _
& "AND ([PORT_DailyData].Date = EPS_History.PEDATE) " _
& "WHERE EPS_History.Price Is Not Null;"
CurrentDB.Execute strSQL, dbFailOnError

I probably would empty a "report table"
then feed it new data using just "fGetEPS"
(something like

CurrentDB.Execute "DELETE * FROM tblReport", dbFailOnError
strSQL = "SELECT EPS_History.PEDATE, " _
& "[PORT_DailyData].Sect, [eps_history].[Price], " _
& "fGetEPS([qecq1], [qecq2], [qecq3], [qecq4], [fytm]) AS Eps " _
& "FROM PORT_DailyData INNER JOIN EPS_History " _
& "ON ([PORT_DailyData].CUSIP = EPS_History.CUSIP) " _
& "AND ([PORT_DailyData].Date = EPS_History.PEDATE) " _
& "INTO tblReport " _
& "WHERE EPS_History.Price Is Not Null;"
CurrentDB.Execute strSQL, dbFailOnError


then, do what you must using "tblReport."