Results 1 to 2 of 2
Thread: Aggregate Query (Access XP)
2002-05-09, 23:25 #1
- Join Date
- Jan 2001
- Serbia and Montenegro (Yugoslavia)
- Thanked 0 Times in 0 Posts
Aggregate Query (Access XP)
To crunch the data obtained through a benchmark test of a system we are about to implement I brought the data into Access to crunch the numbers before exporting it to Excel for a decent graph.
I have a table (tblData) holding the TermName, EventName, StartTime, and EndTime.
My goal for this step is to report on each event (EventName is repeated several times in the table per terminal) and the average time it took the event to complete with the number of Terminals running at the time (StartTime).
What I've done, and I don't think it is right, is set up an aggregate query with the following:
First column I have EventName with a Group By
Second column I have a domain aggregate with the following code:
ProcCount: CInt(DCount("[TermName]","tblProc","Start <= #" & tblData.[StartTime] & "# AND End >= #" & tblData.[StartTime] & "#"))
This also is a Group By. Further, tblProc referenced in the DCount is a table I generated that holds the TermID's and their start and end times (There are usually several start and end times per terminal because the batch program they were using kept failing and had to be restarted by a human).
The last is an attempt to grab the average time an event took per event and number of terminals:
Average Time: (DateDiff("s",[Event Start],[Event End]))
This last is set as an Avg.
I don't know how the Domain Aggregate's evaluate the records with the Group By, and fear I've really butchered this query and probably broke a lot of fundamental rules.
2002-05-10, 22:39 #2
- Join Date
- Dec 2001
- Melbourne, Australia
- Thanked 27 Times in 27 Posts
Re: Aggregate Query (Access XP)
Why don't you post the DB or query (DB preferably) and let people have a try at this.