The Database is ideal for storing information, but Spreadsheet is the best place to perform calculations. Using the database links, you can combine these tools in an efficient and powerful way. For an example of how to make links to database, see Linking from a spreadsheet to a database. The notes below show how the example can be refined.
The number of matching records can be calculated in advance, using the following formula:
=ROWS(DBSQLFILTER("accounts.adb", "invoices", "custid=1024", "invtotal"))
If there are many matching invoices, the ROW function can be used to propagate the formula. For example, supposing record one was entered in row 10, you could use the following:
=DBSQLFILTER("accounts.adb", "invoices", "custid=1024", "invtotal", ROW()-9)
When this cell is copied down the spreadsheet, the appropriate record is displayed.
It could be more efficient if some of the parameters are entered separately in spreadsheet cells. For example, cells A1 through A5 could contain the following text/formulae:
Once A5 is copied down and across (with the correct field names), you can enter a new customer ID in cell A3 and analyze a different customer.
Taking step 3 a bit further, you could insert a mail merge field that will step through each customer and so print out a "payment history". To do this follow these steps:
Select Field from the Insert menu.
Enter custfield as the field name. This is actually the name of the new field, not the database field to be linked to, so you can choose any name you like.
Select the Database radio button and then click Next.
Make the link: pick the accounts database, the invoice table and the custid field and click Finish.
Substitute the following formula for cell A4 (in step 3 above):
=SSGET("spread1", "custfield")
where spread1 is the name of the current spreadsheet and custfield is the new field.
Select the Mail Merge button or the Mail Merge button from the Tools menu.
Select Print to finish the mail merge.