Please help

[kwill]

Here's a sample of the data I have

Name Date Dispatch # Hours
John 9/15/01 453453 2
John 9/15/01 453542 1
John 9/15/01 453543 1
John 9/16/01 453422 4
John 9/17/01 453645 3
John 9/19/01 453594 3
Mike 9/15/01 564321 7
Steve 9/17/01 876544 6
Steve 9/19/01 987876 3
Steve 9/20/01 765432 2

Question:
I need to sum in a form How many days a particular tech (John)worked (4 days) and how many hours a day he worked (3.5). The key is how do I add the number of unique dates?????? thanks for taking a look.

 

[BrooksR]

There are a number of ways to do this. One way would be to use domain functions in the Control Source property of the textboxes.

You could use =DCount(...) for the days and =DAverage(...) for the hours. Search in the help system under DCount, DAverage and Domain functions for information and examples.

Brooks

 

[tlbroadbent]

Here is a query that does what you want. It utilizes a sub-query to summarize before the outer query calculates the count and average.

SELECT
TotQry.EmpName,
Count(TotQry.WorkDate) AS DaysWorked,
Avg(TotQry.TotHrs) AS AvgHrs
FROM
[SELECT
HoursWorked.EmpName,
HoursWorked.WorkDate,
Sum(HoursWorked.Hrs) AS TotHrs
FROM HoursWorked
GROUP BY
HoursWorked.EmpName,
HoursWorked.WorkDate]. AS TotQry
GROUP BY
TotQry.EmpName;

---------------------------
You may want to save the inner (sub-query) query and then use that query as the source for the outer query. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[BrooksR]

The only problem with the query is that if you use it for the Recordsource of the form, you will no longer see the original detail of the records in the form.

You could create a sub-form within the form and use Terry's query there. Then, you could see the original detail plus the totals.

IMHO, the domain functions might be easier if you want to see the detail too.

Brooks

 

[kwill]

DCOUNT Worked!! Thanks Guys!!!