How can pull this data?

[cap.zadi]

It is found that the total results are not ok beacuse some of the values are doubled like if



goodqty is 10 so it is showing 20 and same for rejectqty where as the sum of reject reasons is ok.?

what could be the case please?

[recyan]

It is found that the total results are not ok beacuse some of the values are doubled like if

goodqty is 10 so it is showing 20 and same for rejectqty where as the sum of reject reasons is ok.?

what could be the case please?

Are you using the 3 queries exactly the way they have been posted by me? If yes & still you are getting wrong results, try running each of the sub-queries separately first & see whether the results are proper & then run the final crosstab query. You should be able to find out where things are going wrong. Thanks

[cap.zadi]

Sir,

I will do check again.

[cap.zadi]

Dear Sir,

Question-3

How we can have combine query from the below given three sets of tables.

Set1
tblPrs
tblPrs1
tblPrs2

Set2
tblGlazing
tblGlazing1
tblGlazing2

Set3
tblSorting
tblSorting1
tblSorting2

Common and Process linking fields.

Set1 to Set2 "DCarNo", "ProdnDate", "Product"
Set2 to Set3 "KCarNo", "Product"


Process:

The "DCarNo" which is carrier to take "Product" through some process and reached at Set2 where those "DCarNo" unloaded and inspected then good one again passed ones glazed and loaded on "KCarNo" which is another carrier to take same product through some process and reached at set3 where again "KCarNo" is unloaded and inspected.

I am looking to have query in which can combine the data from Set1, SET2 and SET3.





* Selected Data field from Set1 Tables.
Process: Product is made from Clay Mix (BodyNo) by machines and loaded on DCarNo which is used as carrier to take the Item through some drying process.

ProdnDate    Machine  Item      BodyNo  DCarNo  GoodQty
10/01/2012    Prs1      ES200     030        8         18
10/01/2012    Prs2      ES400     060       10         6

* Selected Data field from Set2 Tables.
Process: those DryCarNo are received after some days and item is inspected and marked as good and reject qty and also record the reasons for reject qty. then those good quantities loaded on KilnCarNo (need many DryercarNo to complete one KilnCarNo) an other carrier to carry through some other firing process.

TrnDate        ProdnDate    Machine  Item     DCarNo  GoodQty RejectQty
 20/1/2012   10/01/2012    GL1      ES200       8         12          6        
 20/1/2012   10/01/2012    GL2      ES400      10         4            2

           RejectQty      Reason
                4               Crack
                2               Bend
                2               Crack

* Selected Data field from Set3 Tables.
Process: those KilnCarNo are received after some days and item is inspected and marked as good and reject qty and also record the reasons for reject qty.

 TrnDate          Machine   Item     KilnCarNo  GoodQty   RejectQty
 25/1/2012       GL1        ES200       90             10            2        
 25/1/2012       GL2        ES400       100            3             1

             RejectQty      Reason
                2               Crack
                1               Bend

Now how we can combine link the results to see the results based on production date through out the process.

ProdnDate    Item    BodyNo  ProdnQty  GoodQty1  RejectQty1  GoodQty2  
10/01/2012  ES200  030       18               12           6                10
10/01/2012  ES400  060        6                 4            2                3

RejectQty2
   2
   1

Or if want to see which KilnCarNo having results and when was loaded from set2 and when was produced and from which BodyNo it is belongs.


Hope it will be clear now?

will be waiting for ur time plz.

thanks

[cap.zadi]

Still waiting for advise.

thanks

zee

[June7]

I am finding your output requirements too complex for conventional query methodology and trying to explain a complete solution within forum too challenging. For instance, your request for this:
ProdnDate GoodQty RejectQty Bend Crack
cannot be done with a crosstab. I tried with IIf expressions. An IIf would be required for each Reason:
SELECT tblGlazing1.ProdnDate, Sum(tblGlazing1.GoodQty) AS SumOfGoodQty, Sum(tblGlazing1.RejectQty) AS SumOfRejectQty, Sum(tblGlazing2.ReasonQty) AS SumOfReasonQty, Sum(IIf([Reason]=1,1,0)) AS BendQty, Sum(IIf([Reason]=3,1,0)) AS CrackQty
FROM tblGlazing LEFT JOIN (tblGlazing1 LEFT JOIN tblGlazing2 ON tblGlazing1.BatchNo = tblGlazing2.BatchNo) ON tblGlazing.TrnNo = tblGlazing1.TrnNo
GROUP BY tblGlazing1.ProdnDate;

