Help using Top Values or DMAX for a report

[Guest]

Context of Db:
I am tracking Audits, Findings (multiple per audit), MCAs (multiple per
finding), and CAPRs (multiple per MCA). In english these are audit findings,
the associated management corrective actions (MCAs) and Corrective Action
Plans/Responses (CAPRs). CAPRs are updated monthly in a continous form.

Issue:
I have a report based on a query (see query SQL below). Two of the many
returned fields are the CAPR (a memo) field and the associated date field.
Both fields are stored in the CAPR table.

Currently the report returns all CAPRs for EACH MCA matching the criteria of
the query (e.g. if there were three CAPRs for one MCA it would look like):
Audit #1 Finding #1 MCA #1 CAPR # 1(most recent)
Audit #1 Finding #1 MCA #1 CAPR # 2(second most recent)
Audit #1 Finding #1 MCA #1 CAPR # 3(oldest)

I need the report to show the information for ONLY the most recent CAPR memo
and associated date for EACH MCA matching the criteria of the query. (e.g.):
Audit #1 Finding #1 MCA #1 CAPR # 1(most recent)
then show the next result based on the query (e.g.):
Audit #1 Finding #1 MCA #2 CAPR # 1(most recent)

I have tried Top Values (using "1") unsuccessfully - It tends to return the
first CAPR of any record into all the returned results. Or, using a
subreport, it reduces the whole report to one line item.

I have no luck (skill) with the DMax function - mostly syntax errors.

Any help would be appreciated.

SQL of query sans any Top Values filtering:
SELECT Audits.OfficialAuditNumber, Audits.AuditTitle,
Audits.AuditDescription, Audits.AuditDate, Audits.AuditOrg, Audits.Auditor,
Audits.AuditStatus, Findings.OfficialFindingNumber, Findings.FindingDesc,
Findings.RiskRating, Findings.InitialDetermination, Findings.FindingStatus,
MCA.OfficialMCANumber, MCA.RespDir, MCA.RespDept, MCA.RespDiv, MCA.RespGroup,
MCA.RespPerson, MCA.DueDate, MCA.MCAStatus, CAPR.CAPR, CAPR.CAPRDate
FROM ((Audits LEFT JOIN Findings ON Audits.SysGenAuditNumber =
Findings.AuditNumber) LEFT JOIN MCA ON Findings.SysGenFindingNumber =
MCA.FindingNumber) LEFT JOIN CAPR ON MCA.SysGenMCANumber = CAPR.MCANumber
WHERE (((Audits.AuditOrg) Like "*" & [Audit Org] & "*") AND ((MCA.RespDiv)
Like "*" & [Responsible Division] & "*") AND ((MCA.MCAStatus) Like "*" & [MCA
Status] & "*"))
ORDER BY Audits.OfficialAuditNumber, Findings.OfficialFindingNumber,
MCA.OfficialMCANumber, CAPR.CAPRDate DESC;

Thanks,

 

[Duane Hookom]

This sounds like the perfect report to use subreports. You can limit a
subreport to a single record if you wish. Whatever level of detail you put
in the subreport record source, you need to remove from the main report.

 

[Guest]

I am still having no luck but might be a little closer. I built a sub report
based on a query of only the CAPR and CAPR Date fields and the Top Values=1.
I then made a report of just those two fields and made that a sub report in
the main report. I removed those two field from the main report and the
query feeding the main report. Now when I run the report, I am getting the
most recent CAPR update verbiage across all audits, findings, and MCAs that
match the criteria. I feel I am a step closer because what I need is the
latest CAPR from each MCA to appear for each MCA fitting the description.

Thanks for the help on this.

This sounds like the perfect report to use subreports. You can limit a
subreport to a single record if you wish. Whatever level of detail you put
in the subreport record source, you need to remove from the main report.
--
Duane Hookom
MS Access MVP

Context of Db:
I am tracking Audits, Findings (multiple per audit), MCAs (multiple per
finding), and CAPRs (multiple per MCA). In english these are audit
findings,
the associated management corrective actions (MCAs) and Corrective Action
Plans/Responses (CAPRs). CAPRs are updated monthly in a continous form.

Issue:
I have a report based on a query (see query SQL below). Two of the many
returned fields are the CAPR (a memo) field and the associated date field.
Both fields are stored in the CAPR table.

Currently the report returns all CAPRs for EACH MCA matching the criteria
of
the query (e.g. if there were three CAPRs for one MCA it would look like):
Audit #1 Finding #1 MCA #1 CAPR # 1(most recent)
Audit #1 Finding #1 MCA #1 CAPR # 2(second most recent)
Audit #1 Finding #1 MCA #1 CAPR # 3(oldest)

I need the report to show the information for ONLY the most recent CAPR
memo
and associated date for EACH MCA matching the criteria of the query.
(e.g.):
Audit #1 Finding #1 MCA #1 CAPR # 1(most recent)
then show the next result based on the query (e.g.):
Audit #1 Finding #1 MCA #2 CAPR # 1(most recent)

I have tried Top Values (using "1") unsuccessfully - It tends to return
the
first CAPR of any record into all the returned results. Or, using a
subreport, it reduces the whole report to one line item.

I have no luck (skill) with the DMax function - mostly syntax errors.

Any help would be appreciated.

