431,982 Members | 1,738 Online
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

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.