Member Avatar for dongtrien
dongtrien 13 Newbie Poster

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 

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

  • 1 Contributor
  • 0 Replies
  • 358 Views

Dani AI

Generated

For : the usual cause of “only one record removed” is an uncorrelated or hard‑coded subquery (or one that returns multiple rows because of a GROUP BY). The reliable pattern is: first identify invoice IDs (HD) that have any line with a different month/year, then exclude those HDs when summing. That way an invoice with even one out‑of‑month line is dropped entirely.

Example (SQL Server style; replace @m and @y with the target month/year):

DECLARE @m INT = 1, @y INT = 2019;

SELECT SUM(
  CASE WHEN d.DONGIA IS NOT NULL
       THEN d.SOLUONG*(d.DONGIA/1000000)
       ELSE d.SOLUONG*(d.DONGIATT/1000000)
  END) AS TotalRevenue
FROM HDMUABAN d
WHERE d.LOAIHD = 'FBAN'
  AND NOT EXISTS (
    SELECT 1 FROM HDMUABAN d2
    WHERE d2.HD = d.HD
      AND (MONTH(d2.NGAYGIAO) <> @m OR YEAR(d2.NGAYGIAO) <> @y)
  );

Notes and alternatives: if the requirement is only to sum lines whose own date is in the month/year (not to drop the whole invoice), use a simple WHERE Month(NGAYGIAO)=m AND Year(NGAYGIAO)=y instead. In Access the same logic works; use a correlated NOT EXISTS or build a small saved query that returns “valid” HDs (grouped by HD with min/max month/year checks) and join it to the detail table. Prefer NOT EXISTS to NOT IN to avoid NULL issues and for better performance on indexed HD.

Troubleshooting tips: verify NGAYGIAO has no unexpected time component or NULLs, trim HD values for exact matches, test the exclusion subquery alone to list excluded HDs, and add indexes on HD and NGAYGIAO if the table is large.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.