Need data that it seems can only be created with a report

[Bob]

I have an access database set up for billing and mailing of customer charge
accts. All of the customers are in one table but each has a unique customer
ID number. All of their purcheses, returns, payments etc are in this table.
My problem is that at the end of the month I must run a report to combine
all of the data on each customer into one amount. This end of month amount
must be added (in this case manually) to the next months table as totals
brought forward. I would like to be able to take these totals directly from
the report and either create another table or add them to the next months
table.

Any help would be appreciated
Bob

 

[Allen Browne]

Bob, there are a couple of ways to approach this.

I will assume that your Transaction table contains positive and negative
values for purchases and payments, so you can sum the Amount field to get a
balance. If not, you will need to use a calculated field that sets the
sign - something like:
IIf([TransactionType] = "Payment", 1, -1) * [Amount]

One simple and completely normalized approach is to calculate the opening
balance for the report as the sum of previous transactions, e.g.:
=DSum("Amount", "Transaction", "TransactionDate < #2/1/2006#")
By placing that into a text box in the Customer ID Header section, you can
end up with a closing balance of:
=[txtOpeningBalance] + Sum([Amount])

Now, if a user goes back and changes a November invoice from $100 to $1000,
this transaction report will give you the new totals. That's both its
strength and its weakness:
- The calculated opening balance is always right.
- The opening balance might not match the closing balance from last time.

If that outcome is undesirable, then you are looking at programmatically
creating and storing batches of invoices for a period (e.g. month.) Store
the opening balance for each statement (customer) in the batch, and lock the
transactions so that a user cannot alter anything (date, amount, ...) on any
purchase/payment that has already become part of an invoice batch.

This second approach involves quite a bit of code, and extra tables, extra
validation to block changes, documentation and training for users. To
implement it you will need to be comfortable with writing VBA code and SQL
statements for appending, and ideally DAO transactions as well. Post a reply
to this thread if you want more info on this approach (not detailed code,
just concepts.)

 

[John Vinson]

I have an access database set up for billing and mailing of customer charge
accts. All of the customers are in one table but each has a unique customer
ID number. All of their purcheses, returns, payments etc are in this table.

You're apparently using spreadsheet logic, rather than a normalized
relational database design. If each customer has multiple purchases,
returns, payments etc. then you essentially MUST have tables in one to
many relationships. Your Customer table should contain only
information *about the customer* - name, contact information, etc.

This table would be related one-to-many to a Purchases table, to a
Returns table, and to a Payments table... etc. These tables would have
a CustomerID as a link, and would have one row for each purchase
(payment, return, etc.)

My problem is that at the end of the month I must run a report to combine
all of the data on each customer into one amount.

Very easily done with a Totals query.

This end of month amount
must be added (in this case manually) to the next months table as totals
brought forward. I would like to be able to take these totals directly from
the report and either create another table or add them to the next months
table.

You should not have a "table per month". Instead, just store the
purchase date (payment date, etc.) in the appropriate table. If you
need to sum the purchases for a month (or a year, or for the
year-to-date, or any other time range) a very simple totals query with
date criteria will do it for you. The total should NOT be stored in
*any* table.


John W. Vinson[MVP]

 

[Bob]

You're apparently using spreadsheet logic, rather than a normalized
relational database design. If each customer has multiple purchases,
returns, payments etc. then you essentially MUST have tables in one to
many relationships. Your Customer table should contain only
information *about the customer* - name, contact information, etc.

This table would be related one-to-many to a Purchases table, to a
Returns table, and to a Payments table... etc. These tables would have
a CustomerID as a link, and would have one row for each purchase
(payment, return, etc.)


Very easily done with a Totals query.


You should not have a "table per month". Instead, just store the
purchase date (payment date, etc.) in the appropriate table. If you
need to sum the purchases for a month (or a year, or for the
year-to-date, or any other time range) a very simple totals query with
date criteria will do it for you. The total should NOT be stored in
*any* table.


John W. Vinson[MVP]

Thanks Allen and John.
I will have to spend a little time checking out your suggestions.
If these are not the answer or I come up with other problems I will be back.

Thanks again
Bob