none
Calculating Source Information RRS feed

  • Question

  • I have a table (Customers) with a field that holds 'Source' information.  I am trying to count the number of occurances of this field within a user defined time frame.  I have put together the following:

    This gives us the whole table grouped by Source; Counting the occurances; and summing the contract price....
    SELECT Customers.Source, Count(Customers.CustDate) AS CountCustDate, Sum(Customers.ContractPrice) AS SumContractPrice
    FROM Customers
    GROUP BY Customers.Source
    ORDER BY Customers.Source;

    I have put this together to allow for grabbing the information within a user defined date range:
    SELECT DCount("Customers.Source" = "Call/Walk In","Customers","Source Between #" & [Enter Start Date] & "# And #" & [Enter End Date] & "#") AS CallWalkIn;

    ...however, this returns a "0", when it should be "5".

    ...this will also be used to count for several other values.

    As you can see, I will also be wanting to sum the 'contractprice' values.

    I tried this too, but it did not work....

    SELECT Customers.Source, Count(Customers.CustDate) AS CountOfCustDate, Sum(Customers.ContractPrice) AS SumOfContractPrice
    FROM Customers
    WHERE "Customers.CustDate Between #" & [StartDate] &  "# And #" & [EndDate] & "#"
    GROUP BY Customers.Source
    ORDER BY Customers.Source;

    I know I am close, but the equation to equal the captured field values is keeping me at bay....thoughts?


    Thank you, Mayhem65

    Wednesday, March 7, 2018 8:54 PM

All replies

  • Hi,

    If you're planning to display this on a form, I would probably suggest to avoid using a parameter query.

    So, if your first query returns all the records from the table, then your DCount() or DSum() expression can calculated the totals within a given date range without any problems.

    For example, to count the Source from your original query, you could try something like:

    =DCount("*", "QueryName", "Source='Call/Walk In' AND CustDate Between #" & [StartDate] & "# And #" & [EndDate] & "#")

    Hope it helps...

    Wednesday, March 7, 2018 9:01 PM
  • Try this:

    PARAMETERS [StartDate] DATETIME, [EndDate]  DATETIME;
    SELECT Source,
        COUNT(Customers.CustDate) AS CountOfCustDate,
        SUM(Customers.ContractPrice) AS SumOfContractPrice
    FROM Customers
    WHERE CustDate BETWEEN [StartDate]  AND [EndDate]
    GROUP BY Source
    ORDER BYSource;

    Not that when you specify a column as the operand of the COUNT operator, this will return the number of rows with a value at the column position.  If the column position cannot be NULL, or you want to include NULLs in the count use COUNT(*) more efficiently.

    Another thing to note when restricting a query by a date range with a BETWEEN....AND operation is that any rows with a non-zero time element in a date/time value on the final day of the range will be ignored.  This is because there is no such thing as a 'date value' per se in Access's implementation of the date/time data type.  When you enter a date in a column, the value really represents the point of time at midnight at the start of the day.  Unless you have specifically disallowed date/time values with a non-zero time element in the table design (by a validation rule), you cannot completely ignore the possibility of such date/time values being present.  The safe way of specifying the range therefore, is:

    WHERE CustDate >= [StartDate]  AND CustDate < [EndDate]+1

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Friday, March 9, 2018 1:33 PM Typo correcteed.
    Friday, March 9, 2018 1:32 PM
  • Hello Mayhem65,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer or provide your solution and mark as answer to close this thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 12, 2018 5:48 AM