Access Bank Statement - newbee question

N

newbee question

How can I create a report wich contains two tables, debits and credits,
displayed in date order with a running total. I am using a query to select
the correct records but they display incorrectly. Many thanks in advance.
 
D

Douglas J. Steele

What's incorrect about them? It's hard to offer advice when we don't know
the problem!
 
V

Van T. Dinh

.... and your Table Structure is likely to be incorrect. For this sort of
things, the usual approach is to set up ONE Transaction Table with credits
having positive values and debits negative values (or vice versa provided
that the data is consistently stored). Perhaps, you should also have a
Field for transaction type, e.g. Credit / Debit / Adjustment ...
 
N

newbee

Sorry, I need the table(s) sorted in date order, then merged and inserted
into the report and a running balance calculated. Too late to alter the
design (not mine) the tables are separate. What's the best way to achieve
this in a new report ? Thanks in advance.
 
N

newbee

Too late to change the design now, the tables are separate. The quetion is
how can I display them together in date order and a balance calculated.
 
J

John Vinson

Sorry, I need the table(s) sorted in date order, then merged and inserted
into the report and a running balance calculated. Too late to alter the
design (not mine) the tables are separate. What's the best way to achieve
this in a new report ? Thanks in advance.
Click to expand...

Use a UNION query to combine the data from the two tables:

SELECT AccountNo, TransDate, Amount FROM Debits
UNION ALL
SELECT AccountNo, TransDate, -Amount FROM Credits
ORDER BY TransDate;

John W. Vinson[MVP]
 
T

Tarnia

I don't know if this is what you are looking for, but I would suggest you
create a query that combines the two tables. You can do this by selecting
fields from both tables when designing the query through the Wizard, or
perhaps the previous method of using "union" would be better

Then create a report from the query. This way the tables are combined in
the query and the report through the query

For a balance column, use the Nz function. Create a textbox. Right click
to display the "properties", and under CONTROL SOURCE type in your equation.
I'm not sure what fields yours are, but assming you had payment amount
(credit or deposit) and total charge (debit or withdrawl)
balance=NZ([Payment_Amount])-NZ([Total charge]). Name the textbox. Then
under "running sum" click "over group" and group the
report by account and sort by date. This should give you one table with a
balance sorted by date!

To give credit where credit is due, this is very similar to a post I used by
John W. Vinson[MVP] . Topic was "performing calculations" so maybe check it
out if you don't understand mine/it doesn't work.

Does this help?
Tarnia
 
Top