I need to set up a calculated field that will calculate the price of a pizza order based on data in fields in other tables. I have a table that assigns each pizza a price and a code. From there, I want to calculate the price of an order in another table that only contains the Pizza Code and not the price of the pizza. I need to make it so that the Calculated Field in the table shows the price of the order based on the quantity of the pizza (that is also in the table) and the pizza code.
Asked
Active
Viewed 226 times
0
-
Can you share your tables' structures please? – Mureinik May 29 '16 at 06:55
-
[link](http://imgur.com/a/g4fWi) Here is the structure of the two tables – MyArmsFellOff May 29 '16 at 07:00
-
This question looks an awful lot like this one: https://stackoverflow.com/questions/37507531/ms-access-calculated-field-using-data-from-another-table -- are you sure you didn't just create another account to post the two questions? – Leviathan May 29 '16 at 08:26
-
why would i do that? i could just ask again right? – MyArmsFellOff May 29 '16 at 09:45
-
You should do neither: Do not ask a question again and do not create additional accounts to do so. – Leviathan May 29 '16 at 10:26
1 Answers
0
if you want to calculate the total amount per order per pizza code try this:
select a.[#order],(a.quantity*b.price) as total,a.[pizza code]
from tblorders a
inner join tblpizza b
on a.[pizza code]=b.[pizza code]
if you want to calculate the total per order try this:
select a.[#order],sum(a.quantity*b.price) as total
from tblorders a
inner join tblpizza b on a.[pizza code]=b.[pizza code]
group by a.[#order]
kostas
- 461
- 5
- 13