SQL of query sans any Top Values filtering:
SELECT Audits.OfficialAuditNumber, Audits.AuditTitle,
Audits.AuditDescription, Audits.AuditDate, Audits.AuditOrg,
Audits.Auditor,
Audits.AuditStatus, Findings.OfficialFindingNumber, Findings.FindingDesc,
Findings.RiskRating, Findings.InitialDetermination,
Findings.FindingStatus,
MCA.OfficialMCANumber, MCA.RespDir, MCA.RespDept, MCA.RespDiv,
MCA.RespGroup,
MCA.RespPerson, MCA.DueDate, MCA.MCAStatus, CAPR.CAPR, CAPR.CAPRDate
FROM ((Audits LEFT JOIN Findings ON Audits.SysGenAuditNumber =
Findings.AuditNumber) LEFT JOIN MCA ON Findings.SysGenFindingNumber =
MCA.FindingNumber) LEFT JOIN CAPR ON MCA.SysGenMCANumber = CAPR.MCANumber
WHERE (((Audits.AuditOrg) Like "*" & [Audit Org] & "*") AND ((MCA.RespDiv)
Like "*" & [Responsible Division] & "*") AND ((MCA.MCAStatus) Like "*" &
[MCA
Status] & "*"))
ORDER BY Audits.OfficialAuditNumber, Findings.OfficialFindingNumber,
MCA.OfficialMCANumber, CAPR.CAPRDate DESC;

Thanks,

 

[Duane Hookom]

Did you set or review the Link Master/Child properties of the subreport
control?
I would remove the "Top values=1" from the query. Add a text box to the
detail section of your subreport:

Name: txtCount
Control Source: =1
Running Sum: Over All
Visible: No

Then add code to the On Format event of the subreport detail section:
Cancel = Me.txtCount>1

This will limit the display in the subreport to the first record based on
its sorting.

--
Duane Hookom
MS Access MVP

I am still having no luck but might be a little closer. I built a sub
report
based on a query of only the CAPR and CAPR Date fields and the Top
Values=1.
I then made a report of just those two fields and made that a sub report
in
the main report. I removed those two field from the main report and the
query feeding the main report. Now when I run the report, I am getting
the
most recent CAPR update verbiage across all audits, findings, and MCAs
that
match the criteria. I feel I am a step closer because what I need is the
latest CAPR from each MCA to appear for each MCA fitting the description.

Thanks for the help on this.

This sounds like the perfect report to use subreports. You can limit a
subreport to a single record if you wish. Whatever level of detail you
put
in the subreport record source, you need to remove from the main report.
--
Duane Hookom
MS Access MVP

Context of Db:
I am tracking Audits, Findings (multiple per audit), MCAs (multiple per
finding), and CAPRs (multiple per MCA). In english these are audit
findings,
the associated management corrective actions (MCAs) and Corrective
Action
Plans/Responses (CAPRs). CAPRs are updated monthly in a continous
form.

Issue:
I have a report based on a query (see query SQL below). Two of the
many
returned fields are the CAPR (a memo) field and the associated date
field.
Both fields are stored in the CAPR table.

Currently the report returns all CAPRs for EACH MCA matching the
criteria
of
the query (e.g. if there were three CAPRs for one MCA it would look
like):
Audit #1 Finding #1 MCA #1 CAPR # 1(most recent)
Audit #1 Finding #1 MCA #1 CAPR # 2(second most recent)
Audit #1 Finding #1 MCA #1 CAPR # 3(oldest)

I need the report to show the information for ONLY the most recent CAPR
memo
and associated date for EACH MCA matching the criteria of the query.
(e.g.):
Audit #1 Finding #1 MCA #1 CAPR # 1(most recent)
then show the next result based on the query (e.g.):
Audit #1 Finding #1 MCA #2 CAPR # 1(most recent)

I have tried Top Values (using "1") unsuccessfully - It tends to return
the
first CAPR of any record into all the returned results. Or, using a
subreport, it reduces the whole report to one line item.

I have no luck (skill) with the DMax function - mostly syntax errors.

Any help would be appreciated.

SQL of query sans any Top Values filtering:
SELECT Audits.OfficialAuditNumber, Audits.AuditTitle,
Audits.AuditDescription, Audits.AuditDate, Audits.AuditOrg,
Audits.Auditor,
Audits.AuditStatus, Findings.OfficialFindingNumber,
Findings.FindingDesc,
Findings.RiskRating, Findings.InitialDetermination,
Findings.FindingStatus,
MCA.OfficialMCANumber, MCA.RespDir, MCA.RespDept, MCA.RespDiv,
MCA.RespGroup,
MCA.RespPerson, MCA.DueDate, MCA.MCAStatus, CAPR.CAPR, CAPR.CAPRDate
FROM ((Audits LEFT JOIN Findings ON Audits.SysGenAuditNumber =
Findings.AuditNumber) LEFT JOIN MCA ON Findings.SysGenFindingNumber =
MCA.FindingNumber) LEFT JOIN CAPR ON MCA.SysGenMCANumber =
CAPR.MCANumber
WHERE (((Audits.AuditOrg) Like "*" & [Audit Org] & "*") AND
((MCA.RespDiv)
Like "*" & [Responsible Division] & "*") AND ((MCA.MCAStatus) Like "*"
&
[MCA
Status] & "*"))
ORDER BY Audits.OfficialAuditNumber, Findings.OfficialFindingNumber,
MCA.OfficialMCANumber, CAPR.CAPRDate DESC;

Thanks,