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

 

[hrdpgajjar]

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.

I have two tables RDC Material Table and GGRC Material Table and I am trying to find out balance stock (RDC Material - GGRC material). attached herewith my database file for your reference. Thanks

 

[hrdpgajjar]

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;

Thanks for the reply,
Following query works for me,


SELECT DealerT.DealerID, DealerT.DealerName, ProductT.ProductID, ProductT.ProductName, ProductT.Unit, Nz((SELECT Sum(RDCOrderT.Quantity)
FROM (DealerT AS d
INNER JOIN DispatchOrderT
ON d.DealerID = DispatchOrderT.DealerID)
INNER JOIN RDCOrderT
ON DispatchOrderT.DispatchOrderID = RDCOrderT.DispatchOrderID
WHERE d.DealerID = DealerT.DealerID
AND RDCOrderT.ProductID = ProductT.ProductID
),0) AS SumOfRDCQuantity, Nz((SELECT Sum(GGRCMaterialT.Quantity)
FROM (DealerT AS d2
INNER JOIN GGRCCaseT
ON d2.DealerID = GGRCCaseT.DealerID)
INNER JOIN GGRCMaterialT
ON GGRCCaseT.GGRCCaseID = GGRCMaterialT.GGRCCaseID
WHERE d2.DealerID = DealerT.DealerID
AND GGRCMaterialT.ProductID = ProductT.ProductID
),0) AS SumOfGGRCQuantity
FROM DealerT, ProductT;

 

[mike60smart]

I have two tables RDC Material Table and GGRC Material Table and I am trying to find out balance stock (RDC Material - GGRC material). attached herewith my database file for your reference. Thanks

Attached is the screenshot of your tables. I do not see a table named "RDCMaterialT" ??

 

[Ken Sheridan]

SELECT DealerT.DealerID, DealerT.DealerName, ProductT.ProductID, ProductT.ProductName, ProductT.Unit, Nz((SELECT Sum(RDCOrderT.Quantity)
FROM (DealerT AS d
INNER JOIN DispatchOrderT
ON d.DealerID = DispatchOrderT.DealerID)
INNER JOIN RDCOrderT
ON DispatchOrderT.DispatchOrderID = RDCOrderT.DispatchOrderID
WHERE d.DealerID = DealerT.DealerID
AND RDCOrderT.ProductID = ProductT.ProductID
),0) AS SumOfRDCQuantity, Nz((SELECT Sum(GGRCMaterialT.Quantity)
FROM (DealerT AS d2
INNER JOIN GGRCCaseT
ON d2.DealerID = GGRCCaseT.DealerID)
INNER JOIN GGRCMaterialT
ON GGRCCaseT.GGRCCaseID = GGRCMaterialT.GGRCCaseID
WHERE d2.DealerID = DealerT.DealerID
AND GGRCMaterialT.ProductID = ProductT.ProductID
),0) AS SumOfGGRCQuantity
FROM DealerT, ProductT;

That returns the Cartesian product of the DealerT and ProductT tables in the outer query. Each subquery returns the sum of the quantities from the RDCOrderT and GGRCMaterialT tables for each dealer/product combination. I see no balances returned. If the above query does what you want, however, the subqueries can be simplified as follows:

SELECT
    DealerT.DealerID,
    DealerT.DealerName,
    ProductT.ProductID,
    ProductT.ProductName,
    ProductT.Unit,
    Nz (
        (
            SELECT
                Sum(RDCOrderT.Quantity)
            FROM
                DispatchOrderT
                INNER JOIN RDCOrderT ON DispatchOrderT.DispatchOrderID = RDCOrderT.DispatchOrderID
            WHERE
                DispatchOrderT.DealerID = DealerT.DealerID
                AND RDCOrderT.ProductID = ProductT.ProductID
        ),
        0
    ) AS SumOfRDCQuantity,
    Nz (
        (
            SELECT
                Sum(GGRCMaterialT.Quantity)
            FROM
                GGRCCaseT
                INNER JOIN GGRCMaterialT ON GGRCCaseT.GGRCCaseID = GGRCMaterialT.GGRCCaseID
            WHERE
                GGRCCaseT.DealerID = DealerT.DealerID
                AND GGRCMaterialT.ProductID = ProductT.ProductID
        ),
        0
    ) AS SumOfGGRCQuantity
FROM
    DealerT,
    ProductT;