Latest date (1 Viewer)

[tezread]

Hi all

I have a table called dbotransfer with a list of transfer episodes (Primary key is EpisodeID)

Another table called dboTransferJournal has a list of journal entries relating to episodes. in dbotransfer table. (One to many relationshuip set up)

There are several journal entries for each episode sometimes so I am trying to query the LATEST journal entry by using the MAX query (Group by totals etc) but it won't work?
It just returns the same number of records each time

See below:

SELECT dbo_Transfer.EpisodeID, dbo_Transfer.RequestDatetime, dbo_Transfer.ReceiveDatetime, dbo_Transfer.PatientSurname, dbo_Transfer.PatientDoB, dbo_TransferProcedure.ProcedureId, dbo_TransferProcedure.ProcedureType, dbo_TransferProcedure.ProcedureDetail, dbo_TransferProcedure.ProcedureDatetime, dbo_TransferJournal.JournalId, dbo_TransferJournal.Hospital, Max(dbo_TransferJournal.JournalDatetime) AS MaxOfJournalDatetime, dbo_TransferJournal.JournalType, dbo_TransferJournal.EventDatetime
FROM (dbo_Transfer LEFT JOIN dbo_TransferProcedure ON dbo_Transfer.[EpisodeID] = dbo_TransferProcedure.[EpisodeId]) LEFT JOIN dbo_TransferJournal ON dbo_Transfer.[EpisodeID] = dbo_TransferJournal.[EpisodeId]
GROUP BY dbo_Transfer.EpisodeID, dbo_Transfer.RequestDatetime, dbo_Transfer.ReceiveDatetime, dbo_Transfer.PatientSurname, dbo_Transfer.PatientDoB, dbo_TransferProcedure.ProcedureId, dbo_TransferProcedure.ProcedureType, dbo_TransferProcedure.ProcedureDetail, dbo_TransferProcedure.ProcedureDatetime, dbo_TransferJournal.JournalId, dbo_TransferJournal.Hospital, dbo_TransferJournal.JournalType, dbo_TransferJournal.EventDatetime
HAVING (((dbo_Transfer.RequestDatetime) Between #4/1/2012# And #5/31/2012#) AND ((dbo_TransferJournal.JournalType) Like "Acc*"));

 

[tezread]

anyone? lol

 

[Bryan]

I think MAX is what you need, but your query seems to be taking another approach. See THIS thread to see if it helps. The code is rough, but it should give you an idea as to direction.

 

[RainLover]

Use a Query in Design View.

Start with One Only field. If that works add another and another until it doesn't work.

Then you will know where the problem is. If you can't solve the problem maybe we can help.

A Tip

Write your code so that we can see it all in one glance.
If I were to submit an assignment in the style that you used, my paper would be returned unmarked.
My instructor was lazy and he taught me to be the same.