Getting Last Record!!

[headintheclouds]

Hi all hope you can help..please

I have a form with a subform (poorly designed by somebody) and need a query that pulls certain information (which I have done) but I also need it to only pull the last record in the subform (the subform is like a dairy entry, a date followed by a comment). I also thought I cracked this by using the "totals" button and using the "last" function) but then I noticed the query pulled most of the last records but not all of them and if I change the "last" function to "first" the query pulls up the missing last records and then most of the rest are wrong. Can anyone please help, if you don't understand what i mean please ask and i'll try explain better. I would post up the database but it's a work database and has senstive information on it

Thanks for your time

Headintheclouds

 

[ScottGem]

I assume that the last record is the one with the most current date? In that case, you can use a subquery to get that one record:

SELECT * FROM table
WHERE RecordID IN(SELECT TOP 1 RecordID FROM table ORDER BY dateField DESC);

 

[headintheclouds]

Cheers for that but I don't understanded access that much. The query does work using the "last" function until I put a new date with a new comment in. I then run the query and it won't pick up the most recent comment an example is below:

28/11/2005 - Also A Datacard (It will pick up this record and not the one below)
06/12/2005 - Test

Headintheclouds

 

[ScottGem]

Enter the IN Clause I gave you as the criteria of your query.

 

[headintheclouds]

here is the sql view using the max function

SELECT tblProspectDatacard.dcSource, tblProspectDatacard.dcCompanyName, tblProspectDatacard.dcDatePassedFromMarketing, tblProspectDatacard.dcRnlDateCombined, tblDiary.diComments, tblProspectDatacard.dcExecutiveDealing, tblProspectDatacard.[dc£placed], tblProspectDatacard.dcSecured, Max(tblDiary.diDateContacted) AS MaxOfdiDateContacted
FROM tblProspectDatacard LEFT JOIN tblDiary ON tblProspectDatacard.dcCompanyID = tblDiary.dcCompanyID
GROUP BY tblProspectDatacard.dcSource, tblProspectDatacard.dcCompanyName, tblProspectDatacard.dcDatePassedFromMarketing, tblProspectDatacard.dcRnlDateCombined, tblProspectDatacard.dcExecutiveDealing, tblProspectDatacard.[dc£placed], tblProspectDatacard.dcSecured
HAVING (((tblProspectDatacard.dcDatePassedFromMarketing) Between [Enter Start Date] And [Enter End Date]) AND ((tblProspectDatacard.dcExecutiveDealing)=[Enter Executive Number]));

this does show the most recent comment first but it also shows all the the other PAST comments which I don't need.

headintheclouds

 

[ScottGem]

As Pat has said, you have to use one query to get the one record you want and then another to filter for that one record. That's what using an IN clause as I suggested does.