How to use the data from other table into the main table in Access 2003 and 2007

Eddy

20 pts.

Tags:

Access 2003 forms

Access 2007 forms

Access Forms Database

Microsoft Access 2003

Microsoft Access 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

Asked: August 13, 2010 4:02 AM Last updated: August 19, 2010 3:44 PM

Answer Wiki

Last updated: August 13, 2010 2:14 PM GMT

Meandyou5,220 pts.
History
Contributors
- Ordered by most recent
- Meandyou5,220 pts.

Thanks. We'll let you know when a new response is added.

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.”

Discuss This Question: 3 Replies

Eddy Aug 17, 2010 5:19 PM GMT

Ok Thanks!

20 pointsBadges:

report
Randym Aug 18, 2010 1:39 PM GMT

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...)

1,740 pointsBadges:

report
Meandyou Aug 19, 2010 3:44 PM GMT

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. :-)

5,220 pointsBadges:

report