I have an invoice summary table with the fields Invoice#, CustomerID, EmployeeID, InvoiceDate and InvoiceAmount.
I want to show in a report only the top 3 InvoiceAmount for each customer. Is this possible? Do I need to build the report based on a query or filter the report when it is printed?
I would create a "top values" query. Then have that query feed into your report. Look in the Access help system for "Show only the high or low values in a query".
Assuming the table is called "tblInvoice", you can do it in two ways.
Method 1. Base your report on a query as follows:-
SELECT *
FROM tblInvoice AS a
WHERE (Select Count(*) from tblInvoice where CustomerID=a.CustomerID and InvoiceAmount>=a.InvoiceAmount) in (1,2,3)
ORDER BY CustomerID, InvoiceAmount DESC;
Method 2. Copy the structure of table tblInvoice to a temporary table "tblTemp". Base your report on the temporary table. Then run the suitable version of VBA code below e.g. from a command button on a form:-
Access 97 using DAO:
Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQL As String
Set db = CurrentDb
SQL = "Delete * from tblTemp"
db.Execute SQL
SQL = "Select distinct CustomerID from tblInvoice"
Set rs = db.OpenRecordset(SQL)
Do While Not rs.EOF
SQL = "Insert Into tblTemp" & _
" Select Top 3 * from tblInvoice" & _
" where CustomerID='" & rs!CustomerID & "'" & _
" order by InvoiceAmount DESC"
db.Execute SQL
rs.MoveNext
Loop
Set db = Nothing
Set rs = Nothing
DoCmd.OpenReport "rptTop3", acViewPreview
Access 2000, 2002 using ADO:
Code:
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Set cnn = CurrentProject.Connection
SQL = "Delete * from tblTemp"
cnn.Execute SQL
SQL = "Select distinct CustomerID from tblInvoice"
Set rs = New ADODB.Recordset
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
Do While Not rs.EOF
SQL = "Insert Into tblTemp" & _
" Select Top 3 * from tblInvoice" & _
" where CustomerID='" & rs!CustomerID & "'" & _
" order by InvoiceAmount DESC"
cnn.Execute SQL
rs.MoveNext
Loop
Set cnn = Nothing
Set rs = Nothing
DoCmd.OpenReport "rptTop3", acViewPreview
Notes:
Method 1 uses a subquery to rank the records of each CustomerID and retrieve records ranked as 1,2 or 3. Running a subquery may take time if the table is fairly large or the system is slow.
Method 2 uses VBA code to append records to a temporary table. This method is much faster. The code assumes CustomerID is a text field so its value is surrounded by single quotes in the SQL statement. If it is a numeric field, you can remove the quotes from the Where Clause:
" where CustomerID=" & rs!CustomerID & _
Your first method will get the desired results, however i think (no fact mind you) below query will be less of a demand on the system.
SELECT *
FROM tblInvoice AS a
WHERE a.InvoiceID in (Select top 3 InvoiceID from tblInvoice where CustomerID=a.CustomerID
Order by InvoiceAmount DESC)
ORDER BY CustomerID, InvoiceAmount DESC;
I tested the two queries in an Access 2000 database on a system running Windows Me. When retrieving 60 records for 20 CustomerIDs out of a table of 20,000 records, both your query and mine took roughly the same time to run (2 minutes plus a few seconds).
The VBA code is real fast. In the same database, both the DAO code and the ADO code took less than 1 second to return the same 60 records and open a report.
So, I think, where performance speed is concerned, VBA code is preferable to subqueries.
