Grouped MAX Records?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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,

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,
 
Not sure what else to suggest, Mark.

Presumably you have an index on the date field?
It may be a matter of fiddling with ideas until you get the optimized one.

Worst case, you have a query that is returning those 2 things quickly, so
you could write that to a temp table (INSERT INTO ...), and then build from
there?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
 
Allen,

Thanks for all your help... I think to be honest the problem is Access!!! I
tried recreating the tables etc will dummy data, only a few hundred rows and
everything works!!! Must have something to do with the amount of data in the
Relationships table (About 200000 rows and in total about 30 fields!!) That
combined with draggin the info via ODBC and using Citrix to access
MsAccess!!!

At least I know that I am sane!!!

Many thanks for all your help
 
Back
Top