Error 3048

The_Doc_Man said:
I understand the concept of layered queries. I've used six-layer queries myself once or twice. They worked fine for me. Normally, though, you don't consume resources like that when opening a query unless there is something odd about the query. Which is why I asked if you could post the SQL of the seven or eight queries.
Click to expand...

There are SQL sentences for layered quries

Qry1
SELECT QOD_OrdLog.ODID, QOD_OrdLog.ODPK, QOD_OrdLog.PDate, QOD_OrdLog.Kitchen, QOD_OrdLog.Job_Order, QOD_OrdLog.TDPK, QOD_OrdLog.ERP_Code AS FP_ERP_Code, QOD_OrdLog.Item_Eng AS FP_Eng_Name, QOD_OrdLog.Item_Arb AS FP_Arb_Name, QFP_RecPHIP.Unit_Arb, QOD_OrdLog.OQty, QFP_RecPHIP.RPFPType, QFP_RecPHIP.PHIPPK, QFP_RecPHIP.RawCode, QFP_RecPHIP.ERP_Code, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb, QFP_RecPHIP.Unit, QFP_RecPHIP.PQty, [PQty]*[OQty] AS Batch
FROM QOD_OrdLog INNER JOIN QFP_RecPHIP ON QOD_OrdLog.TDPK = QFP_RecPHIP.TDPK;

Qry2
SELECT QOD_FP.ODID, QOD_FP.ODPK, QOD_FP.PDate, QOD_FP.Kitchen, QOD_FP.Job_Order, QOD_FP.TDPK, QOD_FP.PHIPPK, QOD_FP.ERP_Code, QOD_FP.RawCode, QOD_FP.Item_Eng, QOD_FP.Item_Arb, QFP_RecPHIP.PHIP_Unit, QFP_RecPHIP.Unit_Arb, QFP_RecPHIP.RecipePK, QFP_RecPHIP.PHIPPK AS PHIPPK1, QFP_RecPHIP.RPFPType AS PHIP1_Type, QFP_RecPHIP.RawCode AS RawCode1, QFP_RecPHIP.ERP_Code AS ERP_Code1, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng1, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb1, QFP_RecPHIP.Unit AS PHIP1_Unit, QOD_FP.OQty, QFP_RecPHIP.PQty AS PHIP1_Qty, [OQty]*[PHIP1_Qty] AS PHIP1_Batch
FROM QOD_FP LEFT JOIN QFP_RecPHIP ON QOD_FP.PHIPPK = QFP_RecPHIP.TDPK;

Qry3
SELECT QOD_PHIP1Filling.ODID, QOD_PHIP1Filling.ODPK, QOD_PHIP1Filling.PDate, QOD_PHIP1Filling.Kitchen, QOD_PHIP1Filling.Job_Order, QOD_PHIP1Filling.TDPK, QOD_PHIP1Filling.PHIPPK1, QOD_PHIP1Filling.ERP_Code1, QOD_PHIP1Filling.RawCode1, QOD_PHIP1Filling.PHIP1_Type, QOD_PHIP1Filling.RPFP_Eng1, QOD_PHIP1Filling.RPFP_Arb1, QOD_PHIP1Filling.PHIP1_Unit, QFP_RecPHIP.Unit_Arb, QFP_RecPHIP.RecipePK, QFP_RecPHIP.PHIPPK AS PHIPPK2, QFP_RecPHIP.RPFPType AS PHIP2_Type, QFP_RecPHIP.RawCode AS RawCode2, QFP_RecPHIP.ERP_Code AS ERP_Code2, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng2, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb2, QFP_RecPHIP.Unit AS PHIP_Unit2, QOD_PHIP1Filling.PHIP1_Batch, QFP_RecPHIP.PQty AS PHIP2_Qty, [PHIP1_Batch]*[PQty] AS PHIP2_Batch
FROM QOD_PHIP1Filling LEFT JOIN QFP_RecPHIP ON QOD_PHIP1Filling.PHIPPK1 = QFP_RecPHIP.TDPK
WHERE (((QOD_PHIP1Filling.PHIP1_Unit)="Pcs"));

