save calculated form fields to a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have my invoice form, that has the following calculated fields:

totalshipping
invoicesubtotal

I would like to be able to save these calculated fields to my
tlbinvoicedetails.

Any ideas? suggestions.

Brook
 
Don't do it!

Seriously, you are only causing yourself more grief by saving the total and
trying to keep it up to date under every circumstance.
 
Allen,

Thanks for the suggestion, but how would you recommend that I "maintain"/
save my totals?

Do you have any suggestions?

Why do you feel so strongly about not saving the values from the form?

Brook
 
Do you have a recommendation on how I can "maintain" my invoice totals within
a table?

Brook
 
Brook, to find out why, locate and read anything on the topic,
"normalization".

If you want a whole book on the topic, try:
Rebecca M. Riordan "Designing Effective Database Systems" (Lebanon IN:
Addison Wesley Professional, 2005) ISBN: 0321290933

The basic idea is that you ask Access to give you the total when you need
it. DSum() is a simple approach. In a Form Footer or Report Footer, you can
use:
=Sum([Amount])
Then the field is never wrong.

For a working example, open the Northwind sample database, and see the
Orders form, and the Invoice report.
 
Allen,

Thanks for all the information, I guess I should explain why I am trying
to perform the task at hand.

I am trying to set up and A/R table so that I can have my invoicedate,
invoicenumber, invoicetotal, and add fields for payments. My client pay their
invoice on a monthly basis due in 90 days after the invoice date, and that is
why I have been trying to create this.

Thanks again, and if you have any suggestion on how I can accomplish what
I am trying to do, please pass it on.

Brook



Allen Browne said:
Brook, to find out why, locate and read anything on the topic,
"normalization".

If you want a whole book on the topic, try:
Rebecca M. Riordan "Designing Effective Database Systems" (Lebanon IN:
Addison Wesley Professional, 2005) ISBN: 0321290933

The basic idea is that you ask Access to give you the total when you need
it. DSum() is a simple approach. In a Form Footer or Report Footer, you can
use:
=Sum([Amount])
Then the field is never wrong.

For a working example, open the Northwind sample database, and see the
Orders form, and the Invoice report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Brook said:
Allen,

Thanks for the suggestion, but how would you recommend that I "maintain"/
save my totals?

Do you have any suggestions?

Why do you feel so strongly about not saving the values from the form?

Brook
Click to expand...
Click to expand...
 
Use the calculated total, like the Invoice report example in Northwind.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Brook said:
Allen,

Thanks for all the information, I guess I should explain why I am trying
to perform the task at hand.

I am trying to set up and A/R table so that I can have my invoicedate,
invoicenumber, invoicetotal, and add fields for payments. My client pay
their
invoice on a monthly basis due in 90 days after the invoice date, and that
is
why I have been trying to create this.

Thanks again, and if you have any suggestion on how I can accomplish what
I am trying to do, please pass it on.

Brook



Allen Browne said:
Brook, to find out why, locate and read anything on the topic,
"normalization".

If you want a whole book on the topic, try:
Rebecca M. Riordan "Designing Effective Database Systems" (Lebanon IN:
Addison Wesley Professional, 2005) ISBN: 0321290933

The basic idea is that you ask Access to give you the total when you need
it. DSum() is a simple approach. In a Form Footer or Report Footer, you
can
use:
=Sum([Amount])
Then the field is never wrong.

For a working example, open the Northwind sample database, and see the
Orders form, and the Invoice report.


Brook said:
Allen,

Thanks for the suggestion, but how would you recommend that I
"maintain"/
save my totals?

Do you have any suggestions?

Why do you feel so strongly about not saving the values from the form?

Brook

:

Don't do it!

Seriously, you are only causing yourself more grief by saving the
total
and
trying to keep it up to date under every circumstance.


I have my invoice form, that has the following calculated fields:

totalshipping
invoicesubtotal

I would like to be able to save these calculated fields to my
tlbinvoicedetails.

Any ideas? suggestions.

Brook
Click to expand...
Click to expand...
Click to expand...
 
Hi Brook,

Allen is totally correct you should try to never store a value in a table
that can be calculated at any point in time from other data, the trouble
with storing it is, if the underlying data that makes up the calculation
changes you need to recalculate the total. In your specific example what if
one item on the invoice was credited, you would have to recalculate the
total when you entered the credit, where as by calculating when you need the
total you just need to calculate at the point of running the report or form,
meaning your total has a much greater chance of being correct. Less things
to go wrong.

