Thread: Return latest event for a case

I posted something here, if anyone would like to help I'd be grateful.
http://www.access-programmers.co.uk/...d.php?t=198961
I don'twant to maintain two threads, if you're a member there I'd love your help, if you do want to post here, I'll copy-pasta it to there on your behalf.
TIA
CC

Copy pasta? Cook more linguini?

MMMmmmm delicious copypasta. Are you going to help or what?

I've never used Access or SQL, CC, so no help coming from this corner. But do call when the pasta is ready.

I would assume that a LEFT JOIN would give you the Nulls, however, I'm not really sure as to the syntax for retrieving all info. for the last record of each t1 id in t2 I'm afraid given the setup.

From your sample .mdb it appears you have no unique ID in Events table, is that correct ?

edit: ignore the above - event id is unique... doh

CC, perhaps something along the lines of:

SELECT t_cases.*, t_events.* FROM cases As t_cases
LEFT JOIN events AS t_events ON t_cases.Case_ID = t_events.Case_ID
WHERE 1=1
AND t_cases.Case_ID & "@" & t_events.date IN
(SELECT t_cases.Case_ID & "@" & MAX(t_events.date) FROM cases AS t_cases
LEFT JOIN events AS t_events ON t_cases.case_id = t_events.case_id
GROUP BY t_cases.case_id)
ORDER BY t_cases.case_id

of course if you have multiple events on the same date for the same case then the above might not work... tweak the above as necessary in terms of final output but hopefully a step in the right direction ?

CC,

Try this:

SELECT Cases.Case_ID, Cases.CustomerName, Cases.Cost, Cases.Initiated, Events.Event_ID, Events.Event, Events.Date
FROM Cases LEFT JOIN Events ON Cases.Case_ID = Events.Case_ID
WHERE (((Events.Event_ID) In (SELECT TOP 1 Event_ID FROM Events WHERE Events.Case_ID = Cases.Case_ID ORDER BY Events.Case_ID, Events.Date Desc))) OR (((Events.Event_ID) Is Null));

Hope this helps,

Dan

P.S. You could add an ORDER BY to the end of that to sort you "Cases" as you see fit.

Hi CC,

I posted my attempt on your original thread; hope it helps.

Cheers,

Is it marinara or alfredo?

Thanks very much guys - if you want to share a view on my follow up question, please do - again I'll post-back.

CC

Dan's is certainly more succinct.
TBH my knowledge of Access is limited and in the MySQL version I have to work with sub queries aren't permitted (it's an o-l-d version) - so I doubt my suggestion is optimal in any way / shape or form.

I think / guess it would boil down to whether or not it's possible to have > 1 event occurring for a given case on the max date ?

If you do mine would return all instances whereas Dan's would I believe return just one - then it's a case of pick whichever suits best.

The man at access-programmers says you're a dumbass, DO.

(Before anyone gets antsy)
Huge amounts of tongue-in-cheek here.

Seriously though, thanks for the help guys.

I'm sure it's all justified...

Regards the WHERE 1=1
I confess this is my standard approach as for the most part the queries I code are dynamic (ie variable number of test from 0 to n)
As the big dutch cheese says - it's a very useful construct but given this code is not dynamic it's entirely superfluous.
I am sure you will notice the performance gain from removing it ...

As for all the other gubbins .. I'd reiterate my limited knowledge of running optimal sub queries so unfortunately I have no cause for argument
(but yes TOP is limited in scope - ie would not work in MySQL [uses LIMIT at tail of query])

FWIW, IMO, CXL query would (like mine) also return multiple records should you have them for a given case and max date and it will also return in the format you requested - optimal on all fronts I'd say.
(rep points from me at any rate)