dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5

Thread: SQL Access: Calculate the total elimination of records not the same month and year ?

  1. #1
    Join Date
    Sep 2007
    Posts
    315

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

    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/kq2mr2...n/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

  2. #2
    VictorN's Avatar
    VictorN is online now Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,399

    Re: SQL Access: Calculate the total elimination of records not the same month and yea

    Unfortunately your post is very hard to read/understand...
    Could you edit it adding code tags and proper indentations for the Access/SQL formulas?
    Victor Nijegorodov

  3. #3
    Join Date
    Sep 2007
    Posts
    315

    Re: SQL Access: Calculate the total elimination of records not the same month and yea

    I have not thought of a way to solve this problem to get the desired results so I need your help to understand the problem

  4. #4
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,002

    Re: SQL Access: Calculate the total elimination of records not the same month and yea

    Quote Originally Posted by dongtrien View Post
    I have not thought of a way to solve this problem to get the desired results so I need your help to understand the problem
    We need your help to.format the code so we can read it.

  5. #5
    Join Date
    Sep 2007
    Posts
    315

    Re: SQL Access: Calculate the total elimination of records not the same month and yea

    I asked how the following problem was:
    Code:
    SELECT     FIELD1, FIELD2, FIELD_EXPRESSION1, FIELD_EXPRESSION2
    FROM         TABLE
    WHERE     CONDITION
    Inside:
    FIELD1, FIELD2: this is field of table
    FIELD_EXPRESSION1, FIELD_EXPRESSION2: this is expression and conditional query

    Code:
    FIELD_EXPRESSION =
    
    SELECT *
    FROM TABLE
    WHERE EXISTS (
      SELECT NULL
      FROM TABLE AS TABLE_2
      WHERE (CONDITION)  
    );
    I don't mean how the problem of checking this to apply to my example query, do you understand me ? How to structure this problem ?
    Last edited by dongtrien; March 22nd, 2019 at 11:58 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
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)