If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how to put 3 text boxes on the form that...
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 |
#2
|
|||
|
|||
how to put 3 text boxes on the form that...
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 "Song Su" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|