Summary Report - Ability to Drill Down a line Item Detail

  • Thread starter Thread starter Ron
  • Start date Start date
R

Ron

Summary Report with the ability to Drill Down a line Item for Account Detail.

I’m looking to use a Form, for On-Line Viewing, versus Report to Summarize
Monthly Financial Data by Account. Then Double Click a line Item “Amount†to
get the Detail behind that item….

Example:
EXPENSE SUMMARY REPORT
Account Total Expense
Maintenance – Routine $ 130,235.23
Maintenance – Turnaround 1,231,565.30
Maintenance – Unplanned 845,255.65
Etc
Etc


Goal: When I Double Click a specific “Amount†field, i.e. “1,231,565.30â€,
representing “Total Maintenance – Turnaround†THEN a Query would run to
display the detail behind the $1,231,565.30.

Fields in query:
Account Reference Vendor Check-No Check-Date Description Amount


Criteria:
Lookup the “Account†value in the Selected Line Item From the “Monthly
Summary Record†i.e. “Maintenance – Turnaround†, then use that value as the
“Account†criteria in the query and select all the detail records for the
selected Month.

Can we do this?
I’m looking for some How-To Examples…
 
You could do this...

1. Drag a list control onto the Form and cancel the wizard.

2. In code, on Form_Load.....
lstAccounts.RowSource="SELECT DISTINCT fldAccountNumber,
FORMAT(SUM(fldAccountCost),'currency') FROM tblAccountDetail ORDER BY
fldAccountNumber

3. Repeat step 1

4. In code, on lstAccounts_Click.....
lstAccountDetails.Rowsource="SELECT fldAccountNumber, fldAccountName,
fldVendor, fldCheckNumber, fldAccountCost FROM tblAccountDetail WHERE
(Account=" & (lstAccounts.column(0)) & ")

.....probably with some minor adjustments

Let us know of any problems you might have with this approach.
 
Ron said:
Summary Report with the ability to Drill Down a line Item for Account Detail.

I’m looking to use a Form, for On-Line Viewing, versus Report to Summarize
Monthly Financial Data by Account. Then Double Click a line Item “Amount” to
get the Detail behind that item….

Example:
EXPENSE SUMMARY REPORT
Account Total Expense
Maintenance – Routine $ 130,235.23
Maintenance – Turnaround 1,231,565.30
Maintenance – Unplanned 845,255.65
Etc
Etc


Goal: When I Double Click a specific “Amount” field, i.e. “1,231,565.30”,
representing “Total Maintenance – Turnaround” THEN a Query would run to
display the detail behind the $1,231,565.30.

Fields in query:
Account Reference Vendor Check-No Check-Date Description Amount


Criteria:
Lookup the “Account” value in the Selected Line Item From the “Monthly
Summary Record” i.e. “Maintenance – Turnaround” , then use that value as the
“Account” criteria in the query and select all the detail records for the
selected Month.
Click to expand...



Sure. Use the DoubleClick event to open a details form.
The form's record source table or query should display all
the details for all accounts. The line of code to open the
form would use the OpenForm method's WhereCondition argument
to filter to the specific account:

DoCmd.OpenForm "detailsform", _
WhereCondition:= "Account=" & Me.Account
 
don't forget to put " at the end of each RowSource SELECT statement (I forgot
to) and adjust you listBox column counts in each listbox properties windows.
 
correction...Point 2....
lstAccounts.RowSource="SELECT fldAccountNumber,
FORMAT(SUM(fldAccountCost),'currency') FROM tblAccountDetail GROUP BY
fldAccountNumber ORDER BY fldAccountNumber;"
 
Back
Top