Results 1 to 12 of 12
  1. #1
    mikesal57 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    48

    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. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    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 ↓↓

  3. #3
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    What do you want the False to be. It would go after the last comma inside the last Parens.

  4. #4
    mikesal57 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    48
    Quote Originally Posted by Minty View Post
    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 baffledClick image for larger version. 

Name:	x.JPG 
Views:	20 
Size:	32.5 KB 
ID:	45499

  5. #5
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    You need to post a sample file.

  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    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 ↓↓

  7. #7
    mikesal57 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    48
    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
    Attached Files Attached Files

  8. #8
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    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...
    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 ↓↓

  9. #9
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    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. #10
    mikesal57 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    48
    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
    Click image for larger version. 

Name:	Capture.jpg 
Views:	15 
Size:	43.9 KB 
ID:	45505


    thxs for helping

    Mike
    Attached Files Attached Files

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

  12. #12
    mikesal57 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    48
    Quote Originally Posted by CarlettoFed View Post
    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????

    Click image for larger version. 

Name:	1.JPG 
Views:	11 
Size:	27.8 KB 
ID:	45517Click image for larger version. 

Name:	2.JPG 
Views:	10 
Size:	24.6 KB 
ID:	45518

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 19
    Last Post: 07-13-2015, 10:15 AM
  2. Replies: 41
    Last Post: 05-29-2015, 02:58 PM
  3. IRR Formula in VBA
    By Mpike926 in forum Access
    Replies: 2
    Last Post: 05-19-2015, 02:48 PM
  4. Please help with formula
    By JeanZander in forum Database Design
    Replies: 2
    Last Post: 01-07-2013, 04:39 PM
  5. Formula
    By hschrunk in forum Access
    Replies: 3
    Last Post: 05-18-2010, 01:40 PM

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