totals on reports

[Carol Shu]

Hi, just have a question, if Mr. Davis have $500 balance on his charge
account, then he wants to pay $100 on 1st & 15th of each month ($200 a
month), so there will be 5 payments total. so there were 2 payments last
month, 6/1/2009 paid $100 and 6/15/2009 paid $100 (balance=500-200). then I
try to run a report on his account balance. here is my question, how do I
make the Report just only show his current balance $300, instead now it show
all the payment transactions history, and I have over 100 accounts. please
help, many thanks.

 

[Larry Linson]

I can only guess at how you have the data stored, and it is the stored data
that is the basis for the report. In general, you would create a Totals
Query to determine the balance, and use that Query as the Record Source for
your Report. If you want to show detail for a particular period based on
dates and the total, too, then you may wish to use a Report with a
Subreport. But specifics of how to do what you want will depend on the data
you have and the table structure in which you have it stored.

Larry Linson
Microsoft Office Access MVP

 

[Carol Shu]

I HAVE THE TABLE NAME
TRANSACTIONS[TRANSACTIONID][CUSTOMERID][PAYDATE][AMOUNTPAID][PREVIOUS
BALANCE][CURRENT BALANCE]. EACH ACCOUNT CONTAINS MORE THAN ONE TRANSACTIONS,
AS THE BALANCE WILL GOES DOWN AFTER EACH PAYMENTS. AND I ENTER THESE DATA
INTO A FORM FORMAT. I THEN CREATE A REPORT TO SEE THE CURRENT BALANCE WITH A
DATE RANGE, LIKE WHOLE MONTH OF JUNE, IT PRINT OUT ALL THE TRANSACTIONS BY
THE CUSTOMER ID, SOMEHOW I JUST CAN NOT HAVE THE CURRENT BALANCE ON EACH
ACCOUNT, IT SHOWS EACH TRANSACTION'S BALANCE, LIKE 500-400=400 ON 6/1/09,
400-100=300 ON 6/15/09, I WOULD LIKE IT ONLY SHOW THE CURRENT BALANCE $300,
THANKS.

 

[Larry Linson]

You are, as we say in the database world, "comitting spreadsheet". Access
is not just Excel with vastly more storage. What you describe may be a
perfectly good design for a spreadsheet, or for an old-time sequential
process, but for relational database you do not store data that you can
calculate when you need it.

I believe you can use a Totals Query to obtain the most recent transaction
for each customer, carry along the current balance, and use that as the
Record Source of your report. But, at this hour of the night/morning, I'm
not prepared to try to create a sample.

Larry Linson
Microsoft Office Access MVP

I HAVE THE TABLE NAME
TRANSACTIONS[TRANSACTIONID][CUSTOMERID][PAYDATE][AMOUNTPAID][PREVIOUS
BALANCE][CURRENT BALANCE]. EACH ACCOUNT CONTAINS MORE THAN ONE
TRANSACTIONS,
AS THE BALANCE WILL GOES DOWN AFTER EACH PAYMENTS. AND I ENTER THESE DATA
INTO A FORM FORMAT. I THEN CREATE A REPORT TO SEE THE CURRENT BALANCE WITH
A
DATE RANGE, LIKE WHOLE MONTH OF JUNE, IT PRINT OUT ALL THE TRANSACTIONS BY
THE CUSTOMER ID, SOMEHOW I JUST CAN NOT HAVE THE CURRENT BALANCE ON EACH
ACCOUNT, IT SHOWS EACH TRANSACTION'S BALANCE, LIKE 500-400=400 ON 6/1/09,
400-100=300 ON 6/15/09, I WOULD LIKE IT ONLY SHOW THE CURRENT BALANCE
$300,
THANKS.

Hi, just have a question, if Mr. Davis have $500 balance on his charge
account, then he wants to pay $100 on 1st & 15th of each month ($200 a
month), so there will be 5 payments total. so there were 2 payments last
month, 6/1/2009 paid $100 and 6/15/2009 paid $100 (balance=500-200).
then I
try to run a report on his account balance. here is my question, how do I
make the Report just only show his current balance $300, instead now it
show
all the payment transactions history, and I have over 100 accounts.
please
help, many thanks.