show only most current record from sub-report

[Guest]

Hi all,

I have a table with various info re: quotation files (Customer Quotes). The
key field is Quote ID. I have another table with specific detail relating to
the quotation records (Quote Details). This detail table can have 0, 1 or
many records for each Quote ID.

What I'd like to be able to do is create a report which shows the Customer
Quote information, and only the most recent (related) record from Quote
Details.

I'm Ok with creating & linking sub-reports, but need help in getting only
the most recent record from the detail file. Any help you can give would be
most appreciated.

Thanks, Sue

 

[Marshall Barton]

I have a table with various info re: quotation files (Customer Quotes). The
key field is Quote ID. I have another table with specific detail relating to
the quotation records (Quote Details). This detail table can have 0, 1 or
many records for each Quote ID.

What I'd like to be able to do is create a report which shows the Customer
Quote information, and only the most recent (related) record from Quote
Details.

I'm Ok with creating & linking sub-reports, but need help in getting only
the most recent record from the detail file. Any help you can give would be
most appreciated.

Use a criteria for the detail date field that retrieves the
latest date:

DMax("detaildatefield", "detailtable", "[Quote ID]=" &
detailtable.[QuoteID])

 

[Guest]

Thanks for you reply Marshall. As I am a novice, can I just confirm with you
that I should put this criteria code in the query that joins the two tables
and is source for the report? And also confirm that this will give me all my
main quote records, and only the most recent quote detail record for each of
the main quote records? I only ask as I'm never sure that I explain myself
properly.

Many thanks, as always, for your time & patience.

Sue

I have a table with various info re: quotation files (Customer Quotes). The
key field is Quote ID. I have another table with specific detail relating to
the quotation records (Quote Details). This detail table can have 0, 1 or
many records for each Quote ID.

What I'd like to be able to do is create a report which shows the Customer
Quote information, and only the most recent (related) record from Quote
Details.

I'm Ok with creating & linking sub-reports, but need help in getting only
the most recent record from the detail file. Any help you can give would be
most appreciated.

Use a criteria for the detail date field that retrieves the
latest date:

DMax("detaildatefield", "detailtable", "[Quote ID]=" &
detailtable.[QuoteID])

 

[Marshall Barton]

Your understanding is correct.

The only thing about getting all your main quote records is
that using that criteria will not pick up the quotes that do
not have any details.

Be sure to replace the names I made up with the names of
your table and fields.
--
Marsh
MVP [MS Access]

Thanks for you reply Marshall. As I am a novice, can I just confirm with you
that I should put this criteria code in the query that joins the two tables
and is source for the report? And also confirm that this will give me all my
main quote records, and only the most recent quote detail record for each of
the main quote records? I only ask as I'm never sure that I explain myself
properly.

Use a criteria for the detail date field that retrieves the
latest date:

DMax("detaildatefield", "detailtable", "[Quote ID]=" &
detailtable.[QuoteID])

 

[Guest]

Thanks heaps Marshall. I shall test this out today. It's no big deal for me
to make 1 detail record for each main record - it's something that should be
completed anyway.

Cheers, Sue

Your understanding is correct.

The only thing about getting all your main quote records is
that using that criteria will not pick up the quotes that do
not have any details.

Be sure to replace the names I made up with the names of
your table and fields.
--
Marsh
MVP [MS Access]

Thanks for you reply Marshall. As I am a novice, can I just confirm with you
that I should put this criteria code in the query that joins the two tables
and is source for the report? And also confirm that this will give me all my
main quote records, and only the most recent quote detail record for each of
the main quote records? I only ask as I'm never sure that I explain myself
properly.

Sue wrote:
I have a table with various info re: quotation files (Customer Quotes). The
key field is Quote ID. I have another table with specific detail relating to
the quotation records (Quote Details). This detail table can have 0, 1 or
many records for each Quote ID.

