Limit report to show only most recent information for each entry

[Mannie G]

My database has a list of companies with a linked table listing quotation
details. I would like to run a report 'By Company' that shows only the
latest quote sent to each of the companies. I have tried using 'Dmax',
however this limits the report to just the company with the latest quote
rather than all the companies with just their latest quote. I have been
using the date field in the Quotations table/query to limit the data.

Can anyone help?

 

[Allen Browne]

The simplest solution will be to use just the Company table in the main
report, and use a subreport to show the most recent quote.

For the subreport, create a query into your Quotations table, and sort by
date descending. Then open the Properties box (View menu), and enter 1
beside the Top Values property.

 

[Marshall Barton]

My database has a list of companies with a linked table listing quotation
details. I would like to run a report 'By Company' that shows only the
latest quote sent to each of the companies. I have tried using 'Dmax',
however this limits the report to just the company with the latest quote
rather than all the companies with just their latest quote. I have been
using the date field in the Quotations table/query to limit the data.

The key is to set up the report's record source query to
retrieve the desired data. Try using something like:

SELECT C.company, Q.quotedate, . . .
FROM Companies As C INNER JOIN Quotations As Q
ON C.companyID = Q. CompanyID
WHERE Q.quotedate =
(SELECT Max(x.quotedate)
FROM Quotations As X
WHERE X.CompanyID = Q.CompanyID)

 

[Mannie G]

Thanks for your suggestion, however I still can only get one quote in the
query (the last dated quote) as opposed to the last quotes to all companies
with an entry. Am I doing something wrong?
--
Thanks

Mannie G

The simplest solution will be to use just the Company table in the main
report, and use a subreport to show the most recent quote.

For the subreport, create a query into your Quotations table, and sort by
date descending. Then open the Properties box (View menu), and enter 1
beside the Top Values property.

 

[Mannie G]

Thanks for your suugestion Marshall,

Could you help me a bit more as I am only a begginer.
Could you expand on the detail of the SELECT string meaning and where do I
need to enter this string?

Thanks

Mannie G

 

[Allen Browne]

The RecordSource of the main report is the Company table. Therefore it shows
all companies.

The RecordSource of the subreport is the TOP 1 query. Therefore it shows
only the latest quote.

Put the 2 together (main report and subreport), and the main report lists
every company, with the subreport listing just the latest quote under each
one.

 

[Marshall Barton]

A SELECT statement is a query. Create a new query without
selecting a table. When you see the query design grid use
the View menu to change it to SQL view. The Copy the Select
statement from my post and Paste it over the top of whatever
stuff Access had preinserted in the SQL window. After that
is done, review the names of the tables and fields and
change them to be exactly the name youare using.

Once you think the names are correct, run the query by using
the View menu to change the query to datasheet view. If it
displays some data, review the data to see if it's what you
want. If it refuses to run, use the error message to
determine what else needs to be corrected and interate until
ou are getting the desired data records. Then change your
report's RecordSource to use this query.

 

[Mannie G]

Thanks Marshall, all working well!
--
Thanks

Mannie G

A SELECT statement is a query. Create a new query without
selecting a table. When you see the query design grid use
the View menu to change it to SQL view. The Copy the Select
statement from my post and Paste it over the top of whatever
stuff Access had preinserted in the SQL window. After that
is done, review the names of the tables and fields and
change them to be exactly the name youare using.

Once you think the names are correct, run the query by using
the View menu to change the query to datasheet view. If it
displays some data, review the data to see if it's what you
want. If it refuses to run, use the error message to
determine what else needs to be corrected and interate until
ou are getting the desired data records. Then change your
report's RecordSource to use this query.
--
Marsh
MVP [MS Access]

Could you help me a bit more as I am only a begginer.
Could you expand on the detail of the SELECT string meaning and where do I
need to enter this string?

 

[Jacky]

Hi Marshall,

I'll like to know more about your SQL statement.
I do have similar difficulty in constructing my report to display the latest
update of each documents. Basic structure of my database is a master list of
all the documents (identified by doc #) and its revision details (linked by
doc # and sorted by edition #). Within the report, i do have other fields to
be display across the same detail, would it be possible?

Thanks.

A SELECT statement is a query. Create a new query without
selecting a table. When you see the query design grid use
the View menu to change it to SQL view. The Copy the Select
statement from my post and Paste it over the top of whatever
stuff Access had preinserted in the SQL window. After that
is done, review the names of the tables and fields and
change them to be exactly the name youare using.

Once you think the names are correct, run the query by using
the View menu to change the query to datasheet view. If it
displays some data, review the data to see if it's what you
want. If it refuses to run, use the error message to
determine what else needs to be corrected and interate until
ou are getting the desired data records. Then change your
report's RecordSource to use this query.
--
Marsh
MVP [MS Access]

Could you help me a bit more as I am only a begginer.
Could you expand on the detail of the SELECT string meaning and where do I
need to enter this string?