Sum Query

[Mina Garas]

Hi Eveerybody

I create database to do sum query but i face one problen that i want to discuss with u

first table called main list and contain code , description item , unit and beging quantity

second table called additioin and contain code, quantity and price
third table called issue and contain code and quantity

i put a primary key on code.main list
and i make relationship between Code.main list , code.addition and code.issue


and i mark on enforce referential integrity, cascade update related fields and cascade delete related records

and i made query to calculate sum quantity in my store and this query contains

code, description item, unit , beging quantity from main list table , quantity from addition table and quantity from issue table

i want to calculate sum of quantity.addition and quantity.issue

i created this query using query wizard and i mark on sum option button from summary in a query wizard

and in this query i add new column called stock and i create equation : (quantity.main list + quantity.addition - (quantity.issue)).

the problem that i had faced is the quantity in a query. maximized!!!!!!

for instance if i put 10 in quantity.addition for any code when i check that in a stock query i found 100 why i don't know!?

and the same when i put any number in quantity.issue

can anybody help me

[Mina Garas]

anybody help

[Mina Garas]

31 views and NoOne give any help??!!

[alansidman]

Suggest you post your db as it will be easier to respond if we are able to see what you have versus having to recreate a db with three tables. Everyone here is a volunteer and time is important. Make it easy for us to give you answers by providing your db with sample data.

[Mina Garas]

thank u very much sir
for your respond

[recyan]

Just check out if below gives some guidelines :

SELECT 
    tblMainList.ItemCode, 
    tblMainList.BeginningQuantity, 
    qryTotalItemsAdditions.ItemCode, 
    qryTotalItemsAdditions.SumOfAdditionQuantity, 
    qryTotalItemsIssues.ItemCode, 
    qryTotalItemsIssues.SumOfIssuedQuantity, 
    [BeginningQuantity]+[SumOfAdditionQuantity]-[SumOfIssuedQuantity] AS TheFinalBalance
FROM 
    (
        tblMainList 
        LEFT JOIN 
        (
            SELECT 
                tblAdditions.ItemCode, 
                Sum(tblAdditions.AdditionQuantity) AS SumOfAdditionQuantity
            FROM 
                tblAdditions
            GROUP BY 
                tblAdditions.ItemCode
        )
        AS qryTotalItemsAdditions 
        ON 
        tblMainList.ItemCode = qryTotalItemsAdditions.ItemCode
    ) 
    LEFT JOIN 
    (
        SELECT 
            tblIssues.ItemCode, 
            Sum(tblIssues.IssuedQuantity) AS SumOfIssuedQuantity
        FROM 
            tblIssues
        GROUP BY 
            tblIssues.ItemCode
    )
    AS qryTotalItemsIssues 
    ON 
    tblMainList.ItemCode = qryTotalItemsIssues.ItemCode;

Note : Take a look at Nz().
Edit : Assumption in query : tblMainlist will contain all the items, even if the BeginningQuantity is 0.

Thanks