Darrell
Registered User.
- Local time
- Today, 15:42
- Joined
- Feb 1, 2001
- Messages
- 299
Just for a change I have something that was working perfectly for well over a year that has now stopped working and is causing vast amounts of :banghead::banghead::banghead:
I am doing analysis of production operation times and as such have two tables - one which has production order header info and the other which has production order operation detail.
In this operation table I have two fields that record whether or not an operation for a production order has been checked and one to say whether or not it should be ignored in analysis etc.
So based on these tables I have two select queries - one which reports all production orders which have been checked and one which reports those that haven't been checked.
Using these, I have a Union query that will then give me a report by Material number showing production order operations that were checked and the new production order operations that haven't been checked.
The main reason for doing it all this way is that the operations table doesn't have the material number in it.
So this all has been working perfectly up until today where it has come up with a Data type mismatch error when trying to run the union query.
Now I could understand if the data was coming from different tables, but the material field (data type = Text) used in the join in the query is from the same table.... I have checked and there aren't any blank records or anything else that looks out of the norm.
Anyway - here is some detail on the queries etc
Query 1 - Checked Operations
Query 2 - Unchecked Operations
Union Query
Any help greatly appreciated
I am doing analysis of production operation times and as such have two tables - one which has production order header info and the other which has production order operation detail.
In this operation table I have two fields that record whether or not an operation for a production order has been checked and one to say whether or not it should be ignored in analysis etc.
So based on these tables I have two select queries - one which reports all production orders which have been checked and one which reports those that haven't been checked.
Using these, I have a Union query that will then give me a report by Material number showing production order operations that were checked and the new production order operations that haven't been checked.
The main reason for doing it all this way is that the operations table doesn't have the material number in it.
So this all has been working perfectly up until today where it has come up with a Data type mismatch error when trying to run the union query.
Now I could understand if the data was coming from different tables, but the material field (data type = Text) used in the join in the query is from the same table.... I have checked and there aren't any blank records or anything else that looks out of the norm.
Anyway - here is some detail on the queries etc
Query 1 - Checked Operations
Code:
SELECT [tbl COOIS Ops].Order, [tbl COOIS Ops].Wk_Center, [tbl COOIS Hdrs].Material, [tbl COOIS Hdrs].Material_description, [tbl COOIS Hdrs].Target_qty, [tbl COOIS Hdrs].Delivered_qty, [tbl COOIS Hdrs].Basic_start, [tbl COOIS Hdrs].Basic_finish, [tbl COOIS Hdrs].System_status, [tbl COOIS Ops].Routings_Checked, [tbl COOIS Ops].Ignore_data, [tbl COOIS Ops].Std_Labour, [Act_Labour]+[OT_Labour] AS TotalActualLabour, Val(IIf([Delivered_qty]=0,Null,[TotalActualLabour]/[Delivered_qty])) AS ActOpQty, IIf([Ignore_data]=-1,Null,[ActOpQty]) AS ActOpQtyIgnored
FROM [tbl COOIS Hdrs] INNER JOIN [tbl COOIS Ops] ON [tbl COOIS Hdrs].Order = [tbl COOIS Ops].Order
WHERE ((([tbl COOIS Ops].Order) Not Like "6*") AND (([tbl COOIS Ops].Wk_Center) Not Like "*FLSK*" And ([tbl COOIS Ops].Wk_Center) Not Like "*REWORK*") AND (([tbl COOIS Hdrs].Delivered_qty)>0) AND (([tbl COOIS Hdrs].System_status) Like "TECO*" Or ([tbl COOIS Hdrs].System_status) Like "*DLV*" Or ([tbl COOIS Hdrs].System_status) Like "*CLSD*") AND (([tbl COOIS Ops].Routings_Checked)=Yes));
Query 2 - Unchecked Operations
Code:
SELECT [tbl COOIS Hdrs].Order, [tbl COOIS Ops].Wk_Center, [tbl COOIS Hdrs].Material, [tbl COOIS Hdrs].Material_description, [tbl COOIS Hdrs].Target_qty, [tbl COOIS Hdrs].Delivered_qty, [tbl COOIS Hdrs].Basic_start, [tbl COOIS Hdrs].Basic_finish, [tbl COOIS Hdrs].System_status, [tbl COOIS Ops].Routings_Checked, [tbl COOIS Ops].Ignore_data, [tbl COOIS Ops].Std_Labour, [Act_Labour]+[OT_Labour] AS TotalActualLabour, Val(IIf([Delivered_qty]=0,Null,[TotalActualLabour]/[Delivered_qty])) AS ActOpQty, IIf([Ignore_data]=-1,Null,[ActOpQty]) AS ActOpQtyIgnored
FROM [tbl COOIS Hdrs] LEFT JOIN [tbl COOIS Ops] ON [tbl COOIS Hdrs].Order = [tbl COOIS Ops].Order
WHERE ((([tbl COOIS Hdrs].Order) Not Like "6*") AND (([tbl COOIS Ops].Wk_Center) Not Like "*FLSK*" And ([tbl COOIS Ops].Wk_Center) Not Like "*REWORK*") AND (([tbl COOIS Hdrs].Material) Not Like "2UCX*") AND (([tbl COOIS Hdrs].Material_description) Not Like "Packing Assy*") AND (([tbl COOIS Hdrs].Delivered_qty)>0) AND (([tbl COOIS Hdrs].System_status) Like "TECO*" Or ([tbl COOIS Hdrs].System_status) Like "*CLSD*") AND (([tbl COOIS Ops].Routings_Checked)=No));
Union Query
Code:
SELECT [qry Routings - To Analyse].*
FROM [qry Routings - To Analyse];
UNION SELECT [qry Routings - Analysed].*
FROM [qry Routings - Analysed] INNER JOIN [qry Routings - To Analyse] ON [qry Routings - Analysed].Material = [qry Routings - To Analyse].Material;
Any help greatly appreciated