Display most recent comment in report

I have a date field in the table which I made visible in the subform.
--
Judy


"Klatuu" wrote:

> How do you know which is the most recent record?
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "mcbaker" wrote:
>
> > 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.
> >
> > --
> > Judy

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);
--
Dave Hargis, Microsoft Access MVP


"mcbaker" wrote:

> I have a date field in the table which I made visible in the subform.
> --
> Judy
>
>
> "Klatuu" wrote:
>
> > How do you know which is the most recent record?
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "mcbaker" wrote:
> >
> > > 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.
> > >
> > > --
> > > Judy

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?
--
Judy


"Klatuu" wrote:

> 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);
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "mcbaker" wrote:
>
> > I have a date field in the table which I made visible in the subform.
> > --
> > Judy
> >
> >
> > "Klatuu" wrote:
> >
> > > How do you know which is the most recent record?
> > > --
> > > Dave Hargis, Microsoft Access MVP
> > >
> > >
> > > "mcbaker" wrote:
> > >
> > > > 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.
> > > >
> > > > --
> > > > Judy

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.
--
Dave Hargis, Microsoft Access MVP


"mcbaker" wrote:

> 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?
> --
> Judy
>
>
> "Klatuu" wrote:
>
> > 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);
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "mcbaker" wrote:
> >
> > > I have a date field in the table which I made visible in the subform.
> > > --
> > > Judy
> > >
> > >
> > > "Klatuu" wrote:
> > >
> > > > How do you know which is the most recent record?
> > > > --
> > > > Dave Hargis, Microsoft Access MVP
> > > >
> > > >
> > > > "mcbaker" wrote:
> > > >
> > > > > 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.
> > > > >
> > > > > --
> > > > > Judy

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

"mcbaker" wrote:

> 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?
> --
> Judy
>
>
> "Klatuu" wrote:
>
> > 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);
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "mcbaker" wrote:
> >
> > > I have a date field in the table which I made visible in the subform.
> > > --
> > > Judy
> > >
> > >
> > > "Klatuu" wrote:
> > >
> > > > How do you know which is the most recent record?
> > > > --
> > > > Dave Hargis, Microsoft Access MVP
> > > >
> > > >
> > > > "mcbaker" wrote:
> > > >
> > > > > 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.
> > > > >
> > > > > --
> > > > > Judy

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

--
Judy


"Ken Sheridan" wrote:

> 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
>
> "mcbaker" wrote:
>
> > 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?
> > --
> > Judy
> >
> >
> > "Klatuu" wrote:
> >
> > > 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);
> > > --
> > > Dave Hargis, Microsoft Access MVP
> > >
> > >
> > > "mcbaker" wrote:
> > >
> > > > I have a date field in the table which I made visible in the subform.
> > > > --
> > > > Judy
> > > >
> > > >
> > > > "Klatuu" wrote:
> > > >
> > > > > How do you know which is the most recent record?
> > > > > --
> > > > > Dave Hargis, Microsoft Access MVP
> > > > >
> > > > >
> > > > > "mcbaker" wrote:
> > > > >
> > > > > > 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.
> > > > > >
> > > > > > --
> > > > > > Judy
>