Can you help me..plss...SQL codes using MS Access

[danzrueda03]

ei, people..
I'm working on an e-commerce website's back-end. Im using MS Access for
my database.

My problem is that i don't know what sql codes should i use to query in
getting the SUM for the total_qty of products in a PRODUCT table.

This is the logic of what i was planning for retrieving the Total_qty
field in SQL. Based on the two tables below, i want to compute for the
total_qty by getting the sum of the ordered_qty per product_no from the
ORDER_LINE table and place the result to the Total_qty field in the
PRODUCT table.

TABLES
PRODUCT ORDER_LINE
Type Transaction_no
Product_no.(*primary key) Ordered_qty
Product_name Product_name
Total_qty Product_Type

Product_no(*foreign key)
Order_no

The result set should be like this...
| Type | Product_no.| Product_name | Total_qty |
| | | | |
| | | | |

Or if you can give me other suggestions on what to do with this type of
query..BTW, this is for the administrator's page of the website in
retrieving product summary..
I hope you can help me...Thanks in advance...

 

[Michel Walsh]

Hi,

In a new query, bring the two tables, join them through their Product_name
field. Click on the summation button on the toolbar to get an extra line,
total, in the query.

Bring the 3 fields Type, ProductNo, and ProductName in the grid, keep the
proposed GROUP BY.
Bring Order_quantity in the grid, change the proposed GROUP BY to SUM.

That's it. No real need to "store" the sum, just use that QUERY, *instead*
of the initial table, where you need to see the sum. A "stored" result is
only good at the moment it is done, is it one hour later? while a query is
up-to-date each time you run (use) it. Much better, isn't it? (for most
cases, it is).


Hoping it may help,
Vanderghast, Access MVP.