Query creating duplicates

[Lisad]

I have the following two querys which are supposed to return only the latest Note for a Prospect.

QRYNotes
SELECT TBLNotes.ProspectID AS Expr1, Max(TBLNotes.NoteDate) AS MaxOfNoteDate
FROM TBLNotes
GROUP BY TBLNotes.ProspectID;

QRYNotes2
SELECT DISTINCT TBLProspects.CompanyName, TBLNotes.NoteID, TBLNotes.Note, TBLNotes.NoteDate
FROM (TBLProspects INNER JOIN TBLNotes ON TBLProspects.ProspectID=TBLNotes.ProspectID) INNER JOIN QRYNotes ON TBLNotes.NoteDate=QRYNotes.MaxOfNoteDate;

When I run the QRYNotes2, I get more than one record per Prospect instead of just the newest NoteDate.

What is wrong?

 

[Doug_Y]

Hi,

You second should be:

SELECT DISTINCT
	  tblProspects.CompanyName
	, tblNotes.NoteID
	, tblNotes.Note
	, tblNotes.NoteDate
FROM 
	  (tblProspects 
INNER JOIN 
	  tblNotes 
ON 
	  tblProspects.ProspectID = tblNotes.ProspectID) 
INNER JOIN 
	  qryNotes 
ON 
	 (tblNotes.NoteDate = qryNotes.MaxOfNoteDate)
[B]AND 
	 (tblNotes.ProspectID = qryNotes.ProspectID)[/B];

This will return the record with the latest note.

HTH

 

[Lisad]

Enter Parameter Value

I have made the changes you suggested and I now get prompted for the Parameter value QRYNotes.ProspectID. I want to show the first note for all the records

Any ideas?

 

[Doug_Y]

In your first query, remove the "AS Expr1"... no need for it.