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

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

Answer Wiki

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Eddy
    Ok Thanks!
    20 pointsBadges:
    report
  • Randym
    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
    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

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following