How to use the data from other table into the main table in Access 2003 and 2007
I'm trying to use data from other tables to make a sum and then display the result in a "Total" text.
For example: I have in table1 3 qty's, in table2 2 qty's and in table3 5 qty's. I want to sum all those qty's and display them into the Main Form. I use MS Access 03 and 07
Software/Hardware used:
Access 2007 and 2003
Software/Hardware used:
Access 2007 and 2003
ASKED:
August 13, 2010 4:02 AM
UPDATED:
August 19, 2010 3:44 PM
Answer Wiki:
Assuming there is some sensible way to join the tables, something like this should work:
<pre>
SELECT SUM (T1.col1 + T1.col2 + T1.col3
+ T2.col1 + T2.col2 + T3.col1 + T3.col2
+ T3.col3 + T3.col4 + T3.col5) AS TOTAL
FROM TABLE_1 T1,
TABLE_2 T2
TABLE_3 T3
WHERE ...
</pre>
I would be remiss if I did not point out that your tables seem to violate first normal form. The rule I refer to is "remove repeating columns."
To see all answers submitted to the Answer Wiki: View Answer History.
Ok Thanks!
Meandyou’s solution could give incorrect results if the tables have a one to many relationship because the qtys in the table on the one side would be added as many times as there are rows in the table on the many side of the join. A solution that would work with either one to many or one to one or perhaps no relationship at all would be a union query. In this eample, the union query is an inline view to get the sum of the qty’s:
select sum(totqty) from
(select qtyt+qty2+qty3 as totqty from t1 where…
union
select qty1+qty2 from t2 where…
union
select qty1+qty2+qty3+qty4+qty5 from t3 where…)
Good point Randym.
Even though I mentioned “assuming there is a sensible join criteria”, you are correct. My answer was for 1:1:1.
My intention, more and more, has become to give a starting point and then let people learn.
I would hope that any incorrect results caused by 2:1 relationships or 1:0 relationships would be caught during extensive testing before deployment.