Hey....
Having problem fixing this.....
IIF([naodd] >0, Sum(IIf([xfin]=1,1,0))/Sum(1/([naodd]+1)),)
It might be the false part but I cant figure it out..
Thxs
Hey....
Having problem fixing this.....
IIF([naodd] >0, Sum(IIf([xfin]=1,1,0))/Sum(1/([naodd]+1)),)
It might be the false part but I cant figure it out..
Thxs
I think you need something like
IIf([naodd] > 0, Sum(IIf([xfin] = 1, 1, 0)) / Sum(1 / ([naodd] + 1)), 0)
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
What do you want the False to be. It would go after the last comma inside the last Parens.
You need to post a sample file.
You can't Sum(A_Field) without it being part of a grouped (Totals) query.
I think we either need to see some data sets - some sample Starting data, and what you expect from that as a result of your query.
We will need to see enough data to cover all the expected outcome's
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
SELECT Count(ALL_HX4.xfin) AS CountOfxfin, Sum(IIf([xwin]>0,1,0)) AS Winners, Sum(ALL_HX4.xwin) AS SumOfxwin, Sum(IIf([xWIN]>0,1,0))/Count([xwin])*100 AS [WIN %], Sum([xWin])/(Count([xwin])*2) AS ROI, [SumOfxwin]/[Winners] AS [AVG PAY], Sum(IIf([xfin]=1,1,0))/Sum(1/([naodd]+1)) AS AE
FROM (ALL_HX4 INNER JOIN ALL_HX44 ON (ALL_HX4.tPGM = ALL_HX44.tPGM) AND (ALL_HX4.nRACE = ALL_HX44.nRACE) AND (ALL_HX4.tDATE = ALL_HX44.tDATE) AND (ALL_HX4.tTRK = ALL_HX44.tTRK)) INNER JOIN ALL_HXD ON (ALL_HX44.tPGM = ALL_HXD.tPGM) AND (ALL_HX44.nRACE = ALL_HXD.nRACE) AND (ALL_HX44.tDATE = ALL_HXD.tDATE) AND (ALL_HX44.tTRK = ALL_HXD.tTRK);
Design and part of file
It probably need to be something like
Code:SELECT Count(ALL_HX4.xfin) AS CountOfxfin, Sum(IIf([xwin]>0,1,0)) AS Winners, Sum(ALL_HX4.xwin) AS SumOfxwin, Sum(IIf([xWIN]>0,1,0))/Count([xwin])*100 AS [WIN %], Sum([xWin])/(Count([xwin])*2) AS ROI, [SumOfxwin]/[Winners] AS [AVG PAY], Sum(IIf([xfin]=1,1,0))/Sum(1/([naodd]+1)) AS AEBut you will need qualify which table xwin and naood come from in all cases.Code:FROM (ALL_HX4 INNER JOIN ALL_HX44 ON (ALL_HX4.tPGM = ALL_HX44.tPGM) AND (ALL_HX4.nRACE = ALL_HX44.nRACE) AND (ALL_HX4.tDATE = ALL_HX44.tDATE) AND (ALL_HX4.tTRK = ALL_HX44.tTRK)) INNER JOIN ALL_HXD ON (ALL_HX44.tPGM = ALL_HXD.tPGM) AND (ALL_HX44.nRACE = ALL_HXD.nRACE) AND (ALL_HX44.tDATE = ALL_HXD.tDATE) AND (ALL_HX44.tTRK = ALL_HXD.tTRK) GROUP BY ALL_HX4.xfin , xwin , ALL_HX4.xwin , naodd
EDIT : I can't seem to get this to format nicely - think there is something up with thr forum software...Code:SELECT Count(ALL_HX4.xfin) AS CountOfxfin, Sum(IIf([xwin]>0,1,0)) AS Winners, Sum(ALL_HX4.xwin) AS SumOfxwin, Sum(IIf([xWIN]>0,1,0))/Count([xwin])*100 AS [WIN %], Sum([xWin])/(Count([xwin])*2) AS ROI, [SumOfxwin]/[Winners] AS [AVG PAY], Sum(IIf([xfin]=1,1,0))/Sum(1/([naodd]+1)) AS AE FROM (ALL_HX4 INNER JOIN ALL_HX44 ON (ALL_HX4.tPGM = ALL_HX44.tPGM) AND (ALL_HX4.nRACE = ALL_HX44.nRACE) AND (ALL_HX4.tDATE = ALL_HX44.tDATE) AND (ALL_HX4.tTRK = ALL_HX44.tTRK)) INNER JOIN ALL_HXD ON (ALL_HX44.tPGM = ALL_HXD.tPGM) AND (ALL_HX44.nRACE = ALL_HXD.nRACE) AND (ALL_HX44.tDATE = ALL_HXD.tDATE) AND (ALL_HX44.tTRK = ALL_HXD.tTRK) GROUP BY ALL_HX4.xfin , xwin , ALL_HX4.xwin , naodd
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
In the attachment HX4.zip you will find an example, naturally in the query the ALL_HX44 table that you have related to the ALL_HX4 is missing as it is not known what the structure is.
heres all the parts...
SELECT Count(ALL_HX4.xfin) AS CountOfxfin, Sum(IIf([xwin]>0,1,0)) AS Winners, Sum(ALL_HX4.xwin) AS SumOfxwin, Sum(IIf([xWIN]>0,1,0))/Count([xwin])*100 AS [WIN %], Sum([xWin])/(Count([xwin])*2) AS ROI, [SumOfxwin]/[Winners] AS [AVG PAY], Sum(IIf([xfin]=1,1,0))/Sum(1/([naodd]+1)) AS AE
FROM (ALL_HX4 INNER JOIN ALL_HX44 ON (ALL_HX4.tPGM = ALL_HX44.tPGM) AND (ALL_HX4.nRACE = ALL_HX44.nRACE) AND (ALL_HX4.tDATE = ALL_HX44.tDATE) AND (ALL_HX4.tTRK = ALL_HX44.tTRK)) INNER JOIN ALL_HXD ON (ALL_HX44.tPGM = ALL_HXD.tPGM) AND (ALL_HX44.nRACE = ALL_HXD.nRACE) AND (ALL_HX44.tDATE = ALL_HXD.tDATE) AND (ALL_HX44.tTRK = ALL_HXD.tTRK);
I still need [nAODD] to use >0
thxs for helping
Mike
In the attachment HX4.zip you will find an example.
In the attachment HX4.zip you will find an example.
Your so close CAR........AE is a better measure than ROI.........and should produce a similar number.........your is too much value wise , but it avoided the division by zero....
here is what your result should get after I manually made the -1 a 0....
maybe you reversed something in the expression????