Re: Grouped MAX Records?
From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 11/15/04
- Next message: John Ortt: "Re: I need help w Best Practice for a single entry query..."
- Previous message: Robin: "Re: Changing data under the column heading in a crosstab query"
- In reply to: Mark Stephenson: "Re: Grouped MAX Records?"
- Next in thread: Mark Stephenson: "Re: Grouped MAX Records?"
- Reply: Mark Stephenson: "Re: Grouped MAX Records?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 15 Nov 2004 21:27:15 +0800
Try something like this:
SELECT CFLIVE_O_RELATIONSHIPS.*
FROM CFLIVE_O_RELATIONSHIPS
WHERE CFLIVE_O_RELATIONSHIPS.REL_ID =
( SELECT TOP 1 Dupe.REL_ID AS FirstOfRelID
FROM CFLIVE_O_RELATIONSHIPS AS Dupe
WHERE (Dupe.REL_SOURCE_PER_GRO_ID =
CFLIVE_O_RELATIONSHIPS.REL_SOURCE_PER_GRO_ID)
AND (Dupe.REL_RTY_CODE = "SCP")
ORDER BY Dupe.REL_START_DATE DESC, Dupe.REL_ID DESC );
The subquery returns the primary key value of the record where the client
matches and the code is SCP. The subquery is sorted by the date in
descending order, it will be return the primary key of the most recent
matching record. To prevent the subquery failing if there's more than one
for the same date, we told it to pick the one with the highest REL_ID value
(2nd field in the ORDER BY clause).
Now that the WHERE clause returns only the primary key value for the most
recent record for the client, you should be able to get the rest of the
field in the main query.
Not sure that's the most efficent solution, but it should work.
-- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Mark Stephenson" <MarkStephenson@discussions.microsoft.com> wrote in message news:5F338AE4-66BA-4C33-8ECA-F32E36B7D6F5@microsoft.com... > Allen, > > I am now picking up what I think is the correct start date (Max), but > still > cant figure out how to get the client relationship name for the record > with > the max start date! > > I am using; > > SELECT Max(CFLIVE_O_RELATIONSHIPS.REL_ID) AS MaxOfREL_ID, > CFLIVE_O_RELATIONSHIPS.REL_SOURCE_PER_GRO_ID, > Max(CFLIVE_O_RELATIONSHIPS.REL_START_DATE) AS MaxOfREL_START_DATE, > Max(CFLIVE_O_RELATIONSHIPS.REL_TO_PER_GRO_ID) AS MaxOfREL_TO_PER_GRO_ID, > Max(IIf([REL_END_DATE] Is Null,"Y","N")) AS [Open] > FROM CFLIVE_O_RELATIONSHIPS > WHERE (((CFLIVE_O_RELATIONSHIPS.REL_RTY_CODE)="SCP")) > GROUP BY CFLIVE_O_RELATIONSHIPS.REL_SOURCE_PER_GRO_ID; > > > Any ideas? I think I have to nest a select somewhere, but am unsure where > or how! > > Thanks, > > > "Allen Browne" wrote: > >> Try Max instead of Last. >> >> "Max" means the highest value. For a date field, that means the latest >> date. >> >> "Last" means read all the records until you come to the last match - and >> that's defined by however they happen to be sorted - so it's probably not >> the result you want. >> >> -- >> Allen Browne - Microsoft MVP. Perth, Western Australia. >> Tips for Access users - http://allenbrowne.com/tips.html >> Reply to group, rather than allenbrowne at mvps dot org. >> >> "Mark Stephenson" <MarkStephenson@discussions.microsoft.com> wrote in >> message news:1D453A8C-FD73-4579-A3D6-2AA6A597FAF3@microsoft.com... >> > Thanks Alan... Have been experimenting with LAST in the totals row... >> > Not >> > sure if its givin me correct data, on checking now.. But I am not >> > getting >> > any >> > duplication... >> > >> > SELECT Last(CFLIVE_O_RELATIONSHIPS.REL_ID) AS LastOfREL_ID, >> > CFLIVE_O_RELATIONSHIPS.REL_SOURCE_PER_GRO_ID, >> > Max(CFLIVE_O_RELATIONSHIPS.REL_START_DATE) AS MaxOfREL_START_DATE, >> > Last(CFLIVE_O_RELATIONSHIPS.REL_TO_PER_GRO_ID) AS >> > LastOfREL_TO_PER_GRO_ID >> > FROM CFLIVE_O_RELATIONSHIPS >> > WHERE (((CFLIVE_O_RELATIONSHIPS.REL_RTY_CODE)="SCP")) >> > GROUP BY CFLIVE_O_RELATIONSHIPS.REL_SOURCE_PER_GRO_ID; >> > >> > I am just about to check out your link... >> > >> > Thanks, >> > >> > Mark Stephenson >> > >> > "Allen Browne" wrote: >> > >> >> See: >> >> Getting a related field from a GroupBy (total) query >> >> at: >> >> http://www.mvps.org/access/queries/qry0020.htm >> >> >> >> The article explains 4 ways to get a result. >> >> >> >> -- >> >> Allen Browne - Microsoft MVP. Perth, Western Australia. >> >> Tips for Access users - http://allenbrowne.com/tips.html >> >> Reply to group, rather than allenbrowne at mvps dot org. >> >> >> >> "Mark Stephenson" <MarkStephenson@discussions.microsoft.com> wrote in >> >> message >> >> news:86484C12-2F56-48E4-8120-231460E168A2@microsoft.com... >> >> >I have a table that contains quite a few field, the ones I am >> >> >interested >> >> >in >> >> > are as follows; >> >> > >> >> > REL_ID Relationship ID >> >> > (table >> >> > key) >> >> > RTY_CODE Text, the criteria >> >> > needs >> >> > to >> >> > be 'SCP' for this. >> >> > REL_SOURCE_PER_GRO_ID This is my client identifyer, >> >> > there >> >> > can be >> >> > duplicates >> >> > for >> >> > this >> >> > field in the table >> >> > REL_START_DATE Date Field (need the max >> >> > of >> >> > this) >> >> > REL_TO_PER_GRO_ID This is who the >> >> > relationship >> >> > is >> >> > too... >> >> > >> >> > The table stores relationships, I need the Most currently created >> >> > (Max >> >> > REL >> >> > START DATE) Per Client And i need to display the START DATE, CLIENT >> >> > ID >> >> > and >> >> > REL TO PER GRO ID. >> >> > >> >> > I have tried searching these forums but I can seem to apply any of >> >> > the >> >> > help >> >> > found to me query. I have not used nested selects before so any >> >> > help >> >> > would >> >> > be great. >> >> > >> >> > Many thanks, >> >> > >> >> > >> >> > Mark Stephenson
- Next message: John Ortt: "Re: I need help w Best Practice for a single entry query..."
- Previous message: Robin: "Re: Changing data under the column heading in a crosstab query"
- In reply to: Mark Stephenson: "Re: Grouped MAX Records?"
- Next in thread: Mark Stephenson: "Re: Grouped MAX Records?"
- Reply: Mark Stephenson: "Re: Grouped MAX Records?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
