MS Access中如何在含GROUP BY与HAVING子句的汇总查询中添加时间范围筛选的WHERE子句

Got it, let's fix your query step by step. The key thing here is remembering that WHERE clauses filter rows before grouping, while HAVING filters after grouping. Since you want to narrow down records by time, date, and icode before calculating sums, we'll add all those conditions in the WHERE clause (and move your icode filter from HAVING to WHERE for better performance).

Here's the updated query that meets all your requirements:

SELECT icode, SUM(soldQty) AS totalSold, SUM(rackQty) AS totalRack
FROM sales
WHERE 
    -- Filter for your target icodes
    icode IN (14000, 15000, 16000)
    -- Add your custom date range (adjust these values as needed)
    AND dt >= #03/17/22# AND dt <= #03/19/22#
    -- Filter for daily time window (10:00 AM to 5:00 PM)
    AND TimeValue(dt) >= #10:00:00# AND TimeValue(dt) <= #17:00:00#
GROUP BY icode
ORDER BY icode;

Breakdown of the changes: #

• WHERE placement: We put all row-level filters right after FROM sales and before GROUP BY. This reduces the number of rows processed during grouping, making the query faster and more efficient.
• Time filter: TimeValue(dt) extracts just the time portion of your dt datetime field, letting us compare it directly to the 10:00-17:00 window using Access's date/time literal syntax (#...#).
• Date range: The dt >= #start_date# AND dt <= #end_date# line lets you define any custom interval—swap out the dates for weekly ranges (e.g., dt >= DateAdd("ww", -1, Date()) AND dt < Date() for last week) or other periods as needed.
• Icode filter: Using IN (14000,15000,16000) is more readable than BETWEEN here (though both work), and moving it to WHERE instead of HAVING avoids grouping rows you don't care about in the first place.

When you run this query against your sample data, it will return a summary matching your expected format (note: the 16000 soldQty in your expected output shows 4, which appears to be a typo in your sample data—only one 16000 record falls within the 10-17 window, so the query returns 3 for that code, which aligns with the provided data).

内容的提问来源于stack exchange，提问作者John