Copying a "Totals" Row

[Simon Lang]

Thanks to some earlier help, I now have a simple database up and running with
contacts and an associated ledger.
The ledger has a "totals" row to calculate the current balance of the
contact. I would like the contacts form to have the account balance, sort of
a snapshot without going into the ledger. How do I achieve this?, I've tried
adding existing fields but the "total field" doesn't show up, presumably
because its a calculation rather than a field itself?. How can I copy that
figure over to the contact page, I am going to be delving into queries?, or
will it be a simple copy and paste, thanks in advance

 

[Douglas J. Steele]

The easiest way would be to create a text box and set its ControlSource
property to use the DSum function.

Unfortunately, without knowing more about your tables and how your form is
set up, that's as specific as I can be.

 

[Simon Lang]

many many thanks, I'll give this a go, you've both put me on the right
tracks, thank you again

It depends how the transactions are entered in the Ledger table. If credits
and debits are entered as positive and negative values in a single column
then you simply need to sum the values in that column for the current contact,
so the ControlSource of an unbound text box in your form would be something
like:

=DSum("Amount", "Ledger", "ContactID = " & [ContactID])

If credits and debits are entered as positive numbers in separate columns in
the ledger table then you sum the credits less the debits, e.g.

=DSum("Nz(Credit,0) – Nz(Debit,0)", "Ledger", "ContactID = " & [ContactID])

The Nz function is used here in case you are allowing Nulls in the Debit or
Credit column. If not and the columns each have a DefaultValue property of
zero, you don't need to call the function, so:

=DSum("Credit - Debit", "Ledger", "ContactID = " & [ContactID])

Ken Sheridan
Stafford, England

Thanks to some earlier help, I now have a simple database up and running with
contacts and an associated ledger.
The ledger has a "totals" row to calculate the current balance of the
contact. I would like the contacts form to have the account balance, sort of
a snapshot without going into the ledger. How do I achieve this?, I've tried
adding existing fields but the "total field" doesn't show up, presumably
because its a calculation rather than a field itself?. How can I copy that
figure over to the contact page, I am going to be delving into queries?, or
will it be a simple copy and paste, thanks in advance

 

[Simon Lang]

Hi Ken

it's this first expression I need, just so I understand,

=DSum("Amount", "Ledger", "ContactID = " & [ContactID])

where "Amount" = the column where the figures are added/subtracted

where "Ledger" = the form/table where the amount sits

where the 1st "contacts id = the contact id in the contact table

where the 2nd "contacts id = the contact id in the ledger

im assuming that the all the relationships need to be there

many thanks simon

It depends how the transactions are entered in the Ledger table. If credits
and debits are entered as positive and negative values in a single column
then you simply need to sum the values in that column for the current contact,
so the ControlSource of an unbound text box in your form would be something
like:

=DSum("Amount", "Ledger", "ContactID = " & [ContactID])

If credits and debits are entered as positive numbers in separate columns in
the ledger table then you sum the credits less the debits, e.g.

=DSum("Nz(Credit,0) – Nz(Debit,0)", "Ledger", "ContactID = " & [ContactID])

The Nz function is used here in case you are allowing Nulls in the Debit or
Credit column. If not and the columns each have a DefaultValue property of
zero, you don't need to call the function, so:

=DSum("Credit - Debit", "Ledger", "ContactID = " & [ContactID])

Ken Sheridan
Stafford, England

Thanks to some earlier help, I now have a simple database up and running with
contacts and an associated ledger.
The ledger has a "totals" row to calculate the current balance of the
contact. I would like the contacts form to have the account balance, sort of
a snapshot without going into the ledger. How do I achieve this?, I've tried
adding existing fields but the "total field" doesn't show up, presumably
because its a calculation rather than a field itself?. How can I copy that
figure over to the contact page, I am going to be delving into queries?, or
will it be a simple copy and paste, thanks in advance

 

[Simon Lang]

Hi Ken ok I have this

=DSum("Actual Amount","Account Transactions","Clients Ledger ID = " & [ID]),
which I'm happy with however

I'm getting an error on this, my only thought is that the "actual amount" is
coming from the form as it doesn't show up in a the table equivalent

Simon

Simon:

where "Amount" = the column where the figures are added/subtracted
Correct

where "Ledger" = the form/table where the amount sits

The name of the table; a form is just the vehicle for seeing/editing the data.

where the 1st "contacts id = the contact id in the contact table

No, it’s the name of the foreign key contact id column in the Ledger table
which identifies which contact the transaction relates to.

where the 2nd "contacts id = the contact id in the ledger

This is the contact id in the contact table, i.e. in the form's underlying
recordset, or it could be a control bound to that column, though in reality
its probably the name of both.

