Calculating st.dev of a data set that skips dates

[redpringles]

Location	Date	Value
Texas	1/1/2014	6
Texas	1/4/2014	12
Texas	1/5/2014	6

Hello,

I have the above data set as a table in Access 2010. When I currently calculate stdev(Value) for Texas, it literally returns the standard deviation of the three values. (6, 12, and 6) However, in reality, this data set indicates that Value was zero on 1/2/2014 and 1/3/2014. How can I form a query that takes these "empty" or "non-existent-as-zero" entries into consideration when calculating the standard deviation? How can I make the query recognize the gap from the data set above, and calculate standard deviation from five values? (6, 0, 0, 12, 6)

Currently, my query is in the form of:

SELECT Distinct Location, stdev(Value)
FROM Table1

Thank you for your help in advance!

[June7]

You would need a dataset of all possible date/location pairs. Join your data table to that all-dates/locations dataset. Compound link on the location and date, join type "Include all records from All_Dates_Locations ...")

Then try: StDev(Nz([Value],0))

[redpringles]

Thank you for your suggestion! Would you be able to describe "Compound link on the location and date, join type "Include all records from All_Dates_Locations ...")" in terms of SQL language in a query?

Also, any way of solving this without creating a table (of all possible date/location pairs) manually and joining it to the existing data, perhaps within a query, using functions and logic?

[June7]

I don't know any way to accomplish this without a table of dates. I presume you already have a table of locations. A date/location dataset can be a query that includes both of those tables. Don't use join clause and the result will be a Cartesian relationship. Every record of each table will join to every record of the other table and generate every possible date/location pair.

Use the date/location dataset in another query that joins to your data table, as already described. Use the query builder to help construct.