Net Cost + Tax = Total

[Wolfgang]

Hi there

I am new to Access and hope someone can help. Thanks in advance.

I have a Database with a Column Called "Price" and a Column Called "Shipping
Cost". The 3rd Column is called "Total". How do i get Access to add Price
and Shipping Cost and show me the result in Column "Total"
Thanks again for you help

Wolfgang
PS Still working on my English writing Please excuse my mistakes

 

[Guest]

Hiya,

There are a couple of ways around achieving this:

- If you are using a query you can add the following statement into the
Total field in query design view. "TOTAL:[Price]+[ShippingCost]"
- Alternatively you can just display the Total if you are using a form in a
text box. In the text box you would need to set its ControlSource to
"[Price]+[ShippingCost]"

Hope that helps,
Ash.

 

[Guest]

Oooops it should be:
Total[Price]+[ShippingCost])
AND
=([Price]+[ShippingCost]) as the control source.

 

[Guest]

Or, better yet:

Total: Nz([Price],0) + Nz([ShippingCost],0)
AND
=Nz([Price],0)+Nz([ShippingCost],0) as the control source.


Nz([FieldToTest],ValueIfNull) will cause the result to show up as 0 instead
of #Error, if either the Price or ShippingCost fields are null. Of course,
you could also just apply the function one time to the result. For example:

Total: Nz(([Price]+[ShippingCost]),0)

On your other comment, regarding rating the post, the OP will likely not
have a clue what you meant, because the question was not posted via the
Microsoft web portal.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Oooops it should be:
Total[Price]+[ShippingCost])
AND
=([Price]+[ShippingCost]) as the control source.
--
Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
(e-mail address removed)

Hiya,

There are a couple of ways around achieving this:

- If you are using a query you can add the following statement into the
Total field in query design view. "TOTAL:[Price]+[ShippingCost]"
- Alternatively you can just display the Total if you are using a form in a
text box. In the text box you would need to set its ControlSource to
"[Price]+[ShippingCost]"

Hope that helps,
Ash.

 

[Guest]

Hey Tom,
Can i ask a quick question? Web portal?
Ive only just started posting answers on here, i want to help others out but
want to get rated as helpful could you explain some more?
thanks in advance.
--
Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
(e-mail address removed)

Or, better yet:

Total: Nz([Price],0) + Nz([ShippingCost],0)
AND
=Nz([Price],0)+Nz([ShippingCost],0) as the control source.


Nz([FieldToTest],ValueIfNull) will cause the result to show up as 0 instead
of #Error, if either the Price or ShippingCost fields are null. Of course,
you could also just apply the function one time to the result. For example:

Total: Nz(([Price]+[ShippingCost]),0)

On your other comment, regarding rating the post, the OP will likely not
have a clue what you meant, because the question was not posted via the
Microsoft web portal.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Oooops it should be:
Total[Price]+[ShippingCost])
AND
=([Price]+[ShippingCost]) as the control source.
--
Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
(e-mail address removed)

Hiya,

There are a couple of ways around achieving this:

- If you are using a query you can add the following statement into the
Total field in query design view. "TOTAL:[Price]+[ShippingCost]"
- Alternatively you can just display the Total if you are using a form in a
text box. In the text box you would need to set its ControlSource to
"[Price]+[ShippingCost]"

Hope that helps,
Ash.

 

[Wolfgang]

I am still confuesd

I like to see the result in my Database Table. So where do i write the
expresion into ? Thanks.

and how can i rate for you as helpfull?

Wolfgang

Hey Tom,
Can i ask a quick question? Web portal?
Ive only just started posting answers on here, i want to help others out
but
want to get rated as helpful could you explain some more?
thanks in advance.
--
Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
(e-mail address removed)

Or, better yet:

Total: Nz([Price],0) + Nz([ShippingCost],0)
AND
=Nz([Price],0)+Nz([ShippingCost],0) as the control source.


Nz([FieldToTest],ValueIfNull) will cause the result to show up as 0
instead
of #Error, if either the Price or ShippingCost fields are null. Of
course,
you could also just apply the function one time to the result. For
example:

Total: Nz(([Price]+[ShippingCost]),0)