And I go with the idea that if it can go wrong it will, your business logic
is far simpler by adopting this method rather than trying to store the total
in the database.

Hope it helps.

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

Brook said:
Allen,

Thanks for all the information, I guess I should explain why I am trying
to perform the task at hand.

I am trying to set up and A/R table so that I can have my invoicedate,
invoicenumber, invoicetotal, and add fields for payments. My client pay
their
invoice on a monthly basis due in 90 days after the invoice date, and that
is
why I have been trying to create this.

Thanks again, and if you have any suggestion on how I can accomplish what
I am trying to do, please pass it on.

Brook



Allen Browne said:
Brook, to find out why, locate and read anything on the topic,
"normalization".

If you want a whole book on the topic, try:
Rebecca M. Riordan "Designing Effective Database Systems" (Lebanon IN:
Addison Wesley Professional, 2005) ISBN: 0321290933

The basic idea is that you ask Access to give you the total when you need
it. DSum() is a simple approach. In a Form Footer or Report Footer, you
can
use:
=Sum([Amount])
Then the field is never wrong.

For a working example, open the Northwind sample database, and see the
Orders form, and the Invoice report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Brook said:
Allen,

Thanks for the suggestion, but how would you recommend that I
"maintain"/
save my totals?

Do you have any suggestions?

Why do you feel so strongly about not saving the values from the form?

Brook

:

Don't do it!

Seriously, you are only causing yourself more grief by saving the
total
and
trying to keep it up to date under every circumstance.


I have my invoice form, that has the following calculated fields:

totalshipping
invoicesubtotal

I would like to be able to save these calculated fields to my
tlbinvoicedetails.

Any ideas? suggestions.

Brook
Click to expand...
Click to expand...
Click to expand...
 
what do you mean by use the calculated total? I thought you said I should use
a calculated total in a table? I am a little confused?

Brook

Allen Browne said:
Use the calculated total, like the Invoice report example in Northwind.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Brook said:
Allen,

Thanks for all the information, I guess I should explain why I am trying
to perform the task at hand.

I am trying to set up and A/R table so that I can have my invoicedate,
invoicenumber, invoicetotal, and add fields for payments. My client pay
their
invoice on a monthly basis due in 90 days after the invoice date, and that
is
why I have been trying to create this.

Thanks again, and if you have any suggestion on how I can accomplish what
I am trying to do, please pass it on.

Brook



Allen Browne said:
Brook, to find out why, locate and read anything on the topic,
"normalization".

If you want a whole book on the topic, try:
Rebecca M. Riordan "Designing Effective Database Systems" (Lebanon IN:
Addison Wesley Professional, 2005) ISBN: 0321290933

The basic idea is that you ask Access to give you the total when you need
it. DSum() is a simple approach. In a Form Footer or Report Footer, you
can
use:
=Sum([Amount])
Then the field is never wrong.

For a working example, open the Northwind sample database, and see the
Orders form, and the Invoice report.


Allen,

Thanks for the suggestion, but how would you recommend that I
"maintain"/
save my totals?

Do you have any suggestions?

Why do you feel so strongly about not saving the values from the form?

Brook

:

Don't do it!

Seriously, you are only causing yourself more grief by saving the
total
and
trying to keep it up to date under every circumstance.


I have my invoice form, that has the following calculated fields:

totalshipping
invoicesubtotal

I would like to be able to save these calculated fields to my
tlbinvoicedetails.

Any ideas? suggestions.

Brook
Click to expand...
Click to expand...
Click to expand...
 
Have you looked at the Orders form and the Invoice report in Northwind yet?

It is a good example of how to work with the calculated total WITHOUT
storing it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Brook said:
what do you mean by use the calculated total? I thought you said I should
use
a calculated total in a table? I am a little confused?

Brook

Allen Browne said:
Use the calculated total, like the Invoice report example in Northwind.


Brook said:
Allen,

Thanks for all the information, I guess I should explain why I am
trying
to perform the task at hand.

I am trying to set up and A/R table so that I can have my
invoicedate,
invoicenumber, invoicetotal, and add fields for payments. My client pay
their
invoice on a monthly basis due in 90 days after the invoice date, and
that
is
why I have been trying to create this.

Thanks again, and if you have any suggestion on how I can accomplish
what
I am trying to do, please pass it on.

Brook



:

Brook, to find out why, locate and read anything on the topic,
"normalization".