Qry4
SELECT QOD_SumPHIP2.ODID, QOD_SumPHIP2.ODPK, QOD_SumPHIP2.PDate, QOD_SumPHIP2.Kitchen, QOD_SumPHIP2.Job_Order, QOD_SumPHIP2.TDPK, QOD_SumPHIP2.PHIPPK1, QOD_SumPHIP2.RawCode1, QOD_SumPHIP2.ERP_Code1, QOD_SumPHIP2.PHIP1_Type, QOD_SumPHIP2.RPFP_Eng1, QOD_SumPHIP2.RPFP_Arb1, QFP_RecPHIP.PHIP_Unit AS PHIP1_Unit, QFP_RecPHIP.Unit_Arb, QFP_RecPHIP.PHIPPK AS PHIPPK3, QFP_RecPHIP.RecipePK, QFP_RecPHIP.RawCode AS RawCode3, QFP_RecPHIP.ERP_Code AS ERP_Code3, QFP_RecPHIP.RPFPType AS PHIP3_Type, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng3, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb3, QFP_RecPHIP.Unit AS PHIP3_Unit, QOD_SumPHIP2.PHIP1_Batch, ([PHIP1_Batch]/[QFP_RecPHIP]![PHIP_LR])*[QFP_RecPHIP]![IngPer] AS PHIP3_Batch
FROM QOD_SumPHIP2 LEFT JOIN QFP_RecPHIP ON QOD_SumPHIP2.PHIPPK1 = QFP_RecPHIP.TDPK
WHERE (((QOD_SumPHIP2.PHIP1_Type) Like "IP" Or (QOD_SumPHIP2.PHIP1_Type)="PH"));

Qry5
SELECT QOD_PHIP3.ODID, QOD_PHIP3.ODPK, QOD_PHIP3.PDate, QOD_PHIP3.Kitchen, QOD_PHIP3.Job_Order, QOD_PHIP3.TDPK, QOD_PHIP3.PHIPPK3, QOD_PHIP3.RawCode3, QOD_PHIP3.ERP_Code3, QOD_PHIP3.PHIP3_Type, QOD_PHIP3.RPFP_Eng3, QOD_PHIP3.RPFP_Arb3, QFP_RecPHIP.PHIP_Unit AS PHIP3_Unit, QFP_RecPHIP.Unit_Arb, QFP_RecPHIP.PHIPPK AS PHIPPK4, QFP_RecPHIP.RecipePK, QFP_RecPHIP.RawCode AS RawCode4, QFP_RecPHIP.ERP_Code AS ERP_Code4, QFP_RecPHIP.RPFPType AS PHIP4_Type, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng4, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb4, QFP_RecPHIP.Unit AS PHIP_Unit4, QOD_PHIP3.PHIP3_Batch, ([PHIP3_Batch]/[QFP_RecPHIP]![PHIP_LR])*[QFP_RecPHIP]![IngPer] AS PHIP4_Batch
FROM QOD_PHIP3 LEFT JOIN QFP_RecPHIP ON QOD_PHIP3.PHIPPK3 = QFP_RecPHIP.TDPK
WHERE (((QOD_PHIP3.PHIP3_Type) Like "IP" Or (QOD_PHIP3.PHIP3_Type)="PH"));

Qry6
SELECT QOD_PHIP4.ODID, QOD_PHIP4.ODPK, QOD_PHIP4.PDate, QOD_PHIP4.Kitchen, QOD_PHIP4.Job_Order, QOD_PHIP4.TDPK, QOD_PHIP4.PHIPPK4, QOD_PHIP4.RawCode4, QOD_PHIP4.ERP_Code4, QOD_PHIP4.PHIP4_Type, QOD_PHIP4.RPFP_Eng4, QOD_PHIP4.RPFP_Arb4, QFP_RecPHIP.PHIP_Unit AS PHIP4_Unit, QFP_RecPHIP.Unit_Arb, QFP_RecPHIP.PHIPPK AS PHIPPK5, QFP_RecPHIP.RecipePK, QFP_RecPHIP.RawCode AS RawCode5, QFP_RecPHIP.ERP_Code AS ERP_Code5, QFP_RecPHIP.RPFPType AS PHIP5_Type, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng5, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb5, QFP_RecPHIP.Unit AS PHIP5_Unit, QOD_PHIP4.PHIP4_Batch, ([PHIP4_Batch]/[QFP_RecPHIP]![PHIP_LR])*[QFP_RecPHIP]![IngPer] AS PHIP5_Batch
FROM QOD_PHIP4 LEFT JOIN QFP_RecPHIP ON QOD_PHIP4.PHIPPK4 = QFP_RecPHIP.TDPK
WHERE (((QOD_PHIP4.PHIP4_Type) Like "IP" Or (QOD_PHIP4.PHIP4_Type)="PH"));

