How to run a 'total' query

[pwebbiz]

Hi,

I'm relatively new to access and Im using access 2003. I created a
small database in an effort to track how much my friends owe me (just
as a learning experience). I have two tables. 'Debts' and 'Debtors'
The primary key in 'debtors' is used in 'debts' as the refference.

In 'debts' I enter data in this format:

Debtor ID (Primary Key from 'debtors')
Reason
Date
Due
Amount
Paid (checkbox y/n)
On time (checkbox y/n)

In the 'debtor' database I want a field for each debtor called 'Total
Debt'. How can I add up all the 'Amount Due' Fields from the records
that match their primary key refference in 'debts'.

=====

Second

How do I exclude records from a report based on a checkbox? I.e. I
want to print a report of all that owe, therefore the 'paid' checkbox
would not be checked. How can I exlcude records that it is checked
on?

====

Finally,

How can I generate a page of a report for each 'debtor' with each debt
line listed -- I made a 'master' report but im looking to make a
'debtor copy'

Thank you for any and all help!!

 

[John W. Vinson]

In the 'debtor' database I want a field for each debtor called 'Total
Debt'. How can I add up all the 'Amount Due' Fields from the records
that match their primary key refference in 'debts'.

No. You DON'T want this field stored in any table.

Calculate it on demand, and display the calculated field. To create a Totals
query add the table to the grid, and click the Greek Sigma icon (looks like a
sideways M). Group By the Debtor field, Sum the debt.

=====

Second

How do I exclude records from a report based on a checkbox? I.e. I
want to print a report of all that owe, therefore the 'paid' checkbox
would not be checked. How can I exlcude records that it is checked
on?

Use a criterion of

False

on the yes/no field in the query.

====

Finally,

How can I generate a page of a report for each 'debtor' with each debt
line listed -- I made a 'master' report but im looking to make a

Use the Report's Sorting and Grouping dialog to group by debtor. Make the
section header and footer visible; put the debtor information in the header
and set the footer to force a new page.

John W. Vinson [MVP]

 

[pwebbiz]

Calculate it on demand, and display the calculated field. To create a Totals
query add the table to the grid, and click the Greek Sigma icon (looks like a
sideways M). Group By the Debtor field, Sum the debt.

Works like a charm -- my only problem is if I include that it wont let
me include the initial debt amount -- only the total.

Use a criterion of

False

on the yes/no field in the query.

Worked perfect

Use the Report's Sorting and Grouping dialog to group by debtor. Make the
section header and footer visible; put the debtor information in the header
and set the footer to force a new page.

The grouping is no problem, then I put the information in the page
header and it only listed one record on the page -- so far so
good...then I put a page break in the footer and It doesnt make more
pages although there is one more record.

Thanks for the help!

 

[pwebbiz]

The grouping is no problem, then I put the information in the page
header and it only listed one record on the page -- so far so
good...then I put a page break in the footer and It doesnt make more
pages although there is one more record.

Thanks for the help!

FIGURED THAT ONE OUT -- FORCE NEW PAGE

 

[pwebbiz]

*bump*