Need help Please.

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

I am working on making a database that is keeping track
of company accounts an keeping track of expenses, and
deposits. I am trying to show a balance on each entry
that is made in the account registry. I have it working
so fare but one problem. When I inter data on the same
day, it adds all the ones on the same day, and show the
balance of the total that it added, for every day.

exp: what I want

11.10.2003 Deposit $500.00 B $500.00
11.12.2003 Expense $30.00 B $470.00
11.12.2003 Expense $20.00 B $450.00
11.17.2003 Expense $50.00 B $400.00

This is what it is doing.


11.10.2003 Deposit $500.00 B $500.00
11.12.2003 Expense $30.00 B $450.00 -
11.12.2003 Expense $20.00 B $450.00 -
11.17.2003 Expense $50.00 B $400.00

This is the Expression I am using:

=DSum("0+[Deposit]-[Payment]","Transactions","[TraDate]
<= [Forms]![Accounts]![Transactions subform].FORM!
[TraDate] and [Forms]![Accounts]![Account ID]=[Account
ID]")

If I go by the [Transaction ID] that is ascending then
the Balances will be scattered, And the dates will not
line up.

Is it posible to make the expression look at the
[Transaction ID] when it comes across the same Date
[TraDate]. OR Is it a way to rewrite the code to look at
eachrecord above or not do a DSum and just let it grab
the Value. Does any body have an Idea on how I can fix
this. I thought of another way I don't know if it will
work. I could make an invisible field were it counts the
records and it puts a 1 at the top record and goes down
with a 2 and 3 and 4 till it hits the last record. and
when I add a record, no matter were the record go's it
will add a number for it, but it will realign the number
from 1,2,3,4,5 all the way to the last record. And I
could have the Balance EXpression point to that value. Is
that possible or could there be an easy way of doing it.
I just need to have all the dates line up.

Thanks, Andrew
 
Considering that DSum is _define_ to total the field you specify, then it
would seem that your application is working as intended. Perhaps you did not
realize that is what it did, so you thought you intended something
different.

You have given a lot of information, but not what is really necessary for us
to help you -- get rid of the extraneous information, and your speculation
on what you might do.

Tell us what information you have, how you have stored it in what tables,
and what, exactly, you are trying to accomplish and whether you are trying
to accomplish it in a form, a report, or in a query. If you give us a
formula, it would be nice to know whether that is in VBA code, or a Control
Source, or ??? Oh, yes, one other thing "show a balance on each entry that
is made in the account registry" -- what do you mean... do you want to show
each entry, and the sum of that and previous entries for the day, or ??? --
this might be suitable for "Running Sum" if it is in a report.

Clarify in a followup, and perhaps someone can offer a worthwhile
suggestion.

Larry Linson
Microsoft Access MVP


Andrew said:
I am working on making a database that is keeping track
of company accounts an keeping track of expenses, and
deposits. I am trying to show a balance on each entry
that is made in the account registry. I have it working
so fare but one problem. When I inter data on the same
day, it adds all the ones on the same day, and show the
balance of the total that it added, for every day.

exp: what I want

11.10.2003 Deposit $500.00 B $500.00
11.12.2003 Expense $30.00 B $470.00
11.12.2003 Expense $20.00 B $450.00
11.17.2003 Expense $50.00 B $400.00

This is what it is doing.


11.10.2003 Deposit $500.00 B $500.00
11.12.2003 Expense $30.00 B $450.00 -
11.12.2003 Expense $20.00 B $450.00 -
11.17.2003 Expense $50.00 B $400.00

This is the Expression I am using:

=DSum("0+[Deposit]-[Payment]","Transactions","[TraDate]
<= [Forms]![Accounts]![Transactions subform].FORM!
[TraDate] and [Forms]![Accounts]![Account ID]=[Account
ID]")

If I go by the [Transaction ID] that is ascending then
the Balances will be scattered, And the dates will not
line up.

Is it posible to make the expression look at the
[Transaction ID] when it comes across the same Date
[TraDate]. OR Is it a way to rewrite the code to look at
eachrecord above or not do a DSum and just let it grab
the Value. Does any body have an Idea on how I can fix
this. I thought of another way I don't know if it will
work. I could make an invisible field were it counts the
records and it puts a 1 at the top record and goes down
with a 2 and 3 and 4 till it hits the last record. and
when I add a record, no matter were the record go's it
will add a number for it, but it will realign the number
from 1,2,3,4,5 all the way to the last record. And I
could have the Balance EXpression point to that value. Is
that possible or could there be an easy way of doing it.
I just need to have all the dates line up.

Thanks, Andrew
Click to expand...
 
Back
Top