If you want a whole book on the topic, try:
Rebecca M. Riordan "Designing Effective Database Systems" (Lebanon IN:
Addison Wesley Professional, 2005) ISBN: 0321290933

The basic idea is that you ask Access to give you the total when you
need
it. DSum() is a simple approach. In a Form Footer or Report Footer,
you
can
use:
=Sum([Amount])
Then the field is never wrong.

For a working example, open the Northwind sample database, and see the
Orders form, and the Invoice report.


Allen,

Thanks for the suggestion, but how would you recommend that I
"maintain"/
save my totals?

Do you have any suggestions?

Why do you feel so strongly about not saving the values from the
form?

Brook

:

Don't do it!

Seriously, you are only causing yourself more grief by saving the
total
and
trying to keep it up to date under every circumstance.


I have my invoice form, that has the following calculated fields:

totalshipping
invoicesubtotal

I would like to be able to save these calculated fields to my
tlbinvoicedetails.

Any ideas? suggestions.

Brook
Click to expand...
Click to expand...
Click to expand...
 
Alex,

With this complete thread I understand the focus on a normalized db. If I
can't save a total amount to a record, can you give me some direction as to
how do this?

michael

Alex White MCDBA MCSE said:
Hi Brook,

Allen is totally correct you should try to never store a value in a table
that can be calculated at any point in time from other data, the trouble
with storing it is, if the underlying data that makes up the calculation
changes you need to recalculate the total. In your specific example what if
one item on the invoice was credited, you would have to recalculate the
total when you entered the credit, where as by calculating when you need the
total you just need to calculate at the point of running the report or form,
meaning your total has a much greater chance of being correct. Less things
to go wrong.

And I go with the idea that if it can go wrong it will, your business logic
is far simpler by adopting this method rather than trying to store the total
in the database.

Hope it helps.

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

Brook said:
Allen,

Thanks for all the information, I guess I should explain why I am trying
to perform the task at hand.

I am trying to set up and A/R table so that I can have my invoicedate,
invoicenumber, invoicetotal, and add fields for payments. My client pay
their
invoice on a monthly basis due in 90 days after the invoice date, and that
is
why I have been trying to create this.

Thanks again, and if you have any suggestion on how I can accomplish what
I am trying to do, please pass it on.

Brook



Allen Browne said:
Brook, to find out why, locate and read anything on the topic,
"normalization".

If you want a whole book on the topic, try:
Rebecca M. Riordan "Designing Effective Database Systems" (Lebanon IN:
Addison Wesley Professional, 2005) ISBN: 0321290933

The basic idea is that you ask Access to give you the total when you need
it. DSum() is a simple approach. In a Form Footer or Report Footer, you
can
use:
=Sum([Amount])
Then the field is never wrong.

For a working example, open the Northwind sample database, and see the
Orders form, and the Invoice report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for the suggestion, but how would you recommend that I
"maintain"/
save my totals?

Do you have any suggestions?

Why do you feel so strongly about not saving the values from the form?

Brook

:

Don't do it!

Seriously, you are only causing yourself more grief by saving the
total
and
trying to keep it up to date under every circumstance.


I have my invoice form, that has the following calculated fields:

totalshipping
invoicesubtotal

I would like to be able to save these calculated fields to my
tlbinvoicedetails.

Any ideas? suggestions.

Brook
Click to expand...
Click to expand...
Click to expand...
 
I don't understand why you feel you need to store a calculation. An AR
database would simply have a transaction table. Each invoice or credit
would be ONE record in your transaction table with a positive number. Each
payment would be a single record in your transaction table with a negative
number. To produce statements, you'd simply pull records for the date
specified.

Is this a balance forward schedule? Or open item?

If it is balance forward, then you would need to pull all entries prior to
the first of this month and total those in a query. That total would be the
first line on your statement and it would say....

Balance forward...........$ xx.xx

Then, all details from the current month would be shown as separate details.

Your footer would include the total for all details plus the balance
forward.



If it is an open item schedule, then you'd simply pull all invoice/reference
numbers that do not clear out to zero.

--
Rick B



michael said:
Alex,

With this complete thread I understand the focus on a normalized db. If I
can't save a total amount to a record, can you give me some direction as to
how do this?

michael

Alex White MCDBA MCSE said:
Hi Brook,

