By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,982 Members | 1,738 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,982 IT Pros & Developers. It's quick & easy.

Calculation across Tables

P: 2
Hi all, I am facing difficulties on creating a particular query.

Sample of my data table 1: (DetailsTable)

Order | Material | Quantity | Weight
123 | Orange | 10 |
123 | Apple | 20 |
123 | Guava | 20 |
233 | Orange | 10 |

Sample of my data table 2:(WeightsTable)

Order | Weight |
123 | 100 |
233 | 20 |

How can I create query to update my DetailsTable and allocate the weights proportionately (by quantity) to the individual order's material.

Sample outcome of data table1: (DetailsTable)

Order | Material | Quantity | Weight
123 | Orange | 10 | 20
123 | Apple | 20 | 40
123 | Guava | 20 | 40
233 | Orange | 10 | 20

Where weight of Order 123's Orange = 10/(10+20+20) * 100.

Thank you so much in advance.

Joe
Sep 29 '11 #1

✓ answered by Rabbit

If you wanted to store the weights of the individual line items in the first place, why have a weights table at all?

Since this is a calculation, it's better not to store the results of the calculation but to calculate on an as needed basis.

To calculate the individual weights, you can join the two tables and use a subquery to get the sum of the order quantity.

Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,364
If you wanted to store the weights of the individual line items in the first place, why have a weights table at all?

Since this is a calculation, it's better not to store the results of the calculation but to calculate on an as needed basis.

To calculate the individual weights, you can join the two tables and use a subquery to get the sum of the order quantity.
Sep 29 '11 #2

P: 2
Hi Rabbit,

The reason that the weights table exist is that the items are measured in orders and not individually.

However, during analysis, I would need the weights of individual items hence I have assumed that the weights is proportional to the quantity.

I was wondering if there is a simple query that can assist me in filling up the weights such as:
Expand|Select|Wrap|Line Numbers
  1. Update DetailsTable INNER JOIN WeightsTable on DetailsTable.Order= WeightsTable.Order
  2. Set DetailsTable.Weight = WeightsTable.Weight* DetailsTable.Quantity/(SUM(DetailsTable.Quantity)
  3. Group by Order
However, this query gives an error message.

Thank you so much in advance.
Oct 4 '11 #3

Rabbit
Expert Mod 10K+
P: 12,364
You'll want to try the subquery instead of the inner join.
Oct 4 '11 #4

Post your reply

Sign in to post your reply or Sign up for a free account.