(so you really juts got the last two the wrong way around.)

Ken Sheridan
Stafford, England

Hi Ken

it's this first expression I need, just so I understand,

=DSum("Amount", "Ledger", "ContactID = " & [ContactID])

where "Amount" = the column where the figures are added/subtracted

where "Ledger" = the form/table where the amount sits

where the 1st "contacts id = the contact id in the contact table

where the 2nd "contacts id = the contact id in the ledger

im assuming that the all the relationships need to be there

many thanks simon

It depends how the transactions are entered in the Ledger table. If credits
and debits are entered as positive and negative values in a single column

[quoted text clipped - 27 lines]

figure over to the contact page, I am going to be delving into queries?, or
will it be a simple copy and paste, thanks in advance

 

[Douglas J. Steele]

Nor should it show up in a table equivalent.

As fellow MVP John Vinson likes to say ""Storing calculated data generally
accomplishes only three things: it wastes disk space, it wastes time (a disk
fetch is much slower than almost any reasonable calculation), and it risks
data validity, since once it's stored in a table either the Total or one of
the fields that goes into the total may be changed, making the value WRONG."


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Ken ok I have this

=DSum("Actual Amount","Account Transactions","Clients Ledger ID = " &
[ID]),
which I'm happy with however

I'm getting an error on this, my only thought is that the "actual amount"
is
coming from the form as it doesn't show up in a the table equivalent

Simon

Simon:

where "Amount" = the column where the figures are added/subtracted
Correct

where "Ledger" = the form/table where the amount sits

The name of the table; a form is just the vehicle for seeing/editing the
data.

where the 1st "contacts id = the contact id in the contact table

No, it's the name of the foreign key contact id column in the Ledger
table
which identifies which contact the transaction relates to.

where the 2nd "contacts id = the contact id in the ledger

This is the contact id in the contact table, i.e. in the form's
underlying
recordset, or it could be a control bound to that column, though in
reality
its probably the name of both.

(so you really juts got the last two the wrong way around.)

Ken Sheridan
Stafford, England

Hi Ken

it's this first expression I need, just so I understand,

=DSum("Amount", "Ledger", "ContactID = " & [ContactID])

where "Amount" = the column where the figures are added/subtracted

where "Ledger" = the form/table where the amount sits

where the 1st "contacts id = the contact id in the contact table

where the 2nd "contacts id = the contact id in the ledger

im assuming that the all the relationships need to be there

many thanks simon

It depends how the transactions are entered in the Ledger table. If
credits
and debits are entered as positive and negative values in a single
column
[quoted text clipped - 27 lines]
figure over to the contact page, I am going to be delving into
queries?, or
will it be a simple copy and paste, thanks in advance

 

[Simon Lang]

Hi Ken

just the ticket, thanks so much for your patience

simon

Simon:

Object names with spaces or other special characters in them must be wrapped
in square brackets when referencing them:

DSum("[Actual Amount]","[Account Transactions]","[Clients Ledger ID] = " &
[ID])

If you don't have a column (field) name Actual Amount in the Account
Transactions table one possibility is that it might have been given a default
Caption property in table design, so is labelled as something other than its
real name on the form and as the column heading when viewing the table in
datasheet view. Switch the table to design view to see its real name if this
is the case, and use the real name when referencing it.

Ken Sheridan
Stafford, England

Hi Ken ok I have this

=DSum("Actual Amount","Account Transactions","Clients Ledger ID = " & [ID]),
which I'm happy with however

I'm getting an error on this, my only thought is that the "actual amount" is
coming from the form as it doesn't show up in a the table equivalent

Simon

Simon:

[quoted text clipped - 45 lines]

figure over to the contact page, I am going to be delving into queries?, or
will it be a simple copy and paste, thanks in advance

 

[Douglas J. Steele]

I think you're right, Ken. I misread the post.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I don't think Simon is referring to the computed control here, is he?
Isn't
it the column in the transactions table which stores the credit and debit
currency values? As I said in my reply to him it could be that the column
has been given a default caption property of 'actual amount' in the table
definition (a misguided feature which only serves to confuse in my view).

Ken Sheridan
Stafford, England

Nor should it show up in a table equivalent.

As fellow MVP John Vinson likes to say ""Storing calculated data generally
accomplishes only three things: it wastes disk space, it wastes time (a
disk
fetch is much slower than almost any reasonable calculation), and it risks
data validity, since once it's stored in a table either the Total or one
of
the fields that goes into the total may be changed, making the value
WRONG."

Hi Ken ok I have this

[quoted text clipped - 64 lines]

queries?, or
will it be a simple copy and paste, thanks in advance