 CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com

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

1. Member Join Date
Sep 2007
Posts
340

## 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  Reply With Quote

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

Could you edit it adding code tags and proper indentations for the Access/SQL formulas?  Reply With Quote

3. Member Join Date
Sep 2007
Posts
340

## 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  Reply With Quote

4. ## Re: SQL Access: Calculate the total elimination of records not the same month and yea Originally Posted by dongtrien 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.  Reply With Quote

5. Member Join Date
Sep 2007
Posts
340

## 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.  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
• 