On your other comment, regarding rating the post, the OP will likely not
have a clue what you meant, because the question was not posted via the
Microsoft web portal.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Oooops it should be:
Total[Price]+[ShippingCost])
AND
=([Price]+[ShippingCost]) as the control source.
--
Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
(e-mail address removed)


:

Hiya,

There are a couple of ways around achieving this:

- If you are using a query you can add the following statement into
the
Total field in query design view. "TOTAL:[Price]+[ShippingCost]"
- Alternatively you can just display the Total if you are using a
form in a
text box. In the text box you would need to set its ControlSource to
"[Price]+[ShippingCost]"

Hope that helps,
Ash.

 

[Guest]

Hi Ash,

Welcome to this newsgroup. You appear to be posting your answers via the
Microsoft web portal:

http://www.microsoft.com/office/com...cess&mid=42f3e8df-ef7c-4b12-89dc-efcef9229b5c

since one can click on your profile name (ashg657) and see that you joined
on 3/22/2006, and, as of right now, have made a total of 8 posts. These
features, along with the ability to rate a post, are available only to those
who access the newsgroup via this web page. However, if someone uses another
newsreader, such as Outlook Express, they will not see any command buttons
for rating a post.

A question was posted using the web portal if you see a small brown circle
with a white question mark. If you do not see that symbol, then the person
who posted the question did not use the web portal.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hey Tom,
Can i ask a quick question? Web portal?
Ive only just started posting answers on here, i want to help others out but
want to get rated as helpful could you explain some more?
thanks in advance.
--
Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
(e-mail address removed)

Or, better yet:

Total: Nz([Price],0) + Nz([ShippingCost],0)
AND
=Nz([Price],0)+Nz([ShippingCost],0) as the control source.


Nz([FieldToTest],ValueIfNull) will cause the result to show up as 0 instead
of #Error, if either the Price or ShippingCost fields are null. Of course,
you could also just apply the function one time to the result. For example:

Total: Nz(([Price]+[ShippingCost]),0)

On your other comment, regarding rating the post, the OP will likely not
have a clue what you meant, because the question was not posted via the
Microsoft web portal.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Oooops it should be:
Total[Price]+[ShippingCost])
AND
=([Price]+[ShippingCost]) as the control source.
--
Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
(e-mail address removed)


:

Hiya,

There are a couple of ways around achieving this:

- If you are using a query you can add the following statement into the
Total field in query design view. "TOTAL:[Price]+[ShippingCost]"
- Alternatively you can just display the Total if you are using a form in a
text box. In the text box you would need to set its ControlSource to
"[Price]+[ShippingCost]"

Hope that helps,
Ash.

 

[Guest]

Excellent stuff, thanks.
It would seem I am new to the newsgroup although I have been here some time,
just a change of email account forced me to create a new account.
Thanks again.

--
Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
(e-mail address removed)

Hi Ash,

Welcome to this newsgroup. You appear to be posting your answers via the
Microsoft web portal:

http://www.microsoft.com/office/com...cess&mid=42f3e8df-ef7c-4b12-89dc-efcef9229b5c

since one can click on your profile name (ashg657) and see that you joined
on 3/22/2006, and, as of right now, have made a total of 8 posts. These
features, along with the ability to rate a post, are available only to those
who access the newsgroup via this web page. However, if someone uses another
newsreader, such as Outlook Express, they will not see any command buttons
for rating a post.

A question was posted using the web portal if you see a small brown circle
with a white question mark. If you do not see that symbol, then the person
who posted the question did not use the web portal.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hey Tom,
Can i ask a quick question? Web portal?
Ive only just started posting answers on here, i want to help others out but
want to get rated as helpful could you explain some more?
thanks in advance.
--
Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
(e-mail address removed)

Or, better yet:

Total: Nz([Price],0) + Nz([ShippingCost],0)
AND
=Nz([Price],0)+Nz([ShippingCost],0) as the control source.


Nz([FieldToTest],ValueIfNull) will cause the result to show up as 0 instead
of #Error, if either the Price or ShippingCost fields are null. Of course,
you could also just apply the function one time to the result. For example:

Total: Nz(([Price]+[ShippingCost]),0)

