MS-Access在日期范围查询中包括所有天数 [英] MS-Access Including all days in a Date Range Query

问题描述

在此处访问2010.

Access 2010 here.

返回另一个益智游戏.我有这个查询:

Back with another puzzler. I have this query:

SELECT DischargeDatabase.Date, Avg([pH]) AS [pH Value], Avg([Temperature]) AS [Temperature (°C)], Avg([ZincLevel]) AS [Zinc (mg/l)], Sum([Effluent]) AS [Discharge (gal)], Count(*) AS [# Discharges]
FROM DischargeDatabase
WHERE DischargeDatabase.Date Between Forms!QueryForm!TextCriteriaQ0A And Forms!QueryForm!TextCriteriaQ0B
GROUP BY DischargeDatabase.Date;

来自我一直在建立的废水处理数据库.这提供了废水排放的每日摘要，平均了pH，温度和锌含量，并对排放量(废水)求和.用户在带有日期选择器的"QueryForm"上的两个文本框中选择一个范围，然后运行查询.

from a waste water treatment database that I've been building. This gives a by-day summary of waste water discharges, averaging the pH, Temperature, and zinc levels, and summing the discharge volume (effluent). The user selects a range in two text boxes on the "QueryForm" with date pickers, and runs the query.

显示的是按日期分组的排放量，用于日期范围，并且仅列出有排放量的天数.用户要求的是每天在选定要显示的范围内的每天，而在"DischargeDatabase"中没有记录的那几天，字段值仅为零.

What is shown is discharges, grouped by day, for the date range, and only days that had discharges are listed. What a user has requested is for every day in the range selected to be shown, and those days without records in the "DischargeDatabase" just have zeros for the field values.

即从此(一个周末的日期范围从4/11/2013到2013/4/16):

i.e. from this (date range 4/11/2013 to 4/16/2013, over a weekend):

Date      | ph Value | Temperature (°C) | Zinc (mg/l) | Discharge (gal) | # Discharges
4/11/2013   9.5        18.6               0.89          5000              5
4/12/2013   9.1        17.9               1.68          3000              2
4/15/2013   8.9        19.6               1.47          10000             7
4/16/2013   9.6        18.2               0.35          1500              1

对此:

Date      | ph Value | Temperature (°C) | Zinc (mg/l) | Discharge (gal) | # Discharges
4/11/2013   9.5        18.6               0.89          5000              5
4/12/2013   9.1        17.9               1.68          3000              2
4/13/2013   0.0         0.0               0.0           0                 0
4/14/2013   0.0         0.0               0.0           0                 0
4/15/2013   8.9        19.6               1.47          10000             7
4/16/2013   9.6        18.2               0.35          1500              1

仅此而已，因此用户可以毫无疑问地将查询粘贴到excel电子表格中.我什至不确定这是可行的，还是在查询范围内(您正在选择"不存在的记录).可以用某种假表/查询预先填充零来进行某种联接吗?

This is all so that the user can paste the query into an excel spreadsheet without issue. I'm not even sure that this is possible, or within the scope of a query (you are "selecting" records that don't exist). What might work is some sort of join with a bogus table/query pre-filled with zeros?

感谢您的帮助和任何想法！

Thank you for the help and any ideas!

推荐答案

使用日历表可以非常容易.您可以使用自定义CreateTable_calendar和LoadCalendar过程来构建自己的过程.

This could be fairly easy with a calendar table. You can build your own using custom CreateTable_calendar and LoadCalendar procedures.

创建一个查询，该查询根据日期范围过滤日历表，然后将其LEFT JOIN过滤到另一个表中. (在此示例中，我简化了SELECT字段列表.)

Create a query which filters the calendar table based the the date range and LEFT JOIN it to your other table. (I simplified the SELECT field list in this example.)

SELECT
    c.the_date,
    Count(ddb.Date) AS [# Discharges]
FROM
    tblCalendar AS c
    LEFT JOIN DischargeDatabase AS ddb
    ON c.the_date = ddb.Date
WHERE
    c.the_date Between
            Forms!QueryForm!TextCriteriaQ0A
        And Forms!QueryForm!TextCriteriaQ0B
GROUP BY c.the_date;

这篇关于MS-Access在日期范围查询中包括所有天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！