Allen is totally correct you should try to never store a value in a table
that can be calculated at any point in time from other data, the trouble
with storing it is, if the underlying data that makes up the calculation
changes you need to recalculate the total. In your specific example what if
one item on the invoice was credited, you would have to recalculate the
total when you entered the credit, where as by calculating when you need the
total you just need to calculate at the point of running the report or form,
meaning your total has a much greater chance of being correct. Less things
to go wrong.

And I go with the idea that if it can go wrong it will, your business logic
is far simpler by adopting this method rather than trying to store the total
in the database.

Hope it helps.

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

Brook said:
Allen,

Thanks for all the information, I guess I should explain why I am trying
to perform the task at hand.

I am trying to set up and A/R table so that I can have my invoicedate,
invoicenumber, invoicetotal, and add fields for payments. My client pay
their
invoice on a monthly basis due in 90 days after the invoice date, and that
is
why I have been trying to create this.

Thanks again, and if you have any suggestion on how I can accomplish what
I am trying to do, please pass it on.

Brook



:

Brook, to find out why, locate and read anything on the topic,
"normalization".

If you want a whole book on the topic, try:
Rebecca M. Riordan "Designing Effective Database Systems" (Lebanon IN:
Addison Wesley Professional, 2005) ISBN: 0321290933

The basic idea is that you ask Access to give you the total when you need
it. DSum() is a simple approach. In a Form Footer or Report Footer, you
can
use:
=Sum([Amount])
Then the field is never wrong.

For a working example, open the Northwind sample database, and see the
Orders form, and the Invoice report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for the suggestion, but how would you recommend that I
"maintain"/
save my totals?

Do you have any suggestions?

Why do you feel so strongly about not saving the values from the form?

Brook

:

Don't do it!

Seriously, you are only causing yourself more grief by saving the
total
and
trying to keep it up to date under every circumstance.


I have my invoice form, that has the following calculated fields:

totalshipping
invoicesubtotal

I would like to be able to save these calculated fields to my
tlbinvoicedetails.

Any ideas? suggestions.

Brook
Click to expand...
Click to expand...
Click to expand...
 
RickB,

Thanks for the reply. The main reason I need to store the calculation is
that the calc number is needed for another (non-Access) application needs to
use it. I know that the calculation can change (as stated in various threads
here), but it is needed for a barcode application. I guess my question is
'can it be saved?' After reading here I am not too sure that a calculation
can be saved. If it is possible any help would be appreciated.

thanks again,



Rick B said:
I don't understand why you feel you need to store a calculation. An AR
database would simply have a transaction table. Each invoice or credit
would be ONE record in your transaction table with a positive number. Each
payment would be a single record in your transaction table with a negative
number. To produce statements, you'd simply pull records for the date
specified.

Is this a balance forward schedule? Or open item?

If it is balance forward, then you would need to pull all entries prior to
the first of this month and total those in a query. That total would be the
first line on your statement and it would say....

Balance forward...........$ xx.xx

Then, all details from the current month would be shown as separate details.

Your footer would include the total for all details plus the balance
forward.



If it is an open item schedule, then you'd simply pull all invoice/reference
numbers that do not clear out to zero.

--
Rick B



michael said:
Alex,

With this complete thread I understand the focus on a normalized db. If I
can't save a total amount to a record, can you give me some direction as to
how do this?

michael

Alex White MCDBA MCSE said:
Hi Brook,

Allen is totally correct you should try to never store a value in a table
that can be calculated at any point in time from other data, the trouble
with storing it is, if the underlying data that makes up the calculation
changes you need to recalculate the total. In your specific example what if
one item on the invoice was credited, you would have to recalculate the
total when you entered the credit, where as by calculating when you need the
total you just need to calculate at the point of running the report or form,
meaning your total has a much greater chance of being correct. Less things
to go wrong.

And I go with the idea that if it can go wrong it will, your business logic
is far simpler by adopting this method rather than trying to store the total
in the database.

Hope it helps.

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

Allen,

Thanks for all the information, I guess I should explain why I am trying
to perform the task at hand.

I am trying to set up and A/R table so that I can have my invoicedate,
invoicenumber, invoicetotal, and add fields for payments. My client pay
their
invoice on a monthly basis due in 90 days after the invoice date, and that
is
why I have been trying to create this.

Thanks again, and if you have any suggestion on how I can accomplish what
I am trying to do, please pass it on.

Brook



:

Brook, to find out why, locate and read anything on the topic,
"normalization".

If you want a whole book on the topic, try:
Rebecca M. Riordan "Designing Effective Database Systems" (Lebanon IN:
Addison Wesley Professional, 2005) ISBN: 0321290933

