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
318

## 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. ## 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?

3. Member
Join Date
Sep 2007
Posts
318

## 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. ## 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.

5. Member
Join Date
Sep 2007
Posts
318

## 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
•

Click Here to Expand Forum to Full Width

On-Demand Webinars (sponsored)