none
SQL Access: Calculate the total elimination of records not the same month and year ? RRS feed

  • Question

  • I want to calculate the total conditional in the month but remove the records that are not the same month and not the same year, the condition that eliminates the view on the sql access because the result is true or false in the excel file http://www.mediafire.com/file/kq2mr2v6px1uxan/exercise.rar 

    DOANHTHU2: Sum(IIf(Not IsNull([DONGIA]),[SOLUONG]*([DONGIA]/1000000),[SOLUONG]*([DONGIATT]/1000000)))
    
    
    DOANHTHU2: Sum(IIf(Not IsNull([DONGIA]) AND (SELECT COUNT(*) AS DEM FROM HDMUABAN WHERE (((HDMUABAN.HD)='M1101') AND ((HDMUABAN.LOAIHD)='FBAN') AND ((Month([NGAYGIAO]))=1) AND ((Year([NGAYGIAO]))=2019)) GROUP BY HDMUABAN.HD, HDMUABAN.NGAYGIAO, HDMUABAN.LOAIHD) AS NUM <=0,[SOLUONG]*([DONGIA]/1000000),[SOLUONG]*([DONGIATT]/1000000)))
    TONGDOANHTHU2: IIf(Not IsNull([DOANHTHU2]) And Not IsNull([BSLN]),[DOANHTHU2]+[BSLN],IIf(IsNull([DOANHTHU2]) And Not IsNull([BSLN]),[BSLN],[DOANHTHU2]))
    My example only removed one record, I designed the problem of removing many records if the same conditions exist



    • Edited by lamtriendong Thursday, February 14, 2019 4:21 AM
    Thursday, February 14, 2019 4:12 AM

All replies