Having problems with an account registry and date

  • 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","[Date] <=
[Forms]![Accounts]![Transactions subform].FORM![Date] and
[Forms]![Accounts]![Account ID]=[Account ID]")

I have the subform Ascending by [date], that is the
reason I am pointing it to [date]. Reason: I want every
thing in order by date, but I am using random "Auto
Numbers" so that more than one person can work on the
account at the same time.
Now I thought of one way to fix the problem is make
another subform that is not visible and point it to the
[Transaction ID] and not to [Date]. But I don't know how
to copy data from one subform to another subform on the
same form by using the [Transaction ID]. Does any body
have a better way of doing it.

Another thing, does any body know how when the form is
open, to have the subform pointed to the last record.

Thanks, Andrew
 
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","[Date] <=
[Forms]![Accounts]![Transactions subform].FORM![Date] and
[Forms]![Accounts]![Account ID]=[Account ID]")

I have the subform Ascending by [date], that is the
reason I am pointing it to [date]. Reason: I want every
thing in order by date, but I am using random "Auto
Numbers" so that more than one person can work on the
account at the same time.
Now I thought of one way to fix the problem is make
another subform that is not visible and point it to the
[Transaction ID] and not to [Date]. But I don't know how
to copy data from one subform to another subform on the
same form by using the [Transaction ID]. Does any body
have a better way of doing it.

Another thing, does any body know how when the form is
open, to have the subform pointed to the last record.

Thanks, Andrew
Click to expand...
 
Hi,

Use a date AND time value... assuming no two transactions occur at the
same exact second, the ordering will be uniquely defined... and sure, I
assume that the DateTIme field can capture the date and its time (supply a
default value = Now( ) in the design of the table).


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top