I know the answer is almost certainly here, but I've been wrestling for a long time and I'm stuck. If someone wouldn't mind showing me how to make it work with my specific tables I'd be really grateful:
I have a table of Cases and a table of Events, each Case has 0-many Events. I want to show all Cases with the latest Event for each Case.
I have:
Code:
SELECT t_Cases.Case_ID, t_Events.Event_ID, t_Events.Date
FROM t_Cases INNER JOIN t_Events ON t_Cases.ID = t_Events.Case_ID;
(I've stripped out extraneous fields, but would like to include things like t_Cases.CustomerName and t_Events.Description but I don't think these are necessary for the question and I'm trying to keep it clear for me and everyone else - please tell me if I'm being an idiot).
If I do this:
Code:
SELECT t_Cases.Case_ID, t_Events.Event_ID, Max(t_Events.Date) AS MaxOfDate
FROM t_Cases INNER JOIN t_Events ON t_Cases.Case_ID = t_Events.Case_ID
GROUP BY t_Cases.Case_ID, t_Events.Event_ID;
I still get just as many results - as I'm grouping by a series of unique entries (Event_ID).
If I do this:
Code:
SELECT t_Cases.Case_ID, Max(t_Events.Event_ID) AS MaxOfEvent_ID, Max(t_Events.Date) AS MaxOfDate
FROM t_Cases INNER JOIN t_Events ON t_Cases.Case_ID = t_Events.Case_ID
GROUP BY t_Cases.Case_ID;
I get the maximum Event number, which is often not the latest chronologically...
SELECT t_Recoveries.NADRU_ID, Max(t_Events.Date) AS MaxOfDate
FROM t_Recoveries INNER JOIN t_Events ON t_Recoveries.NADRU_ID = t_Events.NADRU_ID
GROUP BY t_Recoveries.NADRU_ID;
I did mean to show the event details, not just the date of it.
Hi Trevor,
Thanks for your input, I'm sure you've seen it before, to protect the innocent I'll need to create a new db with just this info in, I was hoping not to need to do that, bear with me.
Please find attached an example showing what I'm looking for.
As before - each Case has 0 to many Events, I'd like an extract of all Cases showing the latest Event (including details of that Event) for each Case - where a Case has no Events it should show the Case with blanks in the Event.
I've put examples into a table with a cunning name - you should be able to see exactly what's going on from that, and see that max of Event_id is not reliable in terms of chronology.
SELECT Cases.Case_ID, Cases.CustomerName, Cases.Cost, Cases.Initiated, qsub2.Event_ID, qsub2.Event, qsub2.Date
FROM Cases LEFT JOIN (SELECT Events.Event_ID, Events.Case_ID, Events.Event, Events.Date
FROM (SELECT Events.Case_ID, Max(Events.Date) AS MaxOfDate
FROM Events
GROUP BY Events.Case_ID
) AS qsub1 INNER JOIN Events ON (qsub1.Case_ID = Events.Case_ID) AND (qsub1.MaxOfDate = Events.Date)
) AS qsub2 ON Cases.Case_ID = qsub2.Case_ID;
Believe it or not, but I do understand your requirement.
This you need to do in 2 steps
1) Find the most recent date/time (hence my suggestion)
2) Use 1 to retrieve the data you want to see.
This is exactly what also CXL has done:
Code:
SELECT Cases.Case_ID, Cases.CustomerName, Cases.Cost, Cases.Initiated, qsub2.Event_ID, qsub2.Event, qsub2.Date
FROM Cases
LEFT JOIN (SELECT Events.Event_ID, Events.Case_ID, Events.Event, Events.Date
FROM ( [b][u]SELECT Events.Case_ID, Max(Events.Date) AS MaxOfDate[/b][/u]
[b][u]FROM Events[/b][/u]
[b][u]GROUP BY Events.Case_ID[/b][/u]) AS qsub1
INNER JOIN Events ON (qsub1.Case_ID = Events.Case_ID)
AND (qsub1.MaxOfDate = Events.Date)
) AS qsub2 ON Cases.Case_ID = qsub2.Case_ID;
The bolded and underlined subquery takes care of 1) find the most recent date per case. -- Take note at how simular/identical this is to my suggested query --
That is then joined back into events to 2) find that date per case and display the full event.
Thanks for your help, both, this is terrific - I was underestimating the complexity of the query.
namliam, I didn't mean to cause offence, sorry, I think perhaps you overestimated my ability - I had no idea from your first post that you were intimating something as in-depth as what CXL & you posted (to me your first post just meant you didn't really understand the request, so I clarified!) - like I say, thanks for helping.
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
Dan provided this:
Code:
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));
Both work beautifully (of course)
Dan's in particular seems neater - but neat code isn't always efficient (I know a certain someone with a furtive penchant for iifs) - and as far as I can tell they all do the same thing (results, not modus operandii).
I have no desire to try to pitch people against each other but I would like to know which of the three approaches I would benefit most from going through in detail to understand exactly how it works - similar queries are going to require similar logic and I would expect to be able to work it out for myself from now on with these examples, but any guidance re: best practice would probably help me go in the right direction.
First things first, Look at my SQL and look at CXL's ... now tell me which is more readable, thus more understandable and more maintainable??
So please learn to format and not splash SQL ...
Second, please view any below as trying to be contructive, it is not meant to try and bring down anyone or even will I pretend that this is 100% fully completely the bibble or anything.
Lets start with DO...
Code:
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 ([u][b]SELECT t_cases.Case_ID & "@" & MAX(t_events.date) [/b][/u]
[u][b]FROM cases AS t_cases[/b][/u]
[u][b]LEFT JOIN events AS t_events ON t_cases.case_id = t_events.case_id[/b][/u]
[u][b]GROUP BY t_cases.case_id[/b][/u])
ORDER BY t_cases.case_id
Note the bolded and underlined part simular again to my original suggestion.
There are 3/4 'non-optimal' things in here...
1) 1=1
This is basicaly non-sense... it can be usefull but here non-sense
2) Left Join
In this case your particularly intrested in the Event information, thus any cases witout events your not intrested in...
Left join will allow for cases to return without an event, even thought you are (I think) not intrested in them.
Using the left join vs an Inner Join has/can have a serious impact on performance, where left join is much more demanding than Inner Join.
3) IN
Using an IN contruct is usually a bad idea... Using a subselect with a join is much much better and prefered. Usually (internal to the database) this is converted to a join contruct anyways thus you will not notice the performance hit...
However, Usually implies that sometimes it doesnt happen... When it doesnt happen it is a serious impact to performance and will drive you nuts trying to figure it out.
On a general level, avoid IN contructs with Sub-selects, the same way we advice against using (m)any D-functions (DLookup/Dsum/etc)....
4) Implicit conversions
Here it dont matter but again its a base principle.... Avoid this at all cost to avoid nightmares and headaches....
Here we are mixing 3 types: t_cases.Case_ID & "@" & t_events.date
Case_ID I assume to be a number
t_events.date I assume to be a real date field.
"@" is offcourse a text string....
Instead this should look something like:
Format(t_cases.Case_ID,"00000000") & "@" & Format(t_events.date, "YYYYMMDDHHNNSS")
explicitly converting the non-texts to texts to avoid issues...
5) Naming convention
Dont know if this is in your database... or not... but in access Date is a reserved word and should not be used as a column name.
Same goes for any other reserved words like: Public, Sub, Private, etc... etc...
And applies to any object, form name, table name, etc etc..
Now Dan's
Code:
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));
Again the IN construct...
The TOP 1 is database specific and may not be supported by all databases... Can work though like in this case but be carefull with its use.
The serious issue here is: WHERE Events.Case_ID = Cases.Case_ID
This join's the Where clause back into the table in the from, it works... However if the Events table is a big table and/or a growing table this can be a serious hit on performace as the subquery will be executed per row not as a whole.
As a result, Dan's version may seem the neatest (is that a word?), it IMHO is also the worst of the 3 options...
Do's example will work, but has some issues which are mostly covered by CXL's sample, which IMHO is the way to go...
1) It limits the Left joins your using
2) It uses joins instead of IN construct
3) It operates on 'natural columns' not concatinating and/or using implicit conversions
Lastly I want to point out to you "Naming convention" note how Do's and CXL's use it...
qsub2... q for query
t_events ... t for table
You should read up on and addopt a nice naming convention this will prevent future (maintenance) issues for you.
In general you can say:
tbl / t for tables, qry / q for queries, frm / f for forms etc....
If you only do this, this will, I can guarantee, save you issues. as well as the non-use of reserved words and the non-use of special characters anywhere in any name or code.
This includes: space, /, _, ', ", -, &, etc etc...
but based on your feedback, I will forgo the cases with blank events, as all cases should have events. I will produce a separate audit query for cases with no events.
3 - Great, thanks.
4 - Understood, tip for the future.
Not a matter of being a moron... I have co-workers that have this same habbit ... it has its reasons in coding this way and like I said it can be usefull
but based on your feedback, I will forgo the cases with blank events, as all cases should have events. I will produce a separate audit query for cases with no events.
The resources available for text formatting in an Access query SQL window are primitive at best (no tabs, etc.). What method do you employ to overcome this?
