 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / August 2008Display most recent comment in report
Hello, I have a report based on a table called projects, and I wish to include only the most recent record from a subform called communications. I figure this would be done by a query...but maybe not? Any help would be appreciated! BTW, thanks to those who helped me with my last problem with the Primary and foreign keys. All is good there now.  SignatureJudy How do you know which is the most recent record? SignatureDave Hargis, Microsoft Access MVP > Hello, > [quoted text clipped - 6 lines] > BTW, thanks to those who helped me with my last problem with the Primary and > foreign keys. All is good there now. I have a date field in the table which I made visible in the subform. SignatureJudy > How do you know which is the most recent record? > [quoted text clipped - 8 lines] > > BTW, thanks to those who helped me with my last problem with the Primary and > > foreign keys. All is good there now. Okay, what you can do is create a totals query that returns the records with the Max value for that date field. You will need to include the field that would join it to the main table to include it in the report's recordset. It would be something like: SELECT RecID, CommentsField FROM MyTable WHERE RecID IN (SELECT RecID, Max([DateFieldName]) AS MostRecent FROM MyTable GROUP BY RecID); SignatureDave Hargis, Microsoft Access MVP > I have a date field in the table which I made visible in the subform. > [quoted text clipped - 10 lines] > > > BTW, thanks to those who helped me with my last problem with the Primary and > > > foreign keys. All is good there now. I'm getting an error message on the query. It says that there is an extra ) in the expression. And, let me make sure I have the correct fields: Main table is Projects, primary key ProjectsID. Sub table (form) is Communications Log, primary key NoteID, with a matching Project field. The field with the information I want to display is called Notes. I also have one called Date. I would like to display both Date and Notes. In the relationship table, I made ProjectID in the Projects table the Primary key, and the Project field in the Communications Log as the foreign key. It is a one to many relationship So, I have written the query like this: SELECT Project, NoteField FROM Communications Log WHERE Project IN (SELECT Project, Max([DateFieldName]) AS MostRecent FROM Communications Log GROUP BY Project) Any idea what I am doing wrong? SignatureJudy > Okay, what you can do is create a totals query that returns the records with > the Max value for that date field. You will need to include the field that [quoted text clipped - 19 lines] > > > > BTW, thanks to those who helped me with my last problem with the Primary and > > > > foreign keys. All is good there now. The most obvious thing I see is you are using spaces in your names (which you should not) and not enclosing the name in brackets. SELECT Project, NoteField FROM [Communications Log] WHERE Project IN (SELECT Project, Max([DateFieldName]) AS MostRecent FROM [Communications Log] GROUP BY Project) Also, you should not use Date as a field name. It is a reserved word. You should not use any reserved words like Date, Name, Month, Year, etc. Access can get confused over what you are referring to. SignatureDave Hargis, Microsoft Access MVP > I'm getting an error message on the query. It says that there is an extra ) > in the expression. [quoted text clipped - 41 lines] > > > > > BTW, thanks to those who helped me with my last problem with the Primary and > > > > > foreign keys. All is good there now. Judy: The IN operator can only be used where the subquery returns one column; yours returns two It should be: SELECT Project, Notes, [Date] FROM [Communications Log] AS CL1 WHERE [Date] = (SELECT MAX([Date]) FROM [Communications Log] AS CL2 WHERE CL2.Project = CL1.Project); Note how the two instances of the table are differentiated by the aliases CL1 and CL2 to enable the subquery to be correlated with the outer query. The subquery returns the MAX (latest) date where the value in the Project column equals the value in the Project column in the current row of the outer query, so by restricting the outer query to those rows where the date equals the value returned by the subquery, the outer query returns only those rows with the latest date per project. I'd strongly advise against using Date as a column name. It’s the name of a built in function so should be avoided for object names. Use something specific like NoteDate or DateReceived. Ken Sheridan Stafford, England > I'm getting an error message on the query. It says that there is an extra ) > in the expression. [quoted text clipped - 41 lines] > > > > > BTW, thanks to those who helped me with my last problem with the Primary and > > > > > foreign keys. All is good there now. Thanks for the clarification. However, I continue to get an error message: Syntax error (missing operator) in query expression SELECT Project, Notes, [NoteDate] FROM [Communications Log] AS CL1 WHERE [NoteDate] = (SELECT MAX([Date]) FROM [Communications Log] AS CL2 WHERE CL2.Project = CL1.Project); Please note that I changed the name of the "Date" field to NoteDate I'm sure this is a simple fix, once I find what I am missing...thank you to everyone kind enough to help me out here. Judy SignatureJudy > Judy: > [quoted text clipped - 68 lines] > > > > > > BTW, thanks to those who helped me with my last problem with the Primary and > > > > > > foreign keys. All is good there now. Judy: You need to change the column name in the subquery too: SELECT Project, Notes, NoteDate FROM [Communications Log] AS CL1 WHERE NoteDate = (SELECT MAX(NoteDate) FROM [Communications Log] AS CL2 WHERE CL2.Project = CL1.Project); but that doesn't explain the error. I'd have expected it to have produced a parameter prompt for [Date]. I can't at first sight see anything in the above which would produce the error. In fact I've tested it against a table with the same name and columns as yours without any problem. See what happens when you change it to NoteDate in the subquery. Ken Sheridan Stafford, England > Thanks for the clarification. However, I continue to get an error message: > [quoted text clipped - 85 lines] > > > > > > > BTW, thanks to those who helped me with my last problem with the Primary and > > > > > > > foreign keys. All is good there now. Hi Ken, I copied and pasted your script as the only field in a new report, and I now get an error message that says the syntax in the subquery is incorrect. There needs to be a parentheses around the subquery. Any ideas? SignatureJudy > Judy: > [quoted text clipped - 105 lines] > > > > > > > > BTW, thanks to those who helped me with my last problem with the Primary and > > > > > > > > foreign keys. All is good there now. Judy: There are parentheses around the subquery! I'm not sure just what you are doing, but the SQL statement is a query; nothing to do with 'a field in a report'. What I think you need to do is include both the Projects and Communications Log tables in the query and then use the query as the report's RecordSource, so the query would be like this: SELECT Projects.*, CL1.Notes, CL1.NoteDate FROM Projects INNER JOIN [Communications Log] AS CL1 ON Projects.ProjectID = CL1.Project WHERE CL1.NoteDate = (SELECT MAX(NoteDate) FROM [Communications Log] AS CL2 WHERE CL2.Project = CL1.Project); Firstly make sure that the column and table names in the above are exactly the same as yours. To create the query open the query designer in the usual way, and then switch to SQL view. Paste the above SQL statement into the SQL window in place of whatever is there already. Save the query under a suitable name. Open the query itself to check that it returns the expected rows. If it does then set the RecordSource property of your report to the name of the query. You can then add text box controls to the report with NoteDate and Notes as their ControlSource properties. The one reservation I have about the above query is that because it uses an INNER JOIN it will only return projects which have at least one matching record in Communications Log. If there could be projects without matches in Communications Log you'd normally get around this by using a LEFT OUTER JOIN. However, you can't do so in the above case because you'd be restricting the query on a column (NoteDate) on the outer side of the join, which is not permitted; in fact it just causes it to behave as if it were an INNER JOIN. So, if this is a possibility you'd need to use a different approach. The simplest is to use two queries. Firstly create the original query I gave you: SELECT Project, Notes, NoteDate FROM [Communications Log] AS CL1 WHERE NoteDate = (SELECT MAX(NoteDate) FROM [Communications Log] AS CL2 WHERE CL2.Project = CL1.Project); Save this as qryLatestNotes. Then create another query which left joins this to the projects table: SELECT Projects.*, qryLatestNotes.Notes, qryLatestNotes.NoteDate FROM Projects LEFT JOIN qryLatestNotes ON Projects.ProjectID = qryLatestNotes.Project; Save this query under a suitable name and use it as the report's RecordSource. Ken Sheridan Stafford, England > Hi Ken, > [quoted text clipped - 113 lines] > > > > > > > > > BTW, thanks to those who helped me with my last problem with the Primary and > > > > > > > > > foreign keys. All is good there now. Hi Ken, I know that there are parentheses around the subquery, and you know that too, but someone has to tell my version of Access! (LOL) I'll work through what suggested, and let you know how I did. Thank you so very much for working with me on this...I'll be in touch soon. SignatureJudy > Judy: > [quoted text clipped - 170 lines] > > > > > > > > > > BTW, thanks to those who helped me with my last problem with the Primary and > > > > > > > > > > foreign keys. All is good there now. Judy: What's happening is that by entering the SQL statement as the ControlSource of a control in the report it thinks the *whole* query is a subquery, so it expects parentheses around the whole thing. Do things the way I described, and you should be OK though. Ken Sheridan Stafford, England > Hi Ken, > [quoted text clipped - 179 lines] > > > > > > > > > > > BTW, thanks to those who helped me with my last problem with the Primary and > > > > > > > > > > > foreign keys. All is good there now. Hi Ken, We're close, but not quite there yet, I can create a data table with the records I want, that is, the most recent note from each record that has a note, but when I put the query in the control source in the report, Access asks for a parameter. I tried a couple of different scenarios...all, a-z, date ranges, etc., but the field on the report displays an error. I would examine the expression, but I really don't know what I'm looking at... Then, I made a new report, selecting all the fields from the Projects table that I want, and then selecting the Note field and NoteDate field. This method displays all of the notes, but does not ask for parameters before opening. SignatureJudy > Judy: > [quoted text clipped - 189 lines] > > > > > > > > > > > > BTW, thanks to those who helped me with my last problem with the Primary and > > > > > > > > > > > > foreign keys. All is good there now. Hi Ken, I found the problem. Some of my records in the Communications Log had the same date, so as soon as I made them each a different date, the display was correct. Thank you, thank you, thank you for your patience and help as I worked through this; I really appreciated it! SignatureJudy > Hi Ken, > [quoted text clipped - 204 lines] > > > > > > > > > > > > > BTW, thanks to those who helped me with my last problem with the Primary and > > > > > > > > > > > > > foreign keys. All is good there now. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.