# Help with Formula

1. Novice
Windows 10 Access 2016
Join Date
Mar 2021
Posts
29

## Help with Formula

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

2. VIP
Windows 10 Office 365
Join Date
Sep 2017
Location
UK - Wiltshire
Posts
1,945
I think you need something like

IIf([naodd] > 0, Sum(IIf([xfin] = 1, 1, 0)) / Sum(1 / ([naodd] + 1)), 0)

3. What do you want the False to be. It would go after the last comma inside the last Parens.

4. Novice
Windows 10 Access 2016
Join Date
Mar 2021
Posts
29
Originally Posted by Minty
I think you need something like

IIf([naodd] > 0, Sum(IIf([xfin] = 1, 1, 0)) / Sum(1 / ([naodd] + 1)), 0)

I tried that too...

I'm baffled

5. Competent Performer
Windows 7 64bit Access 2013 32bit
Join Date
Dec 2019
Posts
134
You need to post a sample file.

6. VIP
Windows 10 Office 365
Join Date
Sep 2017
Location
UK - Wiltshire
Posts
1,945
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

7. Novice
Windows 10 Access 2016
Join Date
Mar 2021
Posts
29
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

8. VIP
Windows 10 Office 365
Join Date
Sep 2017
Location
UK - Wiltshire
Posts
1,945
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 AE`
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

```
But you will need qualify which table xwin and naood come from in all cases.

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```
EDIT : I can't seem to get this to format nicely - think there is something up with thr forum software...

9. Competent Performer
Windows 7 64bit Access 2013 32bit
Join Date
Dec 2019
Posts
134
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.

10. Novice
Windows 10 Access 2016
Join Date
Mar 2021
Posts
29
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

11. Competent Performer
Windows 7 64bit Access 2013 32bit
Join Date
Dec 2019
Posts
134
In the attachment HX4.zip you will find an example.

12. Novice
Windows 10 Access 2016
Join Date
Mar 2021
Posts
29
Originally Posted by CarlettoFed
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????

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Other Forums: Microsoft Office Forums - Senior Forums