The basic idea is that you ask Access to give you the total when you need
it. DSum() is a simple approach. In a Form Footer or Report Footer, you
can
use:
=Sum([Amount])
Then the field is never wrong.

For a working example, open the Northwind sample database, and see the
Orders form, and the Invoice report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for the suggestion, but how would you recommend that I
"maintain"/
save my totals?

Do you have any suggestions?

Why do you feel so strongly about not saving the values from the form?

Brook

:

Don't do it!

Seriously, you are only causing yourself more grief by saving the
total
and
trying to keep it up to date under every circumstance.


I have my invoice form, that has the following calculated fields:

totalshipping
invoicesubtotal

I would like to be able to save these calculated fields to my
tlbinvoicedetails.

Any ideas? suggestions.

Brook
Click to expand...
Click to expand...
Click to expand...
 
Yes, you can save a calculation.

Add your field to the table. Place that field (as a bound field) on your
form. In the before update event for your form, set the value of that
field.

There are other ways including update queries.

Of course, your 'other program' can pull from a query instead of a table and
then you can include your calcualtions in the query.

--
Rick B



michael said:
RickB,

Thanks for the reply. The main reason I need to store the calculation is
that the calc number is needed for another (non-Access) application needs to
use it. I know that the calculation can change (as stated in various threads
here), but it is needed for a barcode application. I guess my question is
'can it be saved?' After reading here I am not too sure that a calculation
can be saved. If it is possible any help would be appreciated.

thanks again,



Rick B said:
I don't understand why you feel you need to store a calculation. An AR
database would simply have a transaction table. Each invoice or credit
would be ONE record in your transaction table with a positive number. Each
payment would be a single record in your transaction table with a negative
number. To produce statements, you'd simply pull records for the date
specified.

Is this a balance forward schedule? Or open item?

If it is balance forward, then you would need to pull all entries prior to
the first of this month and total those in a query. That total would be the
first line on your statement and it would say....

Balance forward...........$ xx.xx

Then, all details from the current month would be shown as separate details.

Your footer would include the total for all details plus the balance
forward.



If it is an open item schedule, then you'd simply pull all invoice/reference
numbers that do not clear out to zero.

--
Rick B



michael said:
Alex,

With this complete thread I understand the focus on a normalized db. If I
can't save a total amount to a record, can you give me some direction
Click to expand...
as
to
how do this?

michael

:

Hi Brook,

Allen is totally correct you should try to never store a value in a table
that can be calculated at any point in time from other data, the trouble
with storing it is, if the underlying data that makes up the calculation
changes you need to recalculate the total. In your specific example
Click to expand...
what
if
one item on the invoice was credited, you would have to recalculate the
total when you entered the credit, where as by calculating when you
Click to expand...
need
the
total you just need to calculate at the point of running the report
Click to expand...
or
form,
meaning your total has a much greater chance of being correct. Less things
to go wrong.

And I go with the idea that if it can go wrong it will, your
Click to expand...
business
logic
is far simpler by adopting this method rather than trying to store
Click to expand...
the
total
in the database.

Hope it helps.

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

Allen,

Thanks for all the information, I guess I should explain why I am trying
to perform the task at hand.

I am trying to set up and A/R table so that I can have my invoicedate,
invoicenumber, invoicetotal, and add fields for payments. My
Click to expand...
client
pay
their
invoice on a monthly basis due in 90 days after the invoice date,
Click to expand...
and
that
is
why I have been trying to create this.

Thanks again, and if you have any suggestion on how I can
Click to expand...
accomplish
what
I am trying to do, please pass it on.

Brook



:

Brook, to find out why, locate and read anything on the topic,
"normalization".

If you want a whole book on the topic, try:
Rebecca M. Riordan "Designing Effective Database Systems"
Click to expand...
(Lebanon
IN:
Addison Wesley Professional, 2005) ISBN: 0321290933

The basic idea is that you ask Access to give you the total when
Click to expand...
you
need
it. DSum() is a simple approach. In a Form Footer or Report
Click to expand...
Footer,
you
can
use:
=Sum([Amount])
Then the field is never wrong.

For a working example, open the Northwind sample database, and
Click to expand...
see
the
Orders form, and the Invoice report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for the suggestion, but how would you recommend that I
"maintain"/
save my totals?

Do you have any suggestions?