Qry7 (Union query)
SELECT QOD_SumPHIP2.ODID, QOD_SumPHIP2.ODPK, QOD_SumPHIP2.PDate, QOD_SumPHIP2.Kitchen, QOD_SumPHIP2.Job_Order, QOD_SumPHIP2.TDPK, QOD_SumPHIP2.PHIPPK1, QOD_SumPHIP2.RawCode1, QOD_SumPHIP2.ERP_Code1, QOD_SumPHIP2.PHIP1_Type, QOD_SumPHIP2.RPFP_Eng1, QOD_SumPHIP2.RPFP_Arb1, QFP_RecPHIP.PHIP_Unit AS PHIP1_Unit, QFP_RecPHIP.Unit_Arb, QFP_RecPHIP.PHIPPK AS PHIPPK3, QFP_RecPHIP.RecipePK, QFP_RecPHIP.RawCode AS RawCode3, QFP_RecPHIP.ERP_Code AS ERP_Code3, QFP_RecPHIP.RPFPType AS PHIP3_Type, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng3, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb3, QFP_RecPHIP.Unit AS PHIP3_Unit, QOD_SumPHIP2.PHIP1_Batch, ([PHIP1_Batch]/[QFP_RecPHIP]![PHIP_LR])*[QFP_RecPHIP]![IngPer] AS PHIP3_Batch
FROM QOD_SumPHIP2 LEFT JOIN QFP_RecPHIP ON QOD_SumPHIP2.PHIPPK1 = QFP_RecPHIP.TDPK
WHERE (((QOD_SumPHIP2.PHIP1_Type) Not Like "Raw"));


UNION SELECT QOD_PHIP3.ODID, QOD_PHIP3.ODPK, QOD_PHIP3.PDate, QOD_PHIP3.Kitchen, QOD_PHIP3.Job_Order, QOD_PHIP3.TDPK, QOD_PHIP3.PHIPPK3, QOD_PHIP3.RawCode3, QOD_PHIP3.ERP_Code3, QOD_PHIP3.PHIP3_Type, QOD_PHIP3.RPFP_Eng3, QOD_PHIP3.RPFP_Arb3, QFP_RecPHIP.PHIP_Unit AS PHIP3_Unit, QFP_RecPHIP.Unit_Arb, QFP_RecPHIP.PHIPPK AS PHIPPK4, QFP_RecPHIP.RecipePK, QFP_RecPHIP.RawCode AS RawCode4, QFP_RecPHIP.ERP_Code AS ERP_Code4, QFP_RecPHIP.RPFPType AS PHIP4_Type, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng4, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb4, QFP_RecPHIP.Unit AS PHIP_Unit4, QOD_PHIP3.PHIP3_Batch, ([PHIP3_Batch]/[QFP_RecPHIP]![PHIP_LR])*[QFP_RecPHIP]![IngPer] AS PHIP4_Batch
FROM QOD_PHIP3 LEFT JOIN QFP_RecPHIP ON QOD_PHIP3.PHIPPK3 = QFP_RecPHIP.TDPK
WHERE (((QOD_PHIP3.PHIP3_Type) Like "IP" Or (QOD_PHIP3.PHIP3_Type)="PH"));

UNION SELECT QOD_PHIP4.ODID, QOD_PHIP4.ODPK, QOD_PHIP4.PDate, QOD_PHIP4.Kitchen, QOD_PHIP4.Job_Order, QOD_PHIP4.TDPK, QOD_PHIP4.PHIPPK4, QOD_PHIP4.RawCode4, QOD_PHIP4.ERP_Code4, QOD_PHIP4.PHIP4_Type, QOD_PHIP4.RPFP_Eng4, QOD_PHIP4.RPFP_Arb4, QFP_RecPHIP.PHIP_Unit AS PHIP4_Unit, QFP_RecPHIP.Unit_Arb, QFP_RecPHIP.PHIPPK AS PHIPPK5, QFP_RecPHIP.RecipePK, QFP_RecPHIP.RawCode AS RawCode5, QFP_RecPHIP.ERP_Code AS ERP_Code5, QFP_RecPHIP.RPFPType AS PHIP5_Type, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng5, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb5, QFP_RecPHIP.Unit AS PHIP5_Unit, QOD_PHIP4.PHIP4_Batch, ([PHIP4_Batch]/[QFP_RecPHIP]![PHIP_LR])*[QFP_RecPHIP]![IngPer] AS PHIP5_Batch
FROM QOD_PHIP4 LEFT JOIN QFP_RecPHIP ON QOD_PHIP4.PHIPPK4 = QFP_RecPHIP.TDPK
WHERE (((QOD_PHIP4.PHIP4_Type) Like "IP" Or (QOD_PHIP4.PHIP4_Type)="PH"));

