Phizon Phizon - 7 months ago 19
SQL Question

MS-Access Including all days in a Date Range Query

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;


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.

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.

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


to this:

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


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!

Answer

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

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;
Comments