What I'd like to be able to do is create a report which shows the Customer
Quote information, and only the most recent (related) record from Quote
Details.

I'm Ok with creating & linking sub-reports, but need help in getting only
the most recent record from the detail file. Any help you can give would be
most appreciated.

Use a criteria for the detail date field that retrieves the
latest date:

DMax("detaildatefield", "detailtable", "[Quote ID]=" &
detailtable.[QuoteID])

 

[Marshall Barton]

Whoa there. Making sure that a record exists is a whole
'nother topic. For the purposes of the report, it's not
necessary for you to make sure that a detail exists for each
main record. In fact it would be a bad thing for the report
to depend on. All we need is to fiddle with the criteria so
it allows for a Null value in the detail's ID field:

DMax("detaildatefield", "detailtable", "[Quote ID]=" &
Nz(detailtable.[QuoteID], 0)) OR Is Null
--
Marsh
MVP [MS Access]

Thanks heaps Marshall. I shall test this out today. It's no big deal for me
to make 1 detail record for each main record - it's something that should be
completed anyway.

Your understanding is correct.

The only thing about getting all your main quote records is
that using that criteria will not pick up the quotes that do
not have any details.

Be sure to replace the names I made up with the names of
your table and fields.

Thanks for you reply Marshall. As I am a novice, can I just confirm with you
that I should put this criteria code in the query that joins the two tables
and is source for the report? And also confirm that this will give me all my
main quote records, and only the most recent quote detail record for each of
the main quote records? I only ask as I'm never sure that I explain myself
properly.


Sue wrote:
I have a table with various info re: quotation files (Customer Quotes). The
key field is Quote ID. I have another table with specific detail relating to
the quotation records (Quote Details). This detail table can have 0, 1 or
many records for each Quote ID.

What I'd like to be able to do is create a report which shows the Customer
Quote information, and only the most recent (related) record from Quote
Details.

I'm Ok with creating & linking sub-reports, but need help in getting only
the most recent record from the detail file. Any help you can give would be
most appreciated.


:
Use a criteria for the detail date field that retrieves the
latest date:

DMax("detaildatefield", "detailtable", "[Quote ID]=" &
detailtable.[QuoteID])

 

[Guest]

Thanks for your time Marshall - and please forgive my ignorance in this - but
I just don't get the logic of the DMAX function when it's used as criteria in
the query. I've in the past used DSUM in Excel tables (just love that
function!). May be it's where I'm putting this DMAX criteria - I just don't
know.

I've put the following expression in the criteria detaildate (QFollowUpDate)
field (in the query design area):

DMax("QFollowUpDate","QuoteFollowUpHistory","[QuoteID]=" &
[QuoteFollowUpHistory].[QuoteID])

QFollowUpDate is the detail date field that I want only to get the most
recent record for each QuoteID
QuoteFollowUpHistory is the detail record table

But when I try to view the query in datasheet view, I get the error message:

'The expression you entered as a query parameter produced this error:
'Microsoft Access can't find the name '211004ABCDE' you entered in the
expression.'

21004ABCDE is a quote ID

I'm obviously doing something really stupid 'cause I just don't "get it".

Sorry - hope you can bear with me for a bit longer and try and explain this
to me.

Sue

Whoa there. Making sure that a record exists is a whole
'nother topic. For the purposes of the report, it's not
necessary for you to make sure that a detail exists for each
main record. In fact it would be a bad thing for the report
to depend on. All we need is to fiddle with the criteria so
it allows for a Null value in the detail's ID field:

DMax("detaildatefield", "detailtable", "[Quote ID]=" &
Nz(detailtable.[QuoteID], 0)) OR Is Null
--
Marsh
MVP [MS Access]

Thanks heaps Marshall. I shall test this out today. It's no big deal for me
to make 1 detail record for each main record - it's something that should be
completed anyway.

Your understanding is correct.

The only thing about getting all your main quote records is
that using that criteria will not pick up the quotes that do
not have any details.

