how to put 3 text boxes on the form that...

[Song Su]

I have a form based on following query

SELECT qryData.SID, DateDiff("n",[timein],[timeout]) AS Minutes
FROM Duration, qryData
WHERE (((qryData.timeIn)>=[from]) AND ((qryData.timeOut)<=[to]+1));\

On the form, I want only 3 text boxes:
1. Uniq SID count
2. total recound count
3. total hours

Thanks

 

[Michel Walsh]

With a crosstab:


TRANSFORM Count(*) AS c
SELECT SUM(minutes) As TotalMinutes,
COUNT(*) AS NumberOfRecords,
COUNT(c) AS NumberOfDistinctSID
FROM qry
GROUP BY 1
PIVOT sid IN (null);


Note you also get a fourth field, <>, which is to be disregarded.



With a standard query, because Jet does not have COUNT DISTINCT, we have to
use a sub query:


SELECT SUM(theMinutes) As TotalMinutes,
COUNT(*) AS CountDistinct,
SUM(theCount) AS numberOfRecords
FROM (SELECT COUNT(*) AS theCount, SUM(minutes) AS theMinutes
FROM qry
GROUP BY sid ) AS a




How the crosstab works is quite interesting and was initially exposed by
Steve Dassin. Most of the game comes from COUNT(*) AS c, the value that
goes in each "cell" of the query result. Remember that if a group does not
have any data under a column, that "cell" get the value NULL. So, when we
further aggregate, horizontally, per group, with
SELECT ..., COUNT(c)
that does count the number of column, for a group, where there is data in
each cell, for a given row and so, indeed, it creates a COUNT DISTINCT.

Since we do not want see each possible generated columns, we use

PIVOT ... IN(null)


Hoping it may help,
Vanderghast, Access MVP