On your other comment, regarding rating the post, the OP will likely not
have a clue what you meant, because the question was not posted via the
Microsoft web portal.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Oooops it should be:
Total[Price]+[ShippingCost])
AND
=([Price]+[ShippingCost]) as the control source.
--
Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
(e-mail address removed)


:

Hiya,

There are a couple of ways around achieving this:

- If you are using a query you can add the following statement into the
Total field in query design view. "TOTAL:[Price]+[ShippingCost]"
- Alternatively you can just display the Total if you are using a form in a
text box. In the text box you would need to set its ControlSource to
"[Price]+[ShippingCost]"

Hope that helps,
Ash.

 

[Wolfgang]

I am still confuesd

I like to see the result in my Database Table. So where do i write the
expresion into ? Thanks.

and how can i rate for you as helpfull?

Wolfgang

Excellent stuff, thanks.
It would seem I am new to the newsgroup although I have been here some
time,
just a change of email account forced me to create a new account.
Thanks again.

--
Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
(e-mail address removed)

Hi Ash,

Welcome to this newsgroup. You appear to be posting your answers via the
Microsoft web portal:

http://www.microsoft.com/office/com...cess&mid=42f3e8df-ef7c-4b12-89dc-efcef9229b5c

since one can click on your profile name (ashg657) and see that you
joined
on 3/22/2006, and, as of right now, have made a total of 8 posts. These
features, along with the ability to rate a post, are available only to
those
who access the newsgroup via this web page. However, if someone uses
another
newsreader, such as Outlook Express, they will not see any command
buttons
for rating a post.

A question was posted using the web portal if you see a small brown
circle
with a white question mark. If you do not see that symbol, then the
person
who posted the question did not use the web portal.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hey Tom,
Can i ask a quick question? Web portal?
Ive only just started posting answers on here, i want to help others
out but
want to get rated as helpful could you explain some more?
thanks in advance.
--
Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
(e-mail address removed)


:

Or, better yet:

Total: Nz([Price],0) + Nz([ShippingCost],0)
AND
=Nz([Price],0)+Nz([ShippingCost],0) as the control source.


Nz([FieldToTest],ValueIfNull) will cause the result to show up as 0
instead
of #Error, if either the Price or ShippingCost fields are null. Of
course,
you could also just apply the function one time to the result. For
example:

Total: Nz(([Price]+[ShippingCost]),0)

On your other comment, regarding rating the post, the OP will likely
not
have a clue what you meant, because the question was not posted via
the
Microsoft web portal.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Oooops it should be:
Total[Price]+[ShippingCost])
AND
=([Price]+[ShippingCost]) as the control source.
--
Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
(e-mail address removed)


:

Hiya,

There are a couple of ways around achieving this:

- If you are using a query you can add the following statement
into the
Total field in query design view. "TOTAL:[Price]+[ShippingCost]"
- Alternatively you can just display the Total if you are using a
form in a
text box. In the text box you would need to set its ControlSource
to
"[Price]+[ShippingCost]"

Hope that helps,
Ash.

 

[Guest]

Hi Wolfgang,

It is usually never a good idea to store the results of a calculation. Doing
so waste disk space, it generally takes longer to retrieve the value using a
disk fetch, and risk the real possibility of bad information. It also
violates both 2nd and 3rd normal form of database design. If the Price or
ShippingCost data is later changed for any reason, you would have to make
sure to update the stored Total value. Here is a quote from database design
expert Michael Hernandez, author of Database Design for Mere Mortals:

"The most important point for you to remember is that you will always
re-introduce data integrity problems when you de-Normalize your structures!
This means that it becomes incumbent upon you or the user to deal with this
issue. Either way, it imposes an unnecessary burden upon the both of you.
De-Normalization is one issue that you'll have to weigh and decide for
yourself whether the perceived benefits are worth the extra effort it will
take to maintain the database properly."

Here are some DB design papers that you may want to check out:

http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Wolfgang]

I did that a couple years ago and it worked fine

All i need is a small database with customer info (name address street
phone) and the Net price + shipping cost , viewed in the column called
Total. Then i use a report and print the bill. Thanks again for helping