Be sure to replace the names I made up with the names of
your table and fields.


Sue wrote:
Thanks for you reply Marshall. As I am a novice, can I just confirm with you
that I should put this criteria code in the query that joins the two tables
and is source for the report? And also confirm that this will give me all my
main quote records, and only the most recent quote detail record for each of
the main quote records? I only ask as I'm never sure that I explain myself
properly.


Sue wrote:
I have a table with various info re: quotation files (Customer Quotes). The
key field is Quote ID. I have another table with specific detail relating to
the quotation records (Quote Details). This detail table can have 0, 1 or
many records for each Quote ID.

What I'd like to be able to do is create a report which shows the Customer
Quote information, and only the most recent (related) record from Quote
Details.

I'm Ok with creating & linking sub-reports, but need help in getting only
the most recent record from the detail file. Any help you can give would be
most appreciated.


:
Use a criteria for the detail date field that retrieves the
latest date:

DMax("detaildatefield", "detailtable", "[Quote ID]=" &
detailtable.[QuoteID])

 

[Marshall Barton]

The DMax is looking up the latest date for the quote ID.
Using that as a criteria will restrict the query's data to
only that detail (assuming there is only one per day or that
the date includes the time of day).

The reason for the error is because I had thought the ID was
a numeric type field. Since it is a text field, it should
be written this way:

DMax("QFollowUpDate","QuoteFollowUpHistory",
"[QuoteID]='" & [QuoteFollowUpHistory].[QuoteID] & "'")

so that the ID string is enclosed in quotation marks.
--
Marsh
MVP [MS Access]

Thanks for your time Marshall - and please forgive my ignorance in this - but
I just don't get the logic of the DMAX function when it's used as criteria in
the query. I've in the past used DSUM in Excel tables (just love that
function!). May be it's where I'm putting this DMAX criteria - I just don't
know.

I've put the following expression in the criteria detaildate (QFollowUpDate)
field (in the query design area):

DMax("QFollowUpDate","QuoteFollowUpHistory","[QuoteID]=" &
[QuoteFollowUpHistory].[QuoteID])

QFollowUpDate is the detail date field that I want only to get the most
recent record for each QuoteID
QuoteFollowUpHistory is the detail record table

But when I try to view the query in datasheet view, I get the error message:

'The expression you entered as a query parameter produced this error:
'Microsoft Access can't find the name '211004ABCDE' you entered in the
expression.'

21004ABCDE is a quote ID

Whoa there. Making sure that a record exists is a whole
'nother topic. For the purposes of the report, it's not
necessary for you to make sure that a detail exists for each
main record. In fact it would be a bad thing for the report
to depend on. All we need is to fiddle with the criteria so
it allows for a Null value in the detail's ID field:

DMax("detaildatefield", "detailtable", "[Quote ID]=" &
Nz(detailtable.[QuoteID], 0)) OR Is Null

 

[Guest]

Dear Marshall,

You are a gem! Thank you so much again for your time & patience.
I still need to sit down and work through the expression so that I fully
understand the syntax of the function - but it worked!

The DMax is looking up the latest date for the quote ID.
Using that as a criteria will restrict the query's data to
only that detail (assuming there is only one per day or that
the date includes the time of day).

The reason for the error is because I had thought the ID was
a numeric type field. Since it is a text field, it should
be written this way:

DMax("QFollowUpDate","QuoteFollowUpHistory",
"[QuoteID]='" & [QuoteFollowUpHistory].[QuoteID] & "'")

so that the ID string is enclosed in quotation marks.
--
Marsh
MVP [MS Access]

Thanks for your time Marshall - and please forgive my ignorance in this - but
I just don't get the logic of the DMAX function when it's used as criteria in
the query. I've in the past used DSUM in Excel tables (just love that
function!). May be it's where I'm putting this DMAX criteria - I just don't
know.

I've put the following expression in the criteria detaildate (QFollowUpDate)
field (in the query design area):

DMax("QFollowUpDate","QuoteFollowUpHistory","[QuoteID]=" &
[QuoteFollowUpHistory].[QuoteID])

QFollowUpDate is the detail date field that I want only to get the most
recent record for each QuoteID
QuoteFollowUpHistory is the detail record table

But when I try to view the query in datasheet view, I get the error message:

'The expression you entered as a query parameter produced this error:
'Microsoft Access can't find the name '211004ABCDE' you entered in the
expression.'

21004ABCDE is a quote ID

Whoa there. Making sure that a record exists is a whole
'nother topic. For the purposes of the report, it's not
necessary for you to make sure that a detail exists for each
main record. In fact it would be a bad thing for the report
to depend on. All we need is to fiddle with the criteria so
it allows for a Null value in the detail's ID field:

DMax("detaildatefield", "detailtable", "[Quote ID]=" &
Nz(detailtable.[QuoteID], 0)) OR Is Null

 

[Guest]

I am trying to piggy back on your earlier responses as the situation
parallels mine exactly -- including the fact that I am a relative amateur at
Access.

When I use the following DMax function

DMax("UpdateDate","tblCurrentStatus","ProjectNumber=" &
tblCurrentStatus.[ProjectNumber])

It gives me an error

Syntax error - (missing opperator) in query expression 'ProjectNumber ='

and Access automatically adds [ ] around the latest tblCurrentStatus to make
the expression read

DMax("UpdateDate","tblCurrentStatus","ProjectNumber=" &
[tblCurrentStatus].[ProjectNumber])


ProjectNumber is a number. So if I put in the expression

DMax("UpdateDate","tblCurrentStatus","ProjectNumber= 38") it works fine.
So, somehow, I am referring to the field wrong.

UpdateDate is the date of the most recent changes to the project history
that is contained in the table tblCurrentStatus. ProjectNumber links
tblCurrentStatus to the tblProject which contains the basic information about
the project, such as name, description, assigned people, etc. as well as
links to other tables.

The DMax is looking up the latest date for the quote ID.
Using that as a criteria will restrict the query's data to
only that detail (assuming there is only one per day or that
the date includes the time of day).

The reason for the error is because I had thought the ID was
a numeric type field. Since it is a text field, it should
be written this way:

DMax("QFollowUpDate","QuoteFollowUpHistory",
"[QuoteID]='" & [QuoteFollowUpHistory].[QuoteID] & "'")

so that the ID string is enclosed in quotation marks.
--
Marsh
MVP [MS Access]

Thanks for your time Marshall - and please forgive my ignorance in this - but
I just don't get the logic of the DMAX function when it's used as criteria in
the query. I've in the past used DSUM in Excel tables (just love that
function!). May be it's where I'm putting this DMAX criteria - I just don't
know.

I've put the following expression in the criteria detaildate (QFollowUpDate)
field (in the query design area):

DMax("QFollowUpDate","QuoteFollowUpHistory","[QuoteID]=" &
[QuoteFollowUpHistory].[QuoteID])

QFollowUpDate is the detail date field that I want only to get the most
recent record for each QuoteID
QuoteFollowUpHistory is the detail record table

But when I try to view the query in datasheet view, I get the error message:

'The expression you entered as a query parameter produced this error:
'Microsoft Access can't find the name '211004ABCDE' you entered in the
expression.'

21004ABCDE is a quote ID

Whoa there. Making sure that a record exists is a whole
'nother topic. For the purposes of the report, it's not
necessary for you to make sure that a detail exists for each
main record. In fact it would be a bad thing for the report
to depend on. All we need is to fiddle with the criteria so
it allows for a Null value in the detail's ID field:

DMax("detaildatefield", "detailtable", "[Quote ID]=" &
Nz(detailtable.[QuoteID], 0)) OR Is Null