Re: Query Help

[AJ.]

Re: Query Help

Hi everyone,

I need some help with the query. The query below returns all the activity time for each company ID.

This is my query:
SELECT tblCoActivities.ActivityTime, tblCoActivities.CompanyID
FROM tblCoActivities
WHERE (((tblCoActivities.StatusID)=1) AND ((tblCoActivities.ActivityDate)=#9/15/2005#))
ORDER BY tblCoActivities.ActivityTime;

result:
ActivityTime CompanyID
11:47 AM 1
4:08:00 PM 1
4:16:00 PM 1
11:47 AM 2
4:08:00 PM 2
4:16:00 PM 2
10:10:00 AM 3
3:20:00 PM 3
3:20:00 PM 4
8:10:00 AM 4
11:00:00 AM 18
10:01:00 AM 19

***I need it to return the earliest activitytime for each company only in recordset.
ex:
ActivityTime CompanyID
11:47 AM 1
11:47 AM 2
10:10:00 AM 3
8:10:00 AM 4
11:00:00 AM 18
10:01:00 AM 19


Thanks for your assistance.

 

[ghudson]

Check out the Access help files for the DMin() function.

 

[ssmithri]

Or, perhaps something like this?

SELECT tblCoActivities.CompanyID, Min(tblCoActivities.ActivityTime) as firstactivity FROM tblCoActivities
WHERE (((tblCoActivities.StatusID)=1) AND ((tblCoActivities.ActivityDate)=#9/15/2005#))
GROUP BY tblCoActivities.CompanyID ORDER BY firstactivity;