Display most recent comment in report

M

mcbaker

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.
 
K

Klatuu

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);
 
M

mcbaker

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 said:
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 said:
I have a date field in the table which I made visible in the subform.
Click to expand...
Click to expand...
 
K

Klatuu

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 said:
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 said:
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 said:
I have a date field in the table which I made visible in the subform.
--
Judy


:

How do you know which is the most recent record?
--
Dave Hargis, Microsoft Access MVP


:

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.
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Sheridan

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 said:
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 said:
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 said:
I have a date field in the table which I made visible in the subform.
--
Judy


:

How do you know which is the most recent record?
--
Dave Hargis, Microsoft Access MVP


:

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.
Click to expand...
Click to expand...
Click to expand...
 
M

mcbaker

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 said:
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 said:
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 said:
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


:

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


:

How do you know which is the most recent record?
--
Dave Hargis, Microsoft Access MVP


:

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.
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Sheridan

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

mcbaker said:
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 said:
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 said:
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


:

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


:

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


:

How do you know which is the most recent record?
--
Dave Hargis, Microsoft Access MVP


:

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.
Click to expand...
Click to expand...
Click to expand...
 
M

mcbaker

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


Ken Sheridan said:
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

mcbaker said:
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 said:
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.

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


:

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


:

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


:

How do you know which is the most recent record?
--
Dave Hargis, Microsoft Access MVP


:

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.
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Sheridan

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

mcbaker said:
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?
--
Judy


Ken Sheridan said:
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

mcbaker said:
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


:

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.

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


:

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


:

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


:

How do you know which is the most recent record?
--
Dave Hargis, Microsoft Access MVP


:

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.
Click to expand...
Click to expand...
Click to expand...
 
M

mcbaker

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


Ken Sheridan said:
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

mcbaker said:
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?
--
Judy


Ken Sheridan said:
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:

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


:

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.

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


:

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


:

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


:

How do you know which is the most recent record?
--
Dave Hargis, Microsoft Access MVP


:

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.
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Sheridan

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

mcbaker said:
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.
--
Judy


Ken Sheridan said:
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

mcbaker said:
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?
--
Judy


:

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:

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


:

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.

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


:

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


:

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


:

How do you know which is the most recent record?
--
Dave Hargis, Microsoft Access MVP


:

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.
Click to expand...
Click to expand...
Click to expand...
 
M

mcbaker

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.


--
Judy


Ken Sheridan said:
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

mcbaker said:
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.
--
Judy


Ken Sheridan said:
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,

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


:

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:

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


:

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.

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


:

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


:

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


:

How do you know which is the most recent record?
--
Dave Hargis, Microsoft Access MVP


:

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.
Click to expand...
Click to expand...
Click to expand...
 
M

mcbaker

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


mcbaker said:
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.


--
Judy


Ken Sheridan said:
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

mcbaker said:
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.
--
Judy


:

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,

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


:

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:

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


:

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.

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


:

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


:

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


:

How do you know which is the most recent record?
--
Dave Hargis, Microsoft Access MVP


:

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating a report using the most recent records by date 1
Return the Most Recent Update 4
Exclude most recent month 3
Report query for most recent date 2
Most recent delivery date 1
Query Assistance (Last or Most Recent) 4
Most Recent Record in a set 1
Access Access Report Works on One Machine but not Another 3

Top