Qry8 (Union query) when i use this one message of Error 3048 appear
SELECT QOD_FP.ODID, QOD_FP.ODPK, QOD_FP.PDate, QOD_FP.Kitchen, QOD_FP.Job_Order, QOD_FP.TDPK, QFP_RecPHIP.PHIPPK AS PHIPPK1, QFP_RecPHIP.RPFPType AS PHIP1_Type, QFP_RecPHIP.RawCode AS RawCode1, QFP_RecPHIP.ERP_Code AS ERP_Code1, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng1, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb1, QFP_RecPHIP.Unit AS PHIP1_Unit, [QFP_RecPHIP]![PQty]*[QOD_FP]![OQty] AS PHIP1_Batch
FROM QOD_FP LEFT JOIN QFP_RecPHIP ON QOD_FP.PHIPPK = QFP_RecPHIP.TDPK
WHERE (((QFP_RecPHIP.RPFPType) Not Like "Raw") AND ((QFP_RecPHIP.Unit) Not Like "Pcs"));

UNION SELECT QOD_PHIP1Filling.ODID, QOD_PHIP1Filling.ODPK, QOD_PHIP1Filling.PDate, QOD_PHIP1Filling.Kitchen, QOD_PHIP1Filling.Job_Order, QOD_PHIP1Filling.TDPK, QFP_RecPHIP.PHIPPK AS PHIPPK2, QFP_RecPHIP.RPFPType AS PHIP2_Type, QFP_RecPHIP.RawCode AS RawCode2, QFP_RecPHIP.ERP_Code AS ERP_Code2, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng2, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb2, QFP_RecPHIP.Unit AS PHIP_Unit2, [QOD_PHIP1Filling]![PHIP1_Batch]*[QFP_RecPHIP]![PQty] AS PHIP2_Batch
FROM QOD_PHIP1Filling LEFT JOIN QFP_RecPHIP ON QOD_PHIP1Filling.PHIPPK1 = QFP_RecPHIP.TDPK
WHERE (((QOD_PHIP1Filling.PHIP1_Unit)="Pcs"));

Thanks
 
OK, first a side comment. You have several examples of "Like" or "Not Like" in your queries but there are no wild cards in the literal comparison values. You might as well use "=" or "<>" because any "Like" variant without wild cards is the same as equals or not equals.

Second, you say "layered" queries but we don't know the names of the layers. None of the FROM clauses have names that match what you used to enumerate your queries. Therefore, EITHER you have omitted query names or something else is going on. If we cannot match up names to layers, it will be hard as heck to analyze.

Third, these queries SEEM to be taking from tables that have some kind of related structure where they are all taking from fields named similarly. I'm wondering about the normalization of these tables.
 
Addition: It is not clear what tables and what queries are and what the connections are.
To understand the logic in a (foreign) database, you always need a picture of the database schema. Please complete that.

Something that stands out to me:
FROM QOD_FP LEFT JOIN QFP_RecPHIP ON QOD_FP.PHIPPK = QFP_RecPHIP.TDPK

The JOIN to the marked table(?) takes place in every subquery. For me, this would be a reason to first summarize the things on the left side of the JOIN across all tables. As already mentioned, you will very quickly encounter problems in the data modeling that cause the enormous effort shown.
Based on experience, more than 70 percent of the use of UNION queries indicates insufficient data modeling; nested UNION queries are also a huge performance problem.

What is also noticeable: The actual calculations are minor, each one JOIN and possibly a filter. But a huge amount of fields is always carried. If the contents of these fields always have to be loaded and carried along, it puts a lot of strain on the memory, which also negatively affects performance.

The goal should always be slim (only the fields that you really need) and flat (only the records that you need) recordsets.
 
Eberhard's point is valid and triggered this thought: The multi-layered UNION that contains the same JOIN each time forces re-evaluation of the same table for each "leg" of the UNION operation, so you are revisiting that table in a way that it has to count against you. To execute a query, SQL must open EACH TABLE (or sub-query) listed in a FROM clause. Each "FROM" source is a database connection. So at the base level, one JOIN is two connections. Looking deeper into that wall of text, I ALSO note a couple of "SUM" items implying the presence of aggregate queries - but I don't see them. All of the queries you showed us are NOT aggregate queries that would generate sums.

If I am to assume you used an orderly naming convention, ALL of those queries called OTHER queries because ALL of the FROM sources started with the letter "Q" and I believe you to have been methodical in your naming choices. So THAT means we STILL don't have any idea of your table structure or, as Eberhard points out, how things are related. But the presence of a sequence of names such as PHIP5_Batch, Item_Arb1, RawCode3, etc. - repeated names with numbers to differentiate them - suggests a seriously non-normalized layout.

Please understand that I do not criticize you for going in this direction... if you have difficult and wildly entangled data relationships, it can be daunting to untangle them. But I am suggesting that a bit more data structure analysis might be beneficial to further disentangle the "mess" you find yourself in.

Try to step back from the low level and tell us what is actually going on here. It appears to me from your naming that you have some kind of industrial process that is being monitored/simulated. Without giving away any industrial secrets, what's the big picture here?
 
Mina Garas Daniel said:
There are SQL sentences for layered quries

Qry1
SELECT QOD_OrdLog.ODID, QOD_OrdLog.ODPK, QOD_OrdLog.PDate, QOD_OrdLog.Kitchen, QOD_OrdLog.Job_Order, QOD_OrdLog.TDPK, QOD_OrdLog.ERP_Code AS FP_ERP_Code, QOD_OrdLog.Item_Eng AS FP_Eng_Name, QOD_OrdLog.Item_Arb AS FP_Arb_Name, QFP_RecPHIP.Unit_Arb, QOD_OrdLog.OQty, QFP_RecPHIP.RPFPType, QFP_RecPHIP.PHIPPK, QFP_RecPHIP.RawCode, QFP_RecPHIP.ERP_Code, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb, QFP_RecPHIP.Unit, QFP_RecPHIP.PQty, [PQty]*[OQty] AS Batch
FROM QOD_OrdLog INNER JOIN QFP_RecPHIP ON QOD_OrdLog.TDPK = QFP_RecPHIP.TDPK;

Qry2
SELECT QOD_FP.ODID, QOD_FP.ODPK, QOD_FP.PDate, QOD_FP.Kitchen, QOD_FP.Job_Order, QOD_FP.TDPK, QOD_FP.PHIPPK, QOD_FP.ERP_Code, QOD_FP.RawCode, QOD_FP.Item_Eng, QOD_FP.Item_Arb, QFP_RecPHIP.PHIP_Unit, QFP_RecPHIP.Unit_Arb, QFP_RecPHIP.RecipePK, QFP_RecPHIP.PHIPPK AS PHIPPK1, QFP_RecPHIP.RPFPType AS PHIP1_Type, QFP_RecPHIP.RawCode AS RawCode1, QFP_RecPHIP.ERP_Code AS ERP_Code1, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng1, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb1, QFP_RecPHIP.Unit AS PHIP1_Unit, QOD_FP.OQty, QFP_RecPHIP.PQty AS PHIP1_Qty, [OQty]*[PHIP1_Qty] AS PHIP1_Batch
FROM QOD_FP LEFT JOIN QFP_RecPHIP ON QOD_FP.PHIPPK = QFP_RecPHIP.TDPK;

Qry3
SELECT QOD_PHIP1Filling.ODID, QOD_PHIP1Filling.ODPK, QOD_PHIP1Filling.PDate, QOD_PHIP1Filling.Kitchen, QOD_PHIP1Filling.Job_Order, QOD_PHIP1Filling.TDPK, QOD_PHIP1Filling.PHIPPK1, QOD_PHIP1Filling.ERP_Code1, QOD_PHIP1Filling.RawCode1, QOD_PHIP1Filling.PHIP1_Type, QOD_PHIP1Filling.RPFP_Eng1, QOD_PHIP1Filling.RPFP_Arb1, QOD_PHIP1Filling.PHIP1_Unit, QFP_RecPHIP.Unit_Arb, QFP_RecPHIP.RecipePK, QFP_RecPHIP.PHIPPK AS PHIPPK2, QFP_RecPHIP.RPFPType AS PHIP2_Type, QFP_RecPHIP.RawCode AS RawCode2, QFP_RecPHIP.ERP_Code AS ERP_Code2, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng2, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb2, QFP_RecPHIP.Unit AS PHIP_Unit2, QOD_PHIP1Filling.PHIP1_Batch, QFP_RecPHIP.PQty AS PHIP2_Qty, [PHIP1_Batch]*[PQty] AS PHIP2_Batch
FROM QOD_PHIP1Filling LEFT JOIN QFP_RecPHIP ON QOD_PHIP1Filling.PHIPPK1 = QFP_RecPHIP.TDPK
WHERE (((QOD_PHIP1Filling.PHIP1_Unit)="Pcs"));

Qry4
SELECT QOD_SumPHIP2.ODID, QOD_SumPHIP2.ODPK, QOD_SumPHIP2.PDate, QOD_SumPHIP2.Kitchen, QOD_SumPHIP2.Job_Order, QOD_SumPHIP2.TDPK, QOD_SumPHIP2.PHIPPK1, QOD_SumPHIP2.RawCode1, QOD_SumPHIP2.ERP_Code1, QOD_SumPHIP2.PHIP1_Type, QOD_SumPHIP2.RPFP_Eng1, QOD_SumPHIP2.RPFP_Arb1, QFP_RecPHIP.PHIP_Unit AS PHIP1_Unit, QFP_RecPHIP.Unit_Arb, QFP_RecPHIP.PHIPPK AS PHIPPK3, QFP_RecPHIP.RecipePK, QFP_RecPHIP.RawCode AS RawCode3, QFP_RecPHIP.ERP_Code AS ERP_Code3, QFP_RecPHIP.RPFPType AS PHIP3_Type, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng3, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb3, QFP_RecPHIP.Unit AS PHIP3_Unit, QOD_SumPHIP2.PHIP1_Batch, ([PHIP1_Batch]/[QFP_RecPHIP]![PHIP_LR])*[QFP_RecPHIP]![IngPer] AS PHIP3_Batch
FROM QOD_SumPHIP2 LEFT JOIN QFP_RecPHIP ON QOD_SumPHIP2.PHIPPK1 = QFP_RecPHIP.TDPK
WHERE (((QOD_SumPHIP2.PHIP1_Type) Like "IP" Or (QOD_SumPHIP2.PHIP1_Type)="PH"));

Qry5
SELECT QOD_PHIP3.ODID, QOD_PHIP3.ODPK, QOD_PHIP3.PDate, QOD_PHIP3.Kitchen, QOD_PHIP3.Job_Order, QOD_PHIP3.TDPK, QOD_PHIP3.PHIPPK3, QOD_PHIP3.RawCode3, QOD_PHIP3.ERP_Code3, QOD_PHIP3.PHIP3_Type, QOD_PHIP3.RPFP_Eng3, QOD_PHIP3.RPFP_Arb3, QFP_RecPHIP.PHIP_Unit AS PHIP3_Unit, QFP_RecPHIP.Unit_Arb, QFP_RecPHIP.PHIPPK AS PHIPPK4, QFP_RecPHIP.RecipePK, QFP_RecPHIP.RawCode AS RawCode4, QFP_RecPHIP.ERP_Code AS ERP_Code4, QFP_RecPHIP.RPFPType AS PHIP4_Type, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng4, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb4, QFP_RecPHIP.Unit AS PHIP_Unit4, QOD_PHIP3.PHIP3_Batch, ([PHIP3_Batch]/[QFP_RecPHIP]![PHIP_LR])*[QFP_RecPHIP]![IngPer] AS PHIP4_Batch
FROM QOD_PHIP3 LEFT JOIN QFP_RecPHIP ON QOD_PHIP3.PHIPPK3 = QFP_RecPHIP.TDPK
WHERE (((QOD_PHIP3.PHIP3_Type) Like "IP" Or (QOD_PHIP3.PHIP3_Type)="PH"));

Qry6
SELECT QOD_PHIP4.ODID, QOD_PHIP4.ODPK, QOD_PHIP4.PDate, QOD_PHIP4.Kitchen, QOD_PHIP4.Job_Order, QOD_PHIP4.TDPK, QOD_PHIP4.PHIPPK4, QOD_PHIP4.RawCode4, QOD_PHIP4.ERP_Code4, QOD_PHIP4.PHIP4_Type, QOD_PHIP4.RPFP_Eng4, QOD_PHIP4.RPFP_Arb4, QFP_RecPHIP.PHIP_Unit AS PHIP4_Unit, QFP_RecPHIP.Unit_Arb, QFP_RecPHIP.PHIPPK AS PHIPPK5, QFP_RecPHIP.RecipePK, QFP_RecPHIP.RawCode AS RawCode5, QFP_RecPHIP.ERP_Code AS ERP_Code5, QFP_RecPHIP.RPFPType AS PHIP5_Type, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng5, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb5, QFP_RecPHIP.Unit AS PHIP5_Unit, QOD_PHIP4.PHIP4_Batch, ([PHIP4_Batch]/[QFP_RecPHIP]![PHIP_LR])*[QFP_RecPHIP]![IngPer] AS PHIP5_Batch
FROM QOD_PHIP4 LEFT JOIN QFP_RecPHIP ON QOD_PHIP4.PHIPPK4 = QFP_RecPHIP.TDPK
WHERE (((QOD_PHIP4.PHIP4_Type) Like "IP" Or (QOD_PHIP4.PHIP4_Type)="PH"));

Qry7 (Union query)
SELECT QOD_SumPHIP2.ODID, QOD_SumPHIP2.ODPK, QOD_SumPHIP2.PDate, QOD_SumPHIP2.Kitchen, QOD_SumPHIP2.Job_Order, QOD_SumPHIP2.TDPK, QOD_SumPHIP2.PHIPPK1, QOD_SumPHIP2.RawCode1, QOD_SumPHIP2.ERP_Code1, QOD_SumPHIP2.PHIP1_Type, QOD_SumPHIP2.RPFP_Eng1, QOD_SumPHIP2.RPFP_Arb1, QFP_RecPHIP.PHIP_Unit AS PHIP1_Unit, QFP_RecPHIP.Unit_Arb, QFP_RecPHIP.PHIPPK AS PHIPPK3, QFP_RecPHIP.RecipePK, QFP_RecPHIP.RawCode AS RawCode3, QFP_RecPHIP.ERP_Code AS ERP_Code3, QFP_RecPHIP.RPFPType AS PHIP3_Type, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng3, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb3, QFP_RecPHIP.Unit AS PHIP3_Unit, QOD_SumPHIP2.PHIP1_Batch, ([PHIP1_Batch]/[QFP_RecPHIP]![PHIP_LR])*[QFP_RecPHIP]![IngPer] AS PHIP3_Batch
FROM QOD_SumPHIP2 LEFT JOIN QFP_RecPHIP ON QOD_SumPHIP2.PHIPPK1 = QFP_RecPHIP.TDPK
WHERE (((QOD_SumPHIP2.PHIP1_Type) Not Like "Raw"));


UNION SELECT QOD_PHIP3.ODID, QOD_PHIP3.ODPK, QOD_PHIP3.PDate, QOD_PHIP3.Kitchen, QOD_PHIP3.Job_Order, QOD_PHIP3.TDPK, QOD_PHIP3.PHIPPK3, QOD_PHIP3.RawCode3, QOD_PHIP3.ERP_Code3, QOD_PHIP3.PHIP3_Type, QOD_PHIP3.RPFP_Eng3, QOD_PHIP3.RPFP_Arb3, QFP_RecPHIP.PHIP_Unit AS PHIP3_Unit, QFP_RecPHIP.Unit_Arb, QFP_RecPHIP.PHIPPK AS PHIPPK4, QFP_RecPHIP.RecipePK, QFP_RecPHIP.RawCode AS RawCode4, QFP_RecPHIP.ERP_Code AS ERP_Code4, QFP_RecPHIP.RPFPType AS PHIP4_Type, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng4, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb4, QFP_RecPHIP.Unit AS PHIP_Unit4, QOD_PHIP3.PHIP3_Batch, ([PHIP3_Batch]/[QFP_RecPHIP]![PHIP_LR])*[QFP_RecPHIP]![IngPer] AS PHIP4_Batch
FROM QOD_PHIP3 LEFT JOIN QFP_RecPHIP ON QOD_PHIP3.PHIPPK3 = QFP_RecPHIP.TDPK
WHERE (((QOD_PHIP3.PHIP3_Type) Like "IP" Or (QOD_PHIP3.PHIP3_Type)="PH"));

UNION SELECT QOD_PHIP4.ODID, QOD_PHIP4.ODPK, QOD_PHIP4.PDate, QOD_PHIP4.Kitchen, QOD_PHIP4.Job_Order, QOD_PHIP4.TDPK, QOD_PHIP4.PHIPPK4, QOD_PHIP4.RawCode4, QOD_PHIP4.ERP_Code4, QOD_PHIP4.PHIP4_Type, QOD_PHIP4.RPFP_Eng4, QOD_PHIP4.RPFP_Arb4, QFP_RecPHIP.PHIP_Unit AS PHIP4_Unit, QFP_RecPHIP.Unit_Arb, QFP_RecPHIP.PHIPPK AS PHIPPK5, QFP_RecPHIP.RecipePK, QFP_RecPHIP.RawCode AS RawCode5, QFP_RecPHIP.ERP_Code AS ERP_Code5, QFP_RecPHIP.RPFPType AS PHIP5_Type, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng5, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb5, QFP_RecPHIP.Unit AS PHIP5_Unit, QOD_PHIP4.PHIP4_Batch, ([PHIP4_Batch]/[QFP_RecPHIP]![PHIP_LR])*[QFP_RecPHIP]![IngPer] AS PHIP5_Batch
FROM QOD_PHIP4 LEFT JOIN QFP_RecPHIP ON QOD_PHIP4.PHIPPK4 = QFP_RecPHIP.TDPK
WHERE (((QOD_PHIP4.PHIP4_Type) Like "IP" Or (QOD_PHIP4.PHIP4_Type)="PH"));

Qry8 (Union query) when i use this one message of Error 3048 appear
SELECT QOD_FP.ODID, QOD_FP.ODPK, QOD_FP.PDate, QOD_FP.Kitchen, QOD_FP.Job_Order, QOD_FP.TDPK, QFP_RecPHIP.PHIPPK AS PHIPPK1, QFP_RecPHIP.RPFPType AS PHIP1_Type, QFP_RecPHIP.RawCode AS RawCode1, QFP_RecPHIP.ERP_Code AS ERP_Code1, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng1, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb1, QFP_RecPHIP.Unit AS PHIP1_Unit, [QFP_RecPHIP]![PQty]*[QOD_FP]![OQty] AS PHIP1_Batch
FROM QOD_FP LEFT JOIN QFP_RecPHIP ON QOD_FP.PHIPPK = QFP_RecPHIP.TDPK
WHERE (((QFP_RecPHIP.RPFPType) Not Like "Raw") AND ((QFP_RecPHIP.Unit) Not Like "Pcs"));

UNION SELECT QOD_PHIP1Filling.ODID, QOD_PHIP1Filling.ODPK, QOD_PHIP1Filling.PDate, QOD_PHIP1Filling.Kitchen, QOD_PHIP1Filling.Job_Order, QOD_PHIP1Filling.TDPK, QFP_RecPHIP.PHIPPK AS PHIPPK2, QFP_RecPHIP.RPFPType AS PHIP2_Type, QFP_RecPHIP.RawCode AS RawCode2, QFP_RecPHIP.ERP_Code AS ERP_Code2, QFP_RecPHIP.QFP_RecipeDtl.QRP_RPFP.Item_Eng AS RPFP_Eng2, QFP_RecPHIP.Brand, QFP_RecPHIP.Item_Arb AS RPFP_Arb2, QFP_RecPHIP.Unit AS PHIP_Unit2, [QOD_PHIP1Filling]![PHIP1_Batch]*[QFP_RecPHIP]![PQty] AS PHIP2_Batch
FROM QOD_PHIP1Filling LEFT JOIN QFP_RecPHIP ON QOD_PHIP1Filling.PHIPPK1 = QFP_RecPHIP.TDPK
WHERE (((QOD_PHIP1Filling.PHIP1_Unit)="Pcs"));

Thanks
Click to expand...
Would it be possible to upload a zipped copy of the database with no confidential data?
 
You must log in or register to reply here.

Similar threads

V
Form don't load on laptop
Replies
11
Views
305
The_Doc_Man
The_Doc_Man
T
Solved Can't find System DSN after upgrading to Office 365
Replies
18
Views
375
sonic8
S
S
Database intermittently does not open on mouse click
2
Replies
35
Views
671
Edgar_
E
E
The record source " qu_sample" specified on this form does not exist.
Replies
6
Views
280
Ecc_9:10
E
S
Solved I used to be able to copy objects from one database to another but now I get error code 3734
2
Replies
31
Views
1,834
spaLOGICng
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom