How do I pull up the last entries of all records for a report?

[StargateFanFromWork]

If I have a subform that tracks files in the office, what do I need to do
when creating a report to get only the last entry in the table for each
record? In other words, the report doesn't need to document all the
history, only the latest information for each for a status report?

Thank you. D

 

[Guest]

Your question is vague in that you want to know the last entry in a table for
each record. Do you mean a record in the table, or does record have some
other meaning to you?
There really is no such thing a "last entry" for a record in a table.
If by record, you mean rows in a table that apply to whatever you are
calling a record, then the best method would be to have a date/time field in
your row that is populated when you create the row; then, you could filter
based on that.

 

[StargateFanFromWork]

Your question is vague in that you want to know the last entry in a table for
each record. Do you mean a record in the table, or does record have some
other meaning to you?
There really is no such thing a "last entry" for a record in a table.
If by record, you mean rows in a table that apply to whatever you are
calling a record, then the best method would be to have a date/time field in
your row that is populated when you create the row; then, you could filter
based on that.

Okay, it's difficult to explain, I realize that - probably because I don't
know the correct terms involved.

I have a form with a tracking subform. So a record from the original table
may have 5 to 25 records associated to it in the tracking subform. I need
to pull the _last_ information from this tracking subform for each main
record in the main table to a report. The history, represented by all the
previous records in the main table, is not needed on this report only the
last one because this is a status report and management needs to know the
latest information for each record in the main table.

Is this a bit more clear? I don't know how else to describe this.

Thank you! D

 

[Guest]

Okay, got it. In the click event of the button you want to populate the
fields on the main form do this:
Me!MySubForm.SetFocus
DoCmd.GoToRecord , , acLast
Me.SetFocus
Me!SomeField = Me!MySubForm!SomeField
...etc...

 

[StargateFanFromWork]

Okay, got it. In the click event of the button you want to populate the
fields on the main form do this:
Me!MySubForm.SetFocus
DoCmd.GoToRecord , , acLast
Me.SetFocus
Me!SomeField = Me!MySubForm!SomeField
...etc...

Hi!

I'm not sure I understand. To get a report showing only the last entry in a
subform plus the description of the main record in the main table, would I
not have to make up a query of some sort? Or would the above do that? I'm
not sure but it seems that we're dealing with the main form and not a
report?? Forgive me if I've misunderstood.

.........................................
I'm thinking that since a 'picture paints a thousand words', that a
screenshot would be easier to deal with in this case. If you would, pls see
this graphic with dummy information:
http://www.angelfire.com/art2/hypatia/Access2000_example_record_with_tracking_subform.gif
that shows one record from the main table along with the tracking subform
information for this record, DocketID # 2194.

The report I need to create would be one that lists all records in the main
table, i.e., dockets #1 to 2400+, whatever # we're at, that are still open.
The report would list by docket # (DocketID) and would have the subject for
each docket from the main table (i.e., "Letter to MP" here), but would take
only the last line from the tracking subform as management wants to know
only the status of each outstanding docket. The difficulty is that the
tracking subform for each docket ID may have as little as 2 lines up to at
least 20 or so. So, as an example, for docket #2194 here, the last entry in
the tracking subform is the one that says docket is with "Heather" and
"given for Director's approval" so for docket 2194, only that line should
appear in the report.

I think this screenshot should help tremendously as it's obvious I'm not
doing a good job of explaining <g>. I didn't know if I could access this
website from the office as they often block these types of sites, so glad
there was no problem. Also, the graphic has mostly bogus information with a
lot of the form erased from this screenshot, so we're okay there, too <g>.

Thanks in advance! D

 

[Guest]

Sorry, forgot about the report part. Yes, you will need to create some sort
of recordset for the report. My previous post will still postion on the data
you want for the report. Perhaps a stored query would do the trick. I don't
know what version of Access you are on. If you are on 2003, then you could
pass the values you need in the WhereCondition argument of the OpenReport
method; otherwise, you could put parameters in the query that would reference
the controls on you form and subform.

 

[StargateFanFromWork]

Sorry, forgot about the report part. Yes, you will need to create some sort
of recordset for the report. My previous post will still postion on the data
you want for the report. Perhaps a stored query would do the trick. I don't
know what version of Access you are on. If you are on 2003, then you could
pass the values you need in the WhereCondition argument of the OpenReport
method; otherwise, you could put parameters in the query that would reference
the controls on you form and subform.

Hi, sorry, forgot to mention I have A2K.

Will give this a try, thanks! D