Need Max(Field1) WITH Field2, Field3, Field4 1

[madelca100]

I've read dozens of posts in several Access forums but can't find an answer to this one. (Probably not using the correct descriptors.)

Please read this entire post before responding.

In another life I could write something like this:
Select Max(Field1) WITH Field2, Field3, Field4
group by field5;

This would give me all Field5's maximum Field1 and the other fields from the same record (row).


For example

Field1 Field2 Field3 Field4 Field5
10 20 30 40 mike
20 10 15 20 mike
30 5 20 10 mike
10 60 10 30 fred
20 10 15 20 fred
40 6 5 10 fred


What I'm looking for is:
mike 30 5 20 10
(since 30 is the max of field1 for mike take the other values from the same row even though they are not 'max')
fred 40 6 5 10
(since 40 is the max of field1 for fred)
etc.

Select max(field1) max(field2) etc. will give me the maximum value for each field per person. Not what I want.
mike 30 20 30 40
fred 40 16 15 30

I'm doing this in design query not straight sql (not sure exactly what this is called).

Can this be done in a single pass?
I know I can do this in two steps.
1)make table query with the max(field1) for each name (and key).
2)use that table to fetch the other values.

Apologies if an answer has already been posted. (I did look.) Please point me to it.

mike

 

[PHV]

SELECT A.Field5, A.Field1, A.Field2, A.Field3, A.Field4
FROM yourTable AS A INNER JOIN (
SELECT Field5, Max(Field1) As MaxField1 FROM yourTable GROUP BY Field5
) AS B ON A.Field5 = B.Field5 AND A.Field1 = B.MaxField1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Select fld1, fld2, fld3, fld4, fld5

From tbl As T

Where fld1 = (Select MAX(fld1) From tbl Where tbl.fld5 = T.fld5)

Which may give you more than one record if more than one record has the MAX value for fld1.

 

[madelca100]

Thanks Golum and PHV,

Don't mean to sound ungrateful but these are sql answers. As stated in my question I am doing this in design view "not straight sql".

I'm not sure how to implement this in design view.

Am I in the wrong forum? If so which one would be more appropriate?

Any other ideas?

thanks,
mike

 

[PHV]

If you absolutely don't want to use the SQL view pane of the query design window then:
1)SELECT query with the max(field1) for each name
2)use that QUERY joined with your table on name and field1 to fetch the other values

I personally avoid to use the query grid as it
1) is buggy (it don't like the inline views)
2) don't handle the theta joins (ON clause with inequality)
3) don't handle UNION
4) make the SQL code unreadable (in my opinion)
5) ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

open a new query, go to SQL view, paste one of the above solutions, then switch over to Design view

that way, you can analyze or "deconstruct" how the query is represented in Design view and hopefully be able to do the same in a similar situation in future

although i gotta tell ya, sometimes it's a lot easier just to write the sql, like Golom's, it's about as easy as it gets, eh



r937.com | rudy.ca

 

[PHV]

Hi rudy.
In your opinion, what is the more efficient way between the correlated subquery (Golom's solution) or the join with an aggregate query ?
PH.

 

[madelca100]

Thanks PVH,

The 'query' joined to a 'table' worked very well.



Now to address the other comments:

I would prefer straight sql. This shop frowns on using sql because no one else here understands it.

My co-workers can maintain the database if the queries are written in design view. If I write sql then I have to maintain it for the rest of my life (or at least my career here).

What is important to realize is that providing help that you feel is easier doesn't necessarily serve the person with the problem.


regards and thanks again,
I really do love this forum
mike

 

[r937]

mike, i hate to break it to you, but underneath each query in Design view, is a query written in SQL

"the shop frowns on sql" makes no sense -- the access jet engine executes only SQL

write it in SQL View, switch to Design View, save the query, exit access, re-open the mdb, and open the query -- voila, it shows you Design View as the default!

your co-workers will never know you wrote it in SQL!!!

r937.com | rudy.ca

 

[r937]

PHV, i don't know which has better performance, the join to a derived aggregate table or a correlated subquery, but my guess is, they are probably equivalent (especially in access)

what really matters is how easy the query is to understand if you've never seen it before, and on that basis, the correlated subquery is shorter, simpler, and -- to me, anyway -- a lot cleaner

see also

http://r937.com/multiple_relationships.html

r937.com | rudy.ca

 

[endoflux]

Gentlemen--

I wrote thread thread701-1150079 before I saw this, so the details there may help clarify my issue a bit if you care to help; keep in mind I haven't touched SQL in years...

I've got a similar problem, but with only a total of 4 fields, and I'll need to limit data based on 2 fields instead of 1. We'll call these fields fld1, fld2, fld51, and fld52 to match up with Golom's solution-- though his won't work, given that I need to only return 1 record even if more than 1 record contains the MIN.

To make it more complicated, my Fld2 comes from a different table than the others-- Fld2 (DEFECTNUM) comes from a table(DEFECTS) that is Left-joined to a second table (DEFECTEVTS) where Fld1 (DATEEVENT), Fld51 (RSLTSTATE), and Fld52 (DEFEVTID) reside.

I need to select the MIN(DATEEVENT) and its RSLTSTATE of records where RSLTSTATE = 8, 10, 11, 13, or 14, or DEFEVTID = 15, returning only 1 record per DEFECTNUM.

Not sure if this is helpful, but simplified:

DEFECTNUM, "first DATEEVENT where RSLTSTATE =8,10,11,13,14 OR DEFEVTID =15", RSLTSTATE

Help? Thanks!

 

[endoflux]

Making progress...i think...but problem in all the joining, maybe?

SELECT DEFECTS.DEFECTNUM, DEFECTEVTS.RSLTSTATE, DEFECTEVTS.DATEEVENT, DEFECTEVTS.EVTDEFID
FROM DEFECTS LEFT JOIN DEFECTEVTS ON DEFECTS.IDRECORD = DEFECTEVTS.PARENTID AND DEFECTEVTS LEFT JOIN ( (SELECT MIN(DATEEVENT) As MINDATE1 FROM DEFECTEVTS WHERE ( RSLTSTATE IN (8, 10, 11, 13, 14) OR EVTDEFID = 15) GROUP BY DEFECTS.DEFECTNUM)) AS MODDED
ON (DEFECTEVTS.RSLTSTATE = MODDED.RSLTSTATE OR DEFECTEVTS.EVTDEFID = MODDED.EVTDEFID) AND DEFECTEVTS.DATEEVENT = MODDED.MINDATE1;

 

[MichaelRed]

basMaxVal see thread701-585979 for one of the many postings including the procedure.



MichaelRed

 

[endoflux]

Thanks for the help, but I think I explained it poorly, cause that's greek compared to the ballpark I'm working in...

I don't need to know the minimum of different fields; it's a single field (DateEvent)I need the minimum for: there may be 20 line items describing events for a Defect, along with dates of when they happened. Maybe 4 of them will be describing the closing of the Defect, which can occur over a month or so. I need to show that earliest of those 4 (or so) events as the "Closed Date".

I've got the record selection working, it's currently returning a line item (repeating the defect #) every time there's an included event result-- I need one line item per DEFECTNUM, showing the minimum date of the set of records having RSLTSTATE = 8, 10, 11, 13, 14 or EVTDEFID = 15.

That said, the code above doesn't like how I'm joining the normal tables to the subquery...but maybe I'm just lost?

Thanks again for all your consideration...any input is very much appreciated!

 

[MichaelRed]

If your code selects / presents the correct recordset. just add the "Top 1" clause and you get the single record. Formally, there is also an "ASC" clause, but that is the default, so just adding the "Order By" Ascending for the field does the same.




MichaelRed