Calculating Balance Stock Quantity from Two Queries (1 Viewer)

[hrdpgajjar]

Hi all,
I have created two queries with two different table values as under,

1. RDC Material Table
2. GGRC Material Table


Now i have created two query for sum of total material for each table as ,

1. DealerWiseRDCMaterialSummary
2. DealerWiseGGRCMaterialSummary

each table/ query has following columns

1. DealerID
2. DealerName
3. ProductID
4. ProductName
5. Unit
6. Quantity


Now i have created sum of quantity for each table and


Need difference between two quantity columns in a single query

I've tried to do it, the code is as under,


SELECT DealerWiseRDCMaterialSummary.DealerID, DealerWiseRDCMaterialSummary.DealerName, DealerWiseRDCMaterialSummary.ProductID, DealerWiseRDCMaterialSummary.ProductName, DealerWiseRDCMaterialSummary.SumOfQuantity, Sum(DealerWiseGGRCMaterialSummary.SumOfQuantity) AS SumOfSumOfQuantity1
FROM DealerWiseRDCMaterialSummary, DealerWiseGGRCMaterialSummary
GROUP BY DealerWiseRDCMaterialSummary.DealerID, DealerWiseRDCMaterialSummary.DealerName, DealerWiseRDCMaterialSummary.ProductID, DealerWiseRDCMaterialSummary.ProductName, DealerWiseRDCMaterialSummary.SumOfQuantity;



but sum of GGRC material quantity gets wrong.


How can i calculate difference of Sum of Material Quantity of Two queries in a single query or table.



Thanks

 

[bob fitz]

I suspect that your db is not normalized correctly. I would recommend that you sort that out first.
Why do you have two tables for Material?

 

[hrdpgajjar]

Two Tables required because there are two different types of Material supply,

1. RDC Material Supply Stock
2. GGRC Material Supply Stock


And i need to find out the balance stock between two i.e. RDC Material Stock - GGRC Material Stock

 

[bob fitz]

Two Tables required because there are two different types of Material supply,

1. RDC Material Supply Stock
2. GGRC Material Supply Stock


And i need to find out the balance stock between two i.e. RDC Material Stock - GGRC Material Stock

I still don't see the need for two Material tables. One table with a column/field for the "Type".
Also, why do you have a column/field for the Dealers Name and another for the Product Name?
I still feel that the DB needs to be normalized correctly.
Could you post a copy of the DB, or at least post a copy of your Relationships window?

 

[hrdpgajjar]

I still don't see the need for two Material tables. One table with a column/field for the "Type".
Also, why do you have a column/field for the Dealers Name and another for the Product Name?
I still feel that the DB needs to be normalized correctly.
Could you post a copy of the DB, or at least post a copy of your Relationships window?

 

[bob fitz]

Hmm. Unfortunately, I am still as confused about your table structure as I was at the beginning of this thread.

Perhaps it would help if you explained what the purpose of the DB is and then gave some explanation of what each table's data is about.
I suspect that the tables have been altered since you started this thread.

 

[Ken Sheridan]

You can Include a subquery which returns the data from a UNION of both tables in an outer query's SELECT clause. Values returned by the subquery can then be aggregated in the outer query. The following is a simple example in which the quantities per transaction are returned from a StockAcquisitions and a StockDisposals table in a subquery using a UNION ALL operation. In the case of the disposals the quantity value is multiplied by -1. By summing the quantities per product in the outer query the current stock in hand per product is then returned by the outer query:

SELECT
    ProductID,
    SUM(Quantity) AS StockInHand
FROM
    (
        SELECT
            "Acquisition" AS MovementType,
            ProductID,
            Quantity
        FROM
            StockAcquisitions
        UNION ALL
        SELECT
            "Disposal",
            ProductID,
            Quantity * -1
        FROM
            StockDisposals
    )
GROUP BY
    ProductID;

 

[mike60smart]

Cannot understand what you are trying to achieve.

You only have 1 table named "GGRCMaterialT"

Why do you need multiple queries based on the same table?

Please explain in detail what it is you are trying to do.

 

[plog]

Two Tables required because there are two different types of Material supply

Why don't you have one of these tables for each dealer? Why not then a table for each product?

Because you put that data where it belongs--in a field. You are able to handle multiple dealers and products in the same table because you have fields that allow you to differentiate what product and dealer each record is for .

You need to do the same for material type. Instead of differentiating material type with a whole new table, you use one table but have a field where you put the material type. Then when you need to total, you just total one table.

Additionally you've broken another rule of normalization by having fields for product and dealer names in those tables. Those fields are not necessary because you already have the ID field of each and should be able to link to you product and dealer tables to get all other information about your dealers and products.

Put this issue you posted about aside and work in properly structuring your tables and fields.