Sorting

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

Guest

I have a database full of individuals information, with the SS# as the
primary key. Each individual may have several case notes, each case note is
a separate record associated with a particular SS#. So there are several
case notes associated with one SS#. I need to create a report that would
list all SS#s and the last case note entered for that SS#.
 
For this to happen you need something like a date/time field linked to the
case note. In other words, what is it that defines which is the last case
note.

If you have this you can create a Totals query and Group By the SS# then
find the Max (not Last) of the date and time. You should then be able to use
this as a subquery to find the latest case note.
 
J. Whittle

Thank you very much for your post. It gave me some great ideas. However I
am not familiar with the specifics of this implementation. Could your please
reply with more detail.

Thank you for your help,

C. Ellis
 
Hi,

The SQL below isn't exactly right, but it's a start. You need to change the
table and field names to what's actually in your database. Hopefully you have
a date/time field in the CaseNotes table and change DateField to that field.
If not, you need something like a number system that tells which case note is
the latest. When you get the SQL to work, please post it back here and we
will tweak it to get exactly what you want. It will probably entail a
subquery.

SELECT Individuals.SS#,
CaseNotes.CaseNotes,
Max(CaseNotes.DateField) AS LAST_CaseNote
FROM Individuals INNER JOIN CaseNotes ON (Individuals.SS# =
CaseNotes.TABLE_NAME)
GROUP BY Individuals.SS#, CaseNotes.CaseNotes;
 
Back
Top