Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #10953

Re: Trouble with Aggregate Query

Show all headers | View raw

On 22/07/2015 23:28:36,  wrote:
> Hi,
> The essence of my problem is that I have a table with many rows. Each pers
> on has 1, 2 or 3 rows in the tale. I want to select the most recent row fo
> r each person. Here's the table:
> 
> PK	EmpID   ColA	ColB	ColC
> 1	1	1	no	blk
> 2	1	2	no	blk
> 3	1	3	yes	wht *
> 4	2	1	yes	wht *
> 5	3	1	no	blk
> 6	3	2	no	blk
> 7	3	5	yes	wht *
> 8	3	4	no	blk
> 9	3	3	no	blk
> 
> 
> Ultimately, I want to select out the three rows marked with a *, which
> have the highest (most recent) value in ColA. So I want to do something
> like:
> 
> Select EmpID, ColA, ColB, ColC
> From tblTestMaxQuery
> Where PK in (Select PK, EmpID, Max(ColA) AS MaxOfColA
> FROM tblTestMaxQuery
> GROUP BY EmpID)  ;
> 
> 
> But I cannot get the inside query to work.  This works:
> 
> SELECT tblTestMaxQuery.EmpID, Max(tblTestMaxQuery.ColA) AS MaxOfColA
> FROM tblTestMaxQuery
> GROUP BY tblTestMaxQuery.EmpID;
> 
> and gives this output:
> 
> EmpID	ColA
> 1	3
> 2	1
> 3	5
> 
> but I cannot seem to get the PK field out. If I add it to the query, I hav
> e to add it to the Group By clause which produces the wrong answer. The in
> ner query needs to produce a single column with these values that would
> fee d the IN clause of the outer query:
> 
> PK
> 3
> 4
> 7
> 
> I'm stuck on the inner query. I need to group on the EmpID so we get a val
> ue for each EMPID where the ColA value is the Max for that EmpID.
> 
> Any help gratefully accepted. This may be just age getting to me. I'm sur
> e I used to do these OK.
> 
> Thanks,
> 
> Michael
> 
> 
Your second query is fine and gives the correct output.
Now create a query with your table and the second query,
and join the 2 EmpIDs and the ColA in the table to the MaxOfColA from the
query. Show all the fields from your table

Phil

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Trouble with Aggregate Query mflynn@scu.edu - 2015-07-22 15:28 -0700
  Re: Trouble with Aggregate Query "Phil" <phil@stantonfamily.co.uk> - 2015-07-23 09:06 +0100
  Re: Trouble with Aggregate Query mflynn@scu.edu - 2015-07-23 09:25 -0700

csiph-web