Why do you feel so strongly about not saving the values from
Click to expand...
the
form?
Brook

:

Don't do it!

Seriously, you are only causing yourself more grief by saving the
total
and
trying to keep it up to date under every circumstance.


I have my invoice form, that has the following calculated fields:

totalshipping
invoicesubtotal

I would like to be able to save these calculated fields to my
tlbinvoicedetails.

Any ideas? suggestions.

Brook
Click to expand...
Click to expand...
Click to expand...
 
Rick B.

Thanks. I would be more interested in how to use queries for seperate .exe.
But for right now I need to get this task off my desk. I am not access
aware, so I am climbing the hill every time I try something.

My original idea was to set the value On Close (might be wrong), but I will
do as noted below. But now I am not sure what to put into the update event.

michael



Rick B said:
Yes, you can save a calculation.

Add your field to the table. Place that field (as a bound field) on your
form. In the before update event for your form, set the value of that
field.

There are other ways including update queries.

Of course, your 'other program' can pull from a query instead of a table and
then you can include your calcualtions in the query.

--
Rick B



michael said:
RickB,

Thanks for the reply. The main reason I need to store the calculation is
that the calc number is needed for another (non-Access) application needs to
use it. I know that the calculation can change (as stated in various threads
here), but it is needed for a barcode application. I guess my question is
'can it be saved?' After reading here I am not too sure that a calculation
can be saved. If it is possible any help would be appreciated.

thanks again,



Rick B said:
I don't understand why you feel you need to store a calculation. An AR
database would simply have a transaction table. Each invoice or credit
would be ONE record in your transaction table with a positive number. Each
payment would be a single record in your transaction table with a negative
number. To produce statements, you'd simply pull records for the date
specified.

Is this a balance forward schedule? Or open item?

If it is balance forward, then you would need to pull all entries prior to
the first of this month and total those in a query. That total would be the
first line on your statement and it would say....

Balance forward...........$ xx.xx

Then, all details from the current month would be shown as separate details.

Your footer would include the total for all details plus the balance
forward.



If it is an open item schedule, then you'd simply pull all invoice/reference
numbers that do not clear out to zero.

--
Rick B



Alex,

With this complete thread I understand the focus on a normalized db. If I
can't save a total amount to a record, can you give me some direction as
to
how do this?

michael

:

Hi Brook,

Allen is totally correct you should try to never store a value in a
table
that can be calculated at any point in time from other data, the trouble
with storing it is, if the underlying data that makes up the calculation
changes you need to recalculate the total. In your specific example what
if
one item on the invoice was credited, you would have to recalculate the
total when you entered the credit, where as by calculating when you need
the
total you just need to calculate at the point of running the report or
form,
meaning your total has a much greater chance of being correct. Less
things
to go wrong.

And I go with the idea that if it can go wrong it will, your business
logic
is far simpler by adopting this method rather than trying to store the
total
in the database.

Hope it helps.

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

Allen,

Thanks for all the information, I guess I should explain why I am
trying
to perform the task at hand.

I am trying to set up and A/R table so that I can have my
invoicedate,
invoicenumber, invoicetotal, and add fields for payments. My client
pay
their
invoice on a monthly basis due in 90 days after the invoice date, and
that
is
why I have been trying to create this.

Thanks again, and if you have any suggestion on how I can accomplish
what
I am trying to do, please pass it on.

Brook



:

Brook, to find out why, locate and read anything on the topic,
"normalization".

If you want a whole book on the topic, try:
Rebecca M. Riordan "Designing Effective Database Systems" (Lebanon
IN:
Addison Wesley Professional, 2005) ISBN: 0321290933

The basic idea is that you ask Access to give you the total when you
need
it. DSum() is a simple approach. In a Form Footer or Report Footer,
you
can
use:
=Sum([Amount])
Then the field is never wrong.

For a working example, open the Northwind sample database, and see
the
Orders form, and the Invoice report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for the suggestion, but how would you recommend that I
"maintain"/
save my totals?

Do you have any suggestions?

Why do you feel so strongly about not saving the values from the
form?

Brook

:

Don't do it!

Seriously, you are only causing yourself more grief by saving the
total
and
trying to keep it up to date under every circumstance.


I have my invoice form, that has the following calculated fields:

totalshipping
invoicesubtotal

I would like to be able to save these calculated fields to my
tlbinvoicedetails.

Any ideas? suggestions.

Brook
Click to expand...
Click to expand...
Click to expand...
 
Back
Top