Display totals on a form or report

[Manuel]

Hello everyone,

I'm trying to create a database in which there is only one table that
contains all of the payables for a company. All the payable invoices have
primarily 3 fields: 'type of cost', 'date due' and 'amount'. The object of
the database is to obtain a printout of expenses categorized by month. It's
supposed to look sort of like this:

Jan Feb Mar TOTAL

Telephone Expenses $xxx $xxx $xxx $xxx
Rent $xxx $xxx $xxx $xxx

I've started to do it as a form where each cell is a subform that has a
query as its data source. After about 65-70 cells, print preview works but
when I go to print it, it tells me that it "can't open any more tables".

Is there a way that anybody can suggest to allow a text box (or anything) on
a form to filter records from a table and display a sum of the values in one
of their fields?

Thank you for your help,
Manuel

 

[Tom Jackson]

You want to investigate the group by clause in a query.
Try searching for group by or aggregate functions.

 

[Ron Weiner]

Manuel

Sounds like a Crosstab query to me:

TRANSFORM Sum(Amount) AS SumOfAmount
SELECT TypeCost, Sum(Amount) AS [Total Amt]
FROM YourTable
WHERE Year([DateDue])=2005
GROUP BY TypeCost
ORDER BY TypeCost
PIVOT Format([DateDue],"mmm")
In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Results look like:
TypeCost Total Amt Jan Feb Mar
xxxxx $73.00 $33.00 $40.00

xxxx $20.00 $20.00


xxx $30.00
$30.00

xxxxxxx $50.00 $50.00






Ron W