Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Classification by speed (AXP)

    Hello Loungers

    The application contains a table of speeds for a vehicle at various points in time. The task is to assign the speeds into various classes, with the class boundaries being defined as:

    atblSpeedClasses
    SpeedClassID - PK - Autonumber
    SpeedClassUpperLimit - long

    typical data in the speed class table
    1 0
    2 3
    3 10
    4 50
    5 100
    6 200

    The base table (GPSPos) includes these fields:
    RecNum - PK, autonumber
    MachineID - Long
    SpeedFromPrevious - double
    SpeedClassIDfk - Long - the speed class for this record (to be populated)

    Examples of desired results
    speed 0 > speedclassIDfk 1
    5 > 2
    20 > 3
    60 > 4

    I have used this SQL to find all the speed class values that are less than the speed for the record, but I am stumped how to find the associated ID associated with each speed class, and how to find the largest of the several candidates:

    SELECT GPSPos.RecNum, atblSpeedClass.SpeedClassUpperLimit FROM GPSPos, atblSpeedClass
    WHERE GPSPos.MachineID=54 AND atblSpeedClass.SpeedClassUpperLimit<[SpeedFromPrevious];


    I have tried various combinations of Sum queries to find the maximum speedClassID for any given speed, without success. Once I find the appropriate class, I would like to UPDATE the SpeedClassIDfk field with the correct value. Can somebody help, please? Thanks in advance.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Classification by speed (AXP)

    To return the speed class:

    SELECT GPSPos.RecNum, GPSPos.MachineID, GPSPos.SpeedFromPrevious, CLng(DMax("SpeedClassID","atblSpeedClasses","Speed ClassUpperLimit<=" & [SpeedFromPrevious])) AS SpeedClass
    FROM GPSPos;

    You don't really need a SpeedClass field in GPSPos, since it is a derived value, but it you need it for performance reasons, use this update query:

    UPDATE GPSPos SET GPSPos.SpeedClassIDfk = CLng(DMax("SpeedClassID","atblSpeedClasses","Speed ClassUpperLimit<=" & [SpeedFromPrevious]));

    Note: some browsers mess up the "less than or equal to". In both queries, the condition should have < = without a space in between.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Classification by speed (AXP)

    Thanks, Hans. Much appreciated.

    As you mentioned, perfomance is an issue in this situation. I tried the DMax method, and it is quite slow executing, and the update time seems to increase exponentially as the number of affected records increases. So I took a look at a different approach using VBA -- walk thru the atblSpeedClasses and build a WHERE clause for the GPSPos table. Then I can select all the records within a particular speed class and update them all to the same class value. For a sample with about 18000 records, the time difference was 100 seconds using the DMax function version versus about 3 seconds walking thru all the class records, and executing 8 separate UPDATE commands.

    Thanks again for your help.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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