Get LastDate from Continuous form into Report

[Leen]

Hi there,



Here are my specs:

* Access 2010
* MainFrm w/ 5 tabbed subforms, among which is one continuous form

Printing a Report works perfectly fine, until I add a Last Date from my continuous form. I try to do that through a query, with "Totals" selected, and the Last Date set to First, descending.

When a client has 20 dates in the record, indeed 20 Report pages show up.

How can I change that? What steps do I need to take to make that happen?

Thanks very much beforehand!

[June7]

Maybe need to apply filter to report. Maybe need to use domain aggregate function (DMax, DLookup, DSum, etc). Maybe need to use a subreport.

Just don't know enough about data structure and report design. If you want to provide db for analysis, follow instructions at bottom of my post. Identify objects involved in issue.

[Leen]

Maybe need to apply filter to report. Maybe need to use domain aggregate function (DMax, DLookup, DSum, etc). Maybe need to use a subreport.

Just don't know enough about data structure and report design. If you want to provide db for analysis, follow instructions at bottom of my post. Identify objects involved in issue.

Thanks for your quick reply!

[June7]

Which report? What should I look for? The two reports appear identical in design.

PatientRecordRpt triggers "No current record" message.

PatientSelectCharQry has a dynamic parameter input popup - I NEVER use dynamic parameterized queries. If I remove the parameter I get the "No current record" message because of the GROUP BY criteria - why use that?

[Leen]

Which report? What should I look for? The two reports appear identical in design.

PatientRecordRpt triggers "No current record" message.

PatientSelectCharQry has a dynamic parameter input popup - I NEVER use dynamic parameterized queries. If I remove the parameter I get the "No current record" message.

There are indeed 2 reports: one for direct printing of the patient record showing, the second one for looking up (and printing) patient files through a parameter for first letter of last name (sorted).

i indeed noticed PatientRecordRpt not finding records when not showing on screen.

[June7]

One report can serve both purposes.

I would have an unbound control (maybe a combobox) on form for selection of patient ID and reference that control to provide criteria for report. Open report with code behind a button very similar to the button for the current record report.

That same Unbound control can be used to search for record on form.

[Leen]

One report can serve both purposes.

I would have an unbound control (maybe a combobox) on form for selection of patient ID and reference that control to provide criteria for report. Open report with code behind a button very similar to the button for the current record report.

That same Unbound control can be used to search for record on form.

I am familiar with setting up a combobox, but am not sure how I can accomplish the steps mentioned above. And will that also result in getting the most recent VisitDate in the report, w/o seeing all these pages?
Last but not least, would you be so kind to give me a step by step approach?

Thanks; I feel already a lot better (searching for this for days on the internet w/o any result)

[June7]

What do you not understand about using the unbound combobox to input search criteria and applying the input to filter report? It is not much different from what you have to open the report filtered by form current record PatientID. Review http://www.allenbrowne.com/ser-62.html

Retrieving the most recent VisitDate can be done by query. Build an aggregate query that retrieves the maximum visit date for each patient then join that query to Patient table. Example of an all-in-one SQL:

SELECT Patient.*, MaxDate FROM Patient INNER JOIN (SELECT PatientID, Max([VisitDate]) As MaxDate FROM Visit GROUP BY PatientID) As LastVisit ON LastVisit.PatientID=Patient.PatientID;

or domain aggregate function:

SELECT Patient.*, DMax("VisitDate", "Visit", "PatientID=" & [PatientID]) AS MaxDate FROM Patient;

Or do the DMax in a textbox on report: =DMax("VisitDate", "Visit", "PatientID=" & [PatientID])

Domain aggregate functions can perform slowly.

[Leen]

What do you not understand about using the unbound combobox to input search criteria and applying the input to filter report? It is not much different from what you have to open the report filtered by form current record PatientID. Review http://www.allenbrowne.com/ser-62.html

Retrieving the most recent VisitDate can be done by query. Build an aggregate query that retrieves the maximum visit date for each patient then join that query to Patient table. Example of an all-in-one SQL:

