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

 

[Sylvain Lafontaine]

A subform for each cell? No wonder that you have some problems. Here 2
solutions:

1- create a temporary table and populate it with the required values; you
shouldn't have any print problem by using this temporary table.

2- take a good book on T-SQL and start learning some good stuff; especially
about Group By, Sum() function, subqueries, temporary tables, cursors,
stored procedures, user defined functions and views.

S. L.

 

[Bill Edwards]

If you just want a printout then create a crosstab query and base your
report on it:

From the database window:
Queries->New->Cross Tab Query Wizard->Select your table->
Which fields do you want as row heading (Type Of Cost);
Which fields do you want as column headings (Date Due);
By which interval do you want to group your date time information (Month);
What number do you want calculated for each column and row intersection
(Sum);
Name the query and click the finish command button. Then create a report
based on this query.

 

[Bill Edwards]

Assuming you are using a SQL backend in an adp project then look up
cross-tab reports in SQL server books online. That will describe how to
create the equivalent of a cross tab query using T-SQL.