I am developing an Access application in Access 2003 using Access as the front end and SQL Server as the back end database. I run a stored procedure that has a result set of approximately 43,000 records that are populated into a table named tblDtlBranchAll.
Could the following sample records, which exist in table tblDtlBranchAll be manipulated via a stored procedure or Query to create sorted records with totals and subtotals so that the result set would appear like the output below the dotted lines into another table which I could then be export into an Excel file using DoCmd.Transferspreadsheet ? My users like to see all their output in Excel files since they are Accountants.
Branch Customer Number Date Range Property Type Value
004 111333111 0 To 6 Months STOCKS 21.00
004 111333111 0 To 6 Months BONDS 11.25
004 888333111 0 To 6 Months STOCKS 8.00
004 888333111 0 To 6 Months BONDS 12.25
004 922111111 6 To 12 Months STOCKS 44.00
004 922111111 6 To 12 Months BONDS 22.25
--------------------------
----------
----------
----------
----------
----------
----------
----------
----------
---
The report takes the table records and performs subtotals and totals on the records as follows:
How can I get the results as follows with the subtotals and totals into an Excel file ?
Can a query be used if I am working with an ADP type file ?
The records are sorted by Customer Number within Date Range.
Branch Customer Number Date Range Property Type Value
004 111333111 0 To 6 Months STOCKS 21.00
004 111333111 0 To 6 Months BONDS 11.25
Sub Total 33.25
004 888333111 0 To 6 Months STOCKS 8.00
004 888333111 0 To 6 Months BONDS 12.25
Sub Total 20.25
004 922111111 6 To 12 Months STOCKS 44.00
004 922111111 6 To 12 Months BONDS 22.25
Sub Total 66.25
Total 119.75
Start Free Trial