SELECT Patient.*, MaxDate FROM Patient INNER JOIN (SELECT PatientID, Max([VisitDate]) As MaxDate FROM Visit GROUP BY PatientID) As LastVisit ON LastVisit.PatientID=Patient.PatientID;

or domain aggregate function:

SELECT Patient.*, DMax("VisitDate", "Visit", "PatientID=" & [PatientID]) AS MaxDate FROM Patient;

Or do the DMax in a textbox on report: =DMax("VisitDate", "Visit", "PatientID=" & [PatientID])

Domain aggregate functions can perform slowly.

Alright, I am going to give it a try.
I will get back to you shortly.
Thanks again very much for your help!

[Leen]

Alright, I am going to give it a try.
I will get back to you shortly.
Thanks again very much for your help!

Hi again,

I tried the DMax in the textbox, but the results are exactly the same as before, namely I see all the patients' visits pages; that gives a problem if I want to print out (for example) all Pts starting lastnames with a q; If I leave the visitDate out, if perfectly gives me the record with the last date ONLY.

Also, I don't know how to "join a query" to the patient table, resulting in the SQL following.

[June7]

Use query builder. Select query from list just like selecting table.

I suspect your reports will be more successful if the related 'many' side related tables are in subreports. Don't think I've ever seen a report with multiple 'many' side tables in one RecordSource work. Note that there are only 3 patients but because of the multiple 'many' tables, the report dataset lists patients more than once.

[Leen]

Use query builder. Select query from list just like selecting table.

I suspect your reports will be more successful if the related 'many' side related tables are in subreports. Don't think I've ever seen a report with multiple 'many' side tables in one RecordSource work. Note that there are only 3 patients but because of the multiple 'many' tables, the report dataset lists patients more than once.

Hi June7,

Thank you for responding quickly!
I am not sure if this is the case, because the reports are printing all the visits of that particular patient. So Mrs. X has visited 11 times, 11 pages show up in the report; The first date being the right date, the second is "doubled" (it seems to be adding up) into something like 04-23-2136, UNLESS I take the VisitDate out of the query; then it only shows the last, or most recent date/report page.

I am wondering if there is a way to take this VisitDate "out of" the continuous form and put it (temporarily) in a table, (or non-continuous form) writing it to the report from there? Just thinking...

[Leen]

Hi June7,

Thank you for responding quickly!
I am not sure if this is the case, because the reports are printing all the visits of that particular patient. So Mrs. X has visited 11 times, 11 pages show up in the report; The first date being the right date, the second is "doubled" (it seems to be adding up) into something like 04-23-2136, UNLESS I take the VisitDate out of the query; then it only shows the last, or most recent date/report page.

I am wondering if there is a way to take this VisitDate "out of" the continuous form and put it (temporarily) in a table, (or non-continuous form) writing it to the report from there? Just thinking...

Hi again,

I have been working for hours to find the solution, and I believe I have come really close.
However, there is still a problem that I don't seem to be able to overcome.

Here is what I did:

I removed all the VisitDate/Items (Continuous form) information from the query.
Then I put the following code in the unbound text boxes:

=DLast("VisitDate","Visit","PatientID=" & [PatientID])

=DLast("Item1","Visit","PatientID=" & [PatientID]) ‘ etc.

When I want to print the searched patient (one page), it gives the perfect date.
However, when I want to find all patients whose lastname starts e.g. with a “q”, the first page gives the right date, the second one is 01-23-2104, and the third one: 09-18-2225, etc.


I am at a loss; I’ve come this far, but I need your help once more. Could you solve this?

Thank you very much!

[June7]

I am also at a loss. As stated, don't think I've ever encountered a query/report structured this way.

I still think more successful approach may be subreports.

[Leen]

I am also at a loss. As stated, don't think I've ever encountered a query/report structured this way.

I still think more successful approach may be subreports.

Dear June7,

Thank you so much for your response!

I can see the reasoning behind subreports; however, I believe that even subreports would not solve the problem w/ the continuous form.
Nonetheless, I am very grateful for your input and help.

Again, thank you very much.

Leen