Wolfgang

 

[Guest]

Calculate your total on-the-fly, when you need it. While you may have stored
a calculated total in another project that you worked on a couple of years
ago, you should know that this is not a good thing to do.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Ill just re-enforce Tom's point. Its better to do all calculations within
queries & on the forms themselves than to do them on tables.
--
Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
(e-mail address removed)

I am still confuesd

I like to see the result in my Database Table. So where do i write the
expresion into ? Thanks.

and how can i rate for you as helpfull?

Wolfgang

Excellent stuff, thanks.
It would seem I am new to the newsgroup although I have been here some
time,
just a change of email account forced me to create a new account.
Thanks again.

--
Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
(e-mail address removed)

Hi Ash,

Welcome to this newsgroup. You appear to be posting your answers via the
Microsoft web portal:

http://www.microsoft.com/office/com...cess&mid=42f3e8df-ef7c-4b12-89dc-efcef9229b5c

since one can click on your profile name (ashg657) and see that you
joined
on 3/22/2006, and, as of right now, have made a total of 8 posts. These
features, along with the ability to rate a post, are available only to
those
who access the newsgroup via this web page. However, if someone uses
another
newsreader, such as Outlook Express, they will not see any command
buttons
for rating a post.

A question was posted using the web portal if you see a small brown
circle
with a white question mark. If you do not see that symbol, then the
person
who posted the question did not use the web portal.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hey Tom,
Can i ask a quick question? Web portal?
Ive only just started posting answers on here, i want to help others
out but
want to get rated as helpful could you explain some more?
thanks in advance.
--
Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
(e-mail address removed)


:

Or, better yet:

Total: Nz([Price],0) + Nz([ShippingCost],0)
AND
=Nz([Price],0)+Nz([ShippingCost],0) as the control source.


Nz([FieldToTest],ValueIfNull) will cause the result to show up as 0
instead
of #Error, if either the Price or ShippingCost fields are null. Of
course,
you could also just apply the function one time to the result. For
example:

Total: Nz(([Price]+[ShippingCost]),0)

On your other comment, regarding rating the post, the OP will likely
not
have a clue what you meant, because the question was not posted via
the
Microsoft web portal.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Oooops it should be:
Total[Price]+[ShippingCost])
AND
=([Price]+[ShippingCost]) as the control source.
--
Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
(e-mail address removed)


:

Hiya,

There are a couple of ways around achieving this:

- If you are using a query you can add the following statement
into the
Total field in query design view. "TOTAL:[Price]+[ShippingCost]"
- Alternatively you can just display the Total if you are using a
form in a
text box. In the text box you would need to set its ControlSource
to
"[Price]+[ShippingCost]"

Hope that helps,
Ash.

 

[Wolfgang]

OK

Then how do i great a report ( to Print the Bill) with the total calculated?

Total:[Net]+[Satz] Tells me "Syntax Error !?!!? and where exactly do i
write this expression ?

Thanks a lot for the patience you have with me

 

[Guest]

Hi Wolfgang,

You should be able to enter Total:[Net]+[Satz]
in a query that serves as the recordsource for your report. This entry would
go on the line in the Query By Example (QBE) grid that reads "Field:".

Alternatively, you can add a textbox to your report, and then set it's
control source to the following expression:
=[Net]+[Satz]

Either method assumes that the fields named Net and Satz are in the query.
If you use the calculated control, make sure that the name of the textbox is
*not* Net or Satz. Name it something like txtTotal.

Do you have a copy of the sample Northwind.mdb database on your computer?
If so, open the Invoice report. Take a look at how the Total is calculated in
the Invoice Footer section.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

OK

Then how do i great a report ( to Print the Bill) with the total calculated?

Total:[Net]+[Satz] Tells me "Syntax Error !?!!? and where exactly do i
write this expression ?

Thanks a lot for the patience you have with me

 

[John Vinson]

I like to see the result in my Database Table. So where do i write the
expresion into ? Thanks.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson[MVP]

 

[Wolfgang]

Thanks to all . I see now what you meen. I am using aexpression in a textbox
in a repoart and it works just great. Thanks again to all for helping me in
this matter

Wolfgang