Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    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.

  2. #2
    Platinum Lounger
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts