How can pull this data?

[cap.zadi]

Hey there,

I have the tables:

TA1

TrnNo PK-AutoNumber
ProdnDate
BodyNo
Product
Type

TA2 Subtable of "TA1"
BatchNo
TrnNo
abc
dxy



TB1
RNo
Rdate
Product


TechnID
Reading
ProdnDate


I am preparing a query based on TB1 and i just need the following info from the above TA1.

*How i can pull the right "BodyNo" based and comparing on "ProdnDate" and "Product" which is existing in both TA1 and TB1.

please advise?

thanks

zee

[alansidman]

Suggest you post some sample data and expected results. Difficult to determine specifically what you are looking for. An example with data would be beneficial.

[cap.zadi]

sir i think i must do that but so far it is just a concern to make a query combining all three tables where from TA1 just need "BodyNo" field and TB1 all fields in query.

the linking key is the Prod'nDate and Product which is same in both TA1 and TB1.

thanks

[cap.zadi]

Sir can u also look at my this post. i am sure u can solve as its a urgent enquiry.


https://www.accessforums.net/reports...ort-21010.html

thanks

[alansidman]

Have you tried to make the query? What does the SQL statement look like? What results do you get? Please work with us and supply information that we can use to help you.

I could not open the file on the other thread. Please show some sample data and the expected results.

[cap.zadi]

Hello Sir,

i attached the sample database and having many questions related queries and reports but right now we will discuss about the few only and those are really urgent one and i need to solve tonight. i am sure you will help out as much as you can.

Question-1.

If you look at the tables "tblGlazing" and its sub-tables "tblGlazing1", "tblGlazing2" which are associated with One to Many relations.

what kind of query will work to show the combine sum results of these three tables based on ProdDate field and Trndate is not important to be part of it.

Question-2

How we can retrieved some fields from "TblPress" to a query which is based on tblMT11 and sub-table "tblRT11" fields.

- Required to pull "BodyNo" from "tblPress".

- Common and linking data is "ProdnDate" and "Item" which is existing in both "tblPress" and tables "tbeMT11" and "tblRT11".


Question-3

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

Set1
tblPress
tblPress1
tblPress2

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 know this will be very helpful through some relational database structure but just help now in current scenario to generate the results.


Please advise and very important to finished today.


i will be grateful to you.

thanks a lot.

zee

[cap.zadi]

Sir waiting for u reply plz?

[June7]

Hard to understand what you are asking for. Maybe this will get you started.

Question 1 - A Total (GROUP BY) query on the ProdnDate field.
SELECT tblGlazing1.ProdnDate, Sum(tblGlazing1.GoodQty) AS SumOfGoodQty, Sum(tblGlazing1.RejectQty) AS SumOfRejectQty, Sum(tblGlazing2.ReasonQty) AS SumOfReasonQty
FROM (tblGlazing LEFT JOIN tblGlazing1 ON tblGlazing.TrnNo = tblGlazing1.TrnNo) LEFT JOIN tblGlazing2 ON tblGlazing1.BatchNo = tblGlazing2.BatchNo
GROUP BY tblGlazing1.ProdnDate;

Question 2 - There is no TblPress but there is a tblPRS. Appears to be a simple SELECT with joins.
SELECT tblMT11.*, tblRT11.*, tblPRS.BodyNo
FROM tblPRS RIGHT JOIN (tblMT11 LEFT JOIN tblRT11 ON tblMT11.TestNo = tblRT11.TestNo) ON tblPRS.ProdnDate = tblRT11.ProdnDate;

Question 3 - Doubt this can be accomplished in one query, if at all. Might even need report/subreport arrangement. Not going to tackle it.

Often, getting the desired output is a matter of changing the JOIN TYPE of a query.

[cap.zadi]

Hello Sir,

thanks for ur time.

Question1.

It works in this way as you show the just sum of reasons qty but how we can show reject reasons in column heading against each record.?

Question-2.

Its working good. sorry for table mistake. its tblPRS.

but there is two type of BodyNo running on same production for each kind of product. so is it will show up the correct data?

as on same prodndate we have two products in tblPRS.

Product BodyNo
A 050
B 060


So is it not important when we retrieved data by linking tblPRS so 1st must match the product and then retrieved BodyNo?

please advise?

[recyan]

Question1.
It works in this way as you show the just sum of reasons qty but how we can show reject reasons in column heading against each record.?

Is it that you want the results of the query in the below manner :

ProdnDate    Bend    Crack
11/2/2011    1    2
12/18/2011        22
12/19/2011        12
12/20/2011        14
12/21/2011        29
12/22/2011        22
12/24/2011    1    5
12/27/2011    1    9 

Thanks

[cap.zadi]

Sir,

yes i need the results like that but along with the goodqty and reject qty.

ProdnDate GoodQty RejectQty Bend Crack

i will appreciate your inputs.

thanks

zee

[recyan]

yes i need the results like that but along with the goodqty and reject qty.

ProdnDate GoodQty RejectQty Bend Crack

Just check out if below gives some guidelines :

The sub-queries to be saved:
qryGlazingsRejectionReasons

SELECT 
    tblGlazing2.BatchNo, 
    TblRReason.Reason, 
    Sum(tblGlazing2.ReasonQty) AS SumOfReasonQty
FROM 
    tblGlazing2 
    INNER JOIN 
    TblRReason 
    ON 
    tblGlazing2.Reason = TblRReason.ReasonID
GROUP BY 
    tblGlazing2.BatchNo, TblRReason.Reason;

qryGlazingGoodRejection

SELECT 
    tblGlazing1.ProdnDate, 
    "GoodQuantity" AS GoodQuantity, 
    Sum(tblGlazing1.GoodQty) AS SumOfGoodQty
FROM 
    tblGlazing1
GROUP BY 
    tblGlazing1.ProdnDate
ORDER BY 
    tblGlazing1.ProdnDate
UNION ALL 
SELECT 
    tblGlazing1.ProdnDate, 
    "RejectionQuantity" AS RejectionQuantity, 
    Sum(tblGlazing1.RejectQty) AS SumOfGoodQty
FROM 
    tblGlazing1
GROUP BY 
    tblGlazing1.ProdnDate
ORDER BY 
    tblGlazing1.ProdnDate
UNION ALL 
SELECT 
    tblGlazing1.ProdnDate, 
    qryGlazingsRejectionReasons.Reason, 
    Sum(qryGlazingsRejectionReasons.SumOfReasonQty) AS SumOfSumOfReasonQty
FROM 
    qryGlazingsRejectionReasons 
    INNER JOIN 
    tblGlazing1 
    ON 
    qryGlazingsRejectionReasons.BatchNo = tblGlazing1.BatchNo
GROUP BY 
    tblGlazing1.ProdnDate, qryGlazingsRejectionReasons.Reason;

Try to to run this final query after saving it :

qryGlazingGoodRejection_Crosstab

TRANSFORM 
    Nz(Max(qryGlazingGoodRejection.[SumOfGoodQty]),0) AS MaxOfSumOfGoodQty
SELECT 
    qryGlazingGoodRejection.ProdnDate
FROM 
    qryGlazingGoodRejection
GROUP BY 
    qryGlazingGoodRejection.ProdnDate
PIVOT 
    qryGlazingGoodRejection.GoodQuantity;

Thanks

[cap.zadi]

Hey Sir,

that's working good so far. can we add other fields along with Prodndate if will be required like Product?

thanks for ur time and happy to see that.

can you look at my other questions as well especially the 3rd one.

[cap.zadi]

I just compare those results now and it seems to be the same with just direct Crosstab query.

TRANSFORM Sum(tblGlazing2.ReasonQty) AS SumOfReasonQty
SELECT tblGlazing1.ProdnDate, Sum(tblGlazing1.GoodQty) AS SumOfGoodQty, Sum(tblGlazing1.RejectQty) AS SumOfRejectQty
FROM tblGlazing INNER JOIN (tblGlazing1 INNER JOIN tblGlazing2 ON tblGlazing1.BatchNo = tblGlazing2.BatchNo) ON tblGlazing.TrnNo = tblGlazing1.TrnNo
GROUP BY tblGlazing1.ProdnDate
PIVOT tblGlazing2.Reason;

the difference is the column heading is named with reasons ID (1 and 3) instead of Name like Crack and Bend.

let me run the both in parallel for some time and see the data results.

thanks

zee

[recyan]

that's working good so far. can we add other fields along with Prodndate if will be required like Product?

You will have to fiddle around. Please note : The queries I have given may not be good. If you have time, sit with them & see if you can improve on them. Also test them thoroughly.

can you look at my other questions as well especially the 3rd one.

It's a bit difficult for me to understand what exactly you want from your questions. Will try whenever possible. Can you show us what the result of the queries for your 2nd & 3rd question should be? The way I showed you in my first reply. Perhaps it will be easier for others to help you. Thanks