Writing a Count(Distinct) Query in Access

re: Writing a Count(Distinct) Query in Access

robcooper — Mon, 24 Sep 2007 17:41:49 GMT

Frederick - the saved query might make this easier to debug and test. Interestingly enough, the subqueries used here appear in the design surface as a derived table, but the designer doesn't make the distinction between a saved query or derived table in this case. Grovelli - the SQL syntax used in Access 2007 is the same as in Access 2003. Rob

re: Writing a Count(Distinct) Query in Access

grovelli — Thu, 20 Sep 2007 13:50:38 GMT

Have there been improvements, updates, changes to the SQL Access uses, going from Access 2003 to Access 2007?

re: Writing a Count(Distinct) Query in Access

Frederick Grose — Thu, 20 Sep 2007 04:43:08 GMT

I find it easier to use a saved query, qryColorSums, within another query for this: qryColorSums = SELECT tblColors.Color, Sum(tblColors.Valu) AS Total

FROM tblColors

GROUP BY tblColors.Color; qryColorSumsCount = SELECT qryColorSums.Color, First(qryColorSums.[Total]) AS Total, Count(qryColorSums.Color) AS N

FROM qryColorSums

GROUP BY qryColorSums.Color; qryCount = SELECT Count(qryColorSums.Color) AS [Count]

FROM qryColorSums; All this is made easier to understand and remember by viewing in the graphic query designer (paste the SQL text into a query's SQL View box and then selecting the Design View).