culculating and creating column total

[shaunieb]

Hi, i've been trying to do this for about 3 hrs and am really killing my self here.

Here are my 2 tables : vat.percent,markup.percent cost.product,productname.product

What i want to do is select the data and then create a total column with a value calculated from cost.product+vat.percent%+markup.percent%

Please someone help. Im sure its possible!

 

[JamesLean]

Sorry, I'm confused. You said "here are my 2 tables" but then provided what looks like four columns from four different tables??

Can you tell us exactly what tables, columns and relationships you have and what figures you want to show?

--James

 

[checkai]

is there only 1 row in the percent table?

"...we both know I'm training to become a cagefigher...see what happens if you try 'n hit me..."

 

[shaunieb]

No, there is percent.vat, percent.markup & percent.id;
the other table has products.id, products.name, products.description, products.cost.

So i need a total column with a value calculated from products.cost + percent.vat + percent.markup

Sorry I got my table/column order mixed up. Hope this is clearer

 

[checkai]

select p.name,
       p.cost + c.vat + c.markup

from products p
     inner join percent c on p.id = c.id

this work?

"...we both know I'm training to become a cagefigher...see what happens if you try 'n hit me..."

 

[Pattycake245]

If the two tables are joined by products.id and percent.id then:

select a.name, a.description, a.cost+b.vat+b.markup total_cost
from products a join percent b
on a.id=b.id

Tim

 

[shaunieb]

ok, ive done it slightly differently since i didnt tell you guys that the 2 tables dont join in an way, I just wanted the 2 values from percentages to use in the calculations for the product totals. heres my try, it doesnt work! markup and vat are integer variables in my app. it complains about the stuff in bold.

select productd.id, products.picture, products.productname, products.basicdescription, products.cost+"& vat &"+"& markup &" total_cost from products

 

[checkai]

so your app is sending this query as a string to the db i'm assuming...what errors are you getting? and what app language are you using?

"...we both know I'm training to become a cagefigher...see what happens if you try 'n hit me..."

 

[Pattycake245]

So, what's the complaint??

Tim

 

[shaunieb]

Syntax error (missing operator) in query expression 'products.cost+14+25 total_cost'.

 

[checkai]

again...what app language are you using?

do you need the 'as' total_cost or a space between the '+' symbols and the field/numbers?

"...we both know I'm training to become a cagefigher...see what happens if you try 'n hit me..."

 

[Pattycake245]

As Checkai said again, what language are you using and are you passing this query to the db. Try adding "as" before total cost:

select productd.id, products.picture, products.productname, products.basicdescription, products.cost+"& vat &"+"& markup &" as total_cost from products.

Tim

 

[Pattycake245]

Checkai, I'll let you deal with this one, since we keep asking the same questions to a problem that probably could have been solved with one or two posts.

Tim

 

[vongrunt]

IMO this looks like M$ Access error... :E

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.