Might be something wrong with the data. tblGlazing1 has 55 records but a join of tblGlazing, tblGlazing1, tblGlazing2 results in 57 records.

You have been directed to a number of techniques to manipulate data. You will have to explore them and determine what best suits your situation. Queries alone will not always accomplish the desired output. Could involve any combination of queries, VBA code, report Grouping & Sorting with aggregate calcs, report/subreport.

[recyan]

I am looking to have query in which can combine the data from Set1, SET2 and SET3.
Now how we can combine link the results to see the results based on production date through out the process.

ProdnDate    Item    BodyNo  ProdnQty  GoodQty1  RejectQty1  GoodQty2  
10/01/2012  ES200  030       18               12           6                10
10/01/2012  ES400  060        6                 4            2                3

RejectQty2
   2
   1

It's a bit difficult for me to understand the various relatioships between Body, Item, Product, etc functionally.
But for your above result(only the good & reject qty for each stage), just check out if below gives some guidelines :

The sub-queries :

qryStage1GoodRejectQty

SELECT 
tblPRS.ProdnDate, 
Sum(tblPRS1.GoodQty) AS SumOfGoodQty, 
Sum(tblPRS1.RejectQty) AS SumOfRejectQty
FROM 
tblPRS INNER JOIN tblPRS1 ON tblPRS.TrnNo = tblPRS1.TrnNo
GROUP BY 
tblPRS.ProdnDate
ORDER BY 
tblPRS.ProdnDate;

qryStage2GoodRejectQty

SELECT 
tblGlazing1.ProdnDate, 
Sum(tblGlazing1.GoodQty) AS Stage2SumOfGoodQty, 
Sum(tblGlazing1.RejectQty) AS Stage2SumOfRejectQty
FROM 
tblGlazing1
GROUP BY 
tblGlazing1.ProdnDate
ORDER BY 
tblGlazing1.ProdnDate;

qryStage3GoodRejectQty

SELECT 
tblGlazing1.ProdnDate, 
Sum(tblSorting1.GoodQty) AS SumOfGoodQty, 
Sum(tblSorting1.RejectQty) AS SumOfRejectQty
FROM 
tblGlazing1 INNER JOIN tblSorting1 
ON 
tblGlazing1.KCarNo = tblSorting1.KCarNo
GROUP BY 
tblGlazing1.ProdnDate
ORDER BY 
tblGlazing1.ProdnDate;

qryProductionDates

SELECT 
ProdnDate
FROM 
tblPRS 
UNION 
SELECT
ProdnDate
FROM 
tblGlazing1;

The final query :
qryFinal

SELECT 
qryProductionDates.ProdnDate, 
qryStage1GoodRejectQty.ProdnDate, qryStage1GoodRejectQty.SumOfGoodQty, qryStage1GoodRejectQty.SumOfRejectQty, qryStage2GoodRejectQty.ProdnDate, qryStage2GoodRejectQty.Stage2SumOfGoodQty, qryStage2GoodRejectQty.Stage2SumOfRejectQty, qryStage3GoodRejectQty.ProdnDate, qryStage3GoodRejectQty.SumOfGoodQty, qryStage3GoodRejectQty.SumOfRejectQty
FROM 
(
(
qryProductionDates 
LEFT JOIN 
qryStage1GoodRejectQty 
ON 
qryProductionDates.ProdnDate = qryStage1GoodRejectQty.ProdnDate
) 
LEFT JOIN 
qryStage2GoodRejectQty 
ON 
qryProductionDates.ProdnDate = qryStage2GoodRejectQty.ProdnDate
) 
LEFT JOIN 
qryStage3GoodRejectQty 
ON 
qryProductionDates.ProdnDate = qryStage3GoodRejectQty.ProdnDate;

Am I missing something?

Thanks