Help with Query

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote