Member Avatar for dongtrien
dongtrien 13 Newbie Poster

In Access I want to group and assign data according to conditions with 2 values 0 and 1 (true or false) and with the condition NGAYGIAO>= #01/01/2019# and NGAYGIAO <=# 31/01/2019# me How to write commands ? The data I put in the excel file consists of two sheets: Original data and Result, where Original data is the original data sheet that is the result of the problem I need, when running the access issue, ask you to help me with the problem.

  • 1 Contributor
  • 0 Replies
  • 316 Views

Dani AI

Generated

Short answer: create a SELECT query that (A) filters NGAYGIAO to your date window and (B) adds a computed 0/1 field with IIf(...) (or Switch for multiple rules). Then either save that query (for per-row results) or build an aggregate query that SUMs the 0/1 values to get counts per group.

Example (per-row, safe with dates and times):

SELECT *,
  IIf([YourConditionField]=1, 1, 0) AS Assigned
FROM YourTable
WHERE [NGAYGIAO] >= DateSerial(2019,1,1)
  AND [NGAYGIAO] <  DateSerial(2019,2,1);

Notes: use DateSerial to avoid mm/dd ambiguity and use an exclusive upper bound (< DateSerial(2019,2,1)) so rows on 2019-01-31 with non-midnight times are included.

Example (aggregate counts by a GroupField):

SELECT [GroupField],
  SUM(IIf([YourConditionField]=1,1,0)) AS TrueCount,
  SUM(IIf([YourConditionField]=0,1,0)) AS FalseCount,
  COUNT(*) AS Total
FROM YourTable
WHERE [NGAYGIAO] >= DateSerial(2019,1,1)
  AND [NGAYGIAO] <  DateSerial(2019,2,1)
GROUP BY [GroupField];

If NGAYGIAO is stored as text use CDate([NGAYGIAO]) or DateValue([NGAYGIAO]) in the WHERE and IIf expressions. To persist the 0/1 into the table use an UPDATE (backup first):

UPDATE YourTable
SET Assigned = IIf([YourConditionField]=1,1,0)
WHERE [NGAYGIAO] >= DateSerial(2019,1,1) AND [NGAYGIAO] < DateSerial(2019,2,1);

Troubleshooting: if you get no rows check the NGAYGIAO data type and format, and whether a time component exists. For multiple rules prefer Switch(...) or nested IIf. If you want the Excel "Result" layout, save the first query and export it or use a Make-Table query.

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.