selecting higest of multiple values 1

[kmcculler]

Hello, I'm having some trouble figuring out how to do the following in an access query. I'm working on a sales prospect type db and I have various companies listed in a querey with entries for each sales stage(We need to keep info on previous stages to track when potenital clients were lost). So, the current query produces data something like this:

Name Stage
--------------------------------
Company A Stage 1
Company A Stage 2
Company B Stage 1
Company C Stage 1
Company C Stage 2
Company C Lost

Ok, now what I want to do (for the purpose of a report) is to only select the higest stage that each company is in so the new query would produce:

Name Stage
----------------------------------
Company A Stage 2
Company B Stage 1
Company C Lost

Anybody know how I can get Access to display this?

Kris McCuller

 

[PHV]

How are the stage ordered ?
IE how do you know tha Stage 2 is the last for Company A ?
No date field ?

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

 

[jrbarnett]

Use a GROUP BY query with the Max aggregate function. The actual syntax will vary depending upon your table layout, but if all entries are in one table, the SQL code will look something like:

SELECT Companyname, Max (Stage)
FROM SalesProspects
GROUP BY CompanyName
ORDER BY CompanyName

John

 

[kmcculler]

My fault, wasn't specific enough. The stage are a grouping of notes that are indeed ordered (inside the group) by a date, however there can be multiple entries per stage (something I forgot to include in my example) and I want all of those entries so a corrected example looks more like this:
Name Stage Date Notes
----------------------------------------------------
Company A Stage 1 12/1/04 Did this
Company A Stage 2 12/31/04 Did that
Company A Stage 2 1/5/05 Did the other
Company B Stage 1 1/6/05 Did it again
Company C Stage 1 12/31/04 Something
Company C Stage 2 1/3/05 Something else
Company C Stage 2 1/3/05 Got a widgit
Company C Stage 2 1/15/05 Showed the widget
Company C Lost 1/21/05 Came back home

and the final product:
Name Stage Date Notes
----------------------------------------------------
Company A Stage 2 12/31/04 Did that
Company A Stage 2 1/5/05 Did the other
Company B Stage 1 1/6/05 Did it again
Company C Lost 1/21/05 Came back home

jrbarnett's example would work if it weren't for the additional fields that I didn't include in my original post. Sorry bout that!


Kris McCuller

 

[jrbarnett]

A slight modification on my original should fix the problem:

SELECT Companyname, Stage, Max(Date), Notes
FROM SalesProspects
GROUP BY CompanyName, Stage, Notes
ORDER BY CompanyName, Stage

John

 

[kmcculler]

That didn't seem to do anything to the original output but change the ordering. The date is there only for sorting purposes, what Im after is to get all the records in the highest sales stage that the company has so far reached. (1,2,3,4,or Lost).

Kris McCuller

 

[PHV]

Create a saved query named, say qryGetLastStage:
SELECT A.Name, A.Stage
FROM tblStages AS A
WHERE A.Date=(SELECT Max([Date]) FROM tblStages M WHERE M.Name=A.Name);

Then your query:
SELECT tblStages.*
FROM tblStages INNER JOIN qryGetLastStage ON (tblStages.Stage = qryGetLastStage.Stage) AND (tblStages.Name = qryGetLastStage.Name);

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

 

[kmcculler]

Thanks.. That seems to have gotten the job done!

Kris McCuller