|
Outer Joins
I currntly have a microsoft query which consists of 3 tables. 'STKDETAIL'
'SNTRACE' & 'VIEW_STOCKED_PARTS' with the following joints: 1. 'STKDETAIL' to 'SNTRACE' on field Uniqbatch 2. 'STKDETAIL' to 'VIEW_STOCKED_PARTS' on field part no. I have tried to change joint 1 to an outer joint so that ALL values from 'STKDETAIL' are shown and records from 'SNTRACE' if appliacable, but it says the you can't have out joins if there are more than two tables in the query. How do i get around it. I have enter the full SQL below if it is easier to change that. SELECT SNTRACE.Serial_Number, SNTRACE.Batch_Uniq, STKDETAIL.Unique_Transaction_R, STKDETAIL.Date_Booked, Sum(STKDETAIL.Add_to_Stock) AS 'Sum of Add_to_Stock', STKDETAIL.Add_to_Stock, STKDETAIL.Part_Number, STKDETAIL.DrawingIssue, STKDETAIL.Location, STKDETAIL.Stock_Qty, STKDETAIL.Ex_Job_No, STKDETAIL.UniqBatchLocatioo, VIEW_STOCKED_PARTS.Part_Description, STKDETAIL.Trans_Memo_Field FROM FMData.dbo.SNTRACE SNTRACE, FMData.dbo.STKDETAIL STKDETAIL, FMData.dbo.VIEW_STOCKED_PARTS VIEW_STOCKED_PARTS WHERE STKDETAIL.Part_Number = VIEW_STOCKED_PARTS.Part_Number AND STKDETAIL.UniqBatchLocatioo = SNTRACE.Batch_Uniq GROUP BY SNTRACE.Serial_Number, SNTRACE.Batch_Uniq, STKDETAIL.Unique_Transaction_R, STKDETAIL.Date_Booked, STKDETAIL.Add_to_Stock, STKDETAIL.Part_Number, STKDETAIL.DrawingIssue, STKDETAIL.Location, STKDETAIL.Stock_Qty, STKDETAIL.Ex_Job_No, STKDETAIL.UniqBatchLocatioo, VIEW_STOCKED_PARTS.Part_Description, STKDETAIL.Trans_Memo_Field HAVING (STKDETAIL.Location Like 'BONDED ST') AND (Sum(STKDETAIL.Add_to_Stock)=1) |
Outer Joins
This isn't an Excel question. Post to the appropriate newsgroup.
Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "The Rook" wrote: I currntly have a microsoft query which consists of 3 tables. 'STKDETAIL' 'SNTRACE' & 'VIEW_STOCKED_PARTS' with the following joints: 1. 'STKDETAIL' to 'SNTRACE' on field Uniqbatch 2. 'STKDETAIL' to 'VIEW_STOCKED_PARTS' on field part no. I have tried to change joint 1 to an outer joint so that ALL values from 'STKDETAIL' are shown and records from 'SNTRACE' if appliacable, but it says the you can't have out joins if there are more than two tables in the query. How do i get around it. I have enter the full SQL below if it is easier to change that. SELECT SNTRACE.Serial_Number, SNTRACE.Batch_Uniq, STKDETAIL.Unique_Transaction_R, STKDETAIL.Date_Booked, Sum(STKDETAIL.Add_to_Stock) AS 'Sum of Add_to_Stock', STKDETAIL.Add_to_Stock, STKDETAIL.Part_Number, STKDETAIL.DrawingIssue, STKDETAIL.Location, STKDETAIL.Stock_Qty, STKDETAIL.Ex_Job_No, STKDETAIL.UniqBatchLocatioo, VIEW_STOCKED_PARTS.Part_Description, STKDETAIL.Trans_Memo_Field FROM FMData.dbo.SNTRACE SNTRACE, FMData.dbo.STKDETAIL STKDETAIL, FMData.dbo.VIEW_STOCKED_PARTS VIEW_STOCKED_PARTS WHERE STKDETAIL.Part_Number = VIEW_STOCKED_PARTS.Part_Number AND STKDETAIL.UniqBatchLocatioo = SNTRACE.Batch_Uniq GROUP BY SNTRACE.Serial_Number, SNTRACE.Batch_Uniq, STKDETAIL.Unique_Transaction_R, STKDETAIL.Date_Booked, STKDETAIL.Add_to_Stock, STKDETAIL.Part_Number, STKDETAIL.DrawingIssue, STKDETAIL.Location, STKDETAIL.Stock_Qty, STKDETAIL.Ex_Job_No, STKDETAIL.UniqBatchLocatioo, VIEW_STOCKED_PARTS.Part_Description, STKDETAIL.Trans_Memo_Field HAVING (STKDETAIL.Location Like 'BONDED ST') AND (Sum(STKDETAIL.Add_to_Stock)=1) |
Outer Joins
I am using excel with database link
"Dave F" wrote: This isn't an Excel question. Post to the appropriate newsgroup. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "The Rook" wrote: I currntly have a microsoft query which consists of 3 tables. 'STKDETAIL' 'SNTRACE' & 'VIEW_STOCKED_PARTS' with the following joints: 1. 'STKDETAIL' to 'SNTRACE' on field Uniqbatch 2. 'STKDETAIL' to 'VIEW_STOCKED_PARTS' on field part no. I have tried to change joint 1 to an outer joint so that ALL values from 'STKDETAIL' are shown and records from 'SNTRACE' if appliacable, but it says the you can't have out joins if there are more than two tables in the query. How do i get around it. I have enter the full SQL below if it is easier to change that. SELECT SNTRACE.Serial_Number, SNTRACE.Batch_Uniq, STKDETAIL.Unique_Transaction_R, STKDETAIL.Date_Booked, Sum(STKDETAIL.Add_to_Stock) AS 'Sum of Add_to_Stock', STKDETAIL.Add_to_Stock, STKDETAIL.Part_Number, STKDETAIL.DrawingIssue, STKDETAIL.Location, STKDETAIL.Stock_Qty, STKDETAIL.Ex_Job_No, STKDETAIL.UniqBatchLocatioo, VIEW_STOCKED_PARTS.Part_Description, STKDETAIL.Trans_Memo_Field FROM FMData.dbo.SNTRACE SNTRACE, FMData.dbo.STKDETAIL STKDETAIL, FMData.dbo.VIEW_STOCKED_PARTS VIEW_STOCKED_PARTS WHERE STKDETAIL.Part_Number = VIEW_STOCKED_PARTS.Part_Number AND STKDETAIL.UniqBatchLocatioo = SNTRACE.Batch_Uniq GROUP BY SNTRACE.Serial_Number, SNTRACE.Batch_Uniq, STKDETAIL.Unique_Transaction_R, STKDETAIL.Date_Booked, STKDETAIL.Add_to_Stock, STKDETAIL.Part_Number, STKDETAIL.DrawingIssue, STKDETAIL.Location, STKDETAIL.Stock_Qty, STKDETAIL.Ex_Job_No, STKDETAIL.UniqBatchLocatioo, VIEW_STOCKED_PARTS.Part_Description, STKDETAIL.Trans_Memo_Field HAVING (STKDETAIL.Location Like 'BONDED ST') AND (Sum(STKDETAIL.Add_to_Stock)=1) |
Outer Joins
Try doing the query in MS Access, which does allow multiple outer
joins in most cases and use the Access q. On 15 Mar, 16:05, The Rook wrote: I am using excel with database link "Dave F" wrote: This isn't an Excel question. Post to the appropriate newsgroup. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "The Rook" wrote: I currntly have a microsoft query which consists of 3 tables. 'STKDETAIL' 'SNTRACE' & 'VIEW_STOCKED_PARTS' with the following joints: 1. 'STKDETAIL' to 'SNTRACE' on field Uniqbatch 2. 'STKDETAIL' to 'VIEW_STOCKED_PARTS' on field part no. I have tried to change joint 1 to an outer joint so that ALL values from 'STKDETAIL' are shown and records from 'SNTRACE' if appliacable, but it says the you can't have out joins if there are more than two tables in the query. How do i get around it. I have enter the full SQL below if it is easier to change that. SELECT SNTRACE.Serial_Number, SNTRACE.Batch_Uniq, STKDETAIL.Unique_Transaction_R, STKDETAIL.Date_Booked, Sum(STKDETAIL.Add_to_Stock) AS 'Sum of Add_to_Stock', STKDETAIL.Add_to_Stock, STKDETAIL.Part_Number, STKDETAIL.DrawingIssue, STKDETAIL.Location, STKDETAIL.Stock_Qty, STKDETAIL.Ex_Job_No, STKDETAIL.UniqBatchLocatioo, VIEW_STOCKED_PARTS.Part_Description, STKDETAIL.Trans_Memo_Field FROM FMData.dbo.SNTRACE SNTRACE, FMData.dbo.STKDETAIL STKDETAIL, FMData.dbo.VIEW_STOCKED_PARTS VIEW_STOCKED_PARTS WHERE STKDETAIL.Part_Number = VIEW_STOCKED_PARTS.Part_Number AND STKDETAIL.UniqBatchLocatioo = SNTRACE.Batch_Uniq GROUP BY SNTRACE.Serial_Number, SNTRACE.Batch_Uniq, STKDETAIL.Unique_Transaction_R, STKDETAIL.Date_Booked, STKDETAIL.Add_to_Stock, STKDETAIL.Part_Number, STKDETAIL.DrawingIssue, STKDETAIL.Location, STKDETAIL.Stock_Qty, STKDETAIL.Ex_Job_No, STKDETAIL.UniqBatchLocatioo, VIEW_STOCKED_PARTS.Part_Description, STKDETAIL.Trans_Memo_Field HAVING (STKDETAIL.Location Like 'BONDED ST') AND (Sum(STKDETAIL.Add_to_Stock)=1)- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 10:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com