Results 1 to 8 of 8

Thread: Help with Query

  1. #1
    Dobbinsson
    Guest

    Help with Query

    Okay here is what I got a table with the following information:

    Engine Wuc Time Remaining
    E5191 27cla 1300
    E5191 27CLB 1600
    E5191 27CLC 1700
    E5191 27CLD 1000

    This data keeps going on for quite some time and the engine number keeps changes for about 150 engines. What I need to do is take each engine and find the lowest value for the WUC's that start with 27CL. Anyone have any suggestions for me? Thanks in advance for any help.

    Thanks Richard.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with Query

    This will give you a list of Engine, WUC and the lowest values by WUC for each engine.

    SELECT E.Engine, E.WUC, MIN(E.Time)
    FROM tblEngines AS E
    GROUP BY E.Engine, E.WUC
    HAVING E.WUC Like "27CL*"

    This assumes that the table is named tblEngines and that your fields are named Engine, WUC and Time. If your field names have spaces in them, you'll have to put square brackets around the field names; and you'll have to substitute the actual table and field names for the ones I used.
    Charlotte

  3. #3
    Dobbinsson
    Guest

    Re: Help with Query

    Thanks that gets me much closer to where I wanted to be but not quite there.

    Take my example for instance

    Engine Wuc Time Remaining
    E5191 27cla 1300
    E5191 27CLB 1600
    E5191 27CLC 1700
    E5191 27CLD 1000

    What I would like the query to return is

    E5191 27CLD 1000

    But in my main table many of the WUC fields have several times and it just returned the lowest one for each WUC and I need the lowest time remaining for the whole series 27CL.

    Thanks again for your help it is much appreciated.

    Richard

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Query

    Does an ascending sort on the Time Remaining field get you there?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with Query

    OK, I wasn't clear on the result you were trying for. What you want, then, is actually two pieces of information: The minimum value and the WUC associated with that value, right? You can do that by changing your query into a top values query like this:

    SELECT TOP 1 E.Engine, E.WUC, MIN(E.Time)
    FROM tblEngines AS E
    GROUP BY E.Engine, E.WUC
    HAVING E.WUC Like "27CL*"
    ORDER BY Min(E.Time);

    Since you're ordering the values on the minimum time, ascending, the top 1 record will be the minimum time for that grouping and the associated WUC will be the right one for that time.
    Charlotte

  6. #6
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Query

    Hi Charlotte - is there any way to write the query by 'clicking' this up in Access, or must the statement be written in SQL?

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with Query

    No, you can build it in the query grid. Just click on the sigma in the toolbar to turn on the totals line, drag the fields down onto the query grid from the table, change GroupBy to Min under the appropriate field, put the Like expression in the criteria line under the field you're grouping by and then right click the gray area where the table is to bring up the query properties. Set the Top Values property to 1 and run the query. If you look at the SQL view, you'll see the same query I posted.
    Charlotte

  8. #8
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Query

    Thanks - I tried that, it worked.

    Then, i decided to try to select the MAX value (to check my minimal knowledge). I changed min() to max()in the grid, but the query didnt 'work' because the Sort has to be DESC when max is employed, and neither the query design view, nor the property view, changed the SQL statement.

    Now i know a little more about what to look for - thanks again.

Posting Permissions

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