Need Help with SQL Statement
I have the following statement that works:
strSQL = "Insert into [dbo_CustomerInv] (WOReceived, Barcode, ItemNo, Product, Description1, Width1, Length1, NoRolls, UM, DateRec, QtyRec, CurrentRolls, CurrentInv, Location1) IN '\\ Computer0051\DB\CustomerDB
I need to limit the SELECT even further and tried to add the following criteria:
AND Exists (SELECT [tblProduction].[Barcode] FROM [tblProduction] WHERE [tblProduction].[Barcode] = [tblInventory].[Barcode]))
Which results in MS Access requesting the parameter for [tblInventory].[Barcode]..
How can I include the requirement that [Barcode] has to exist in both the tblProduction and tblInventory tables to allow only that data to be inserted into [dbo_CustomerInv]?
strSQL = "Insert into [dbo_CustomerInv] (WOReceived, Barcode, ItemNo, Product, Description1, Width1, Length1, NoRolls, UM, DateRec, QtyRec, CurrentRolls, CurrentInv, Location1) IN '\\ Computer0051\DB\CustomerDB
I need to limit the SELECT even further and tried to add the following criteria:
AND Exists (SELECT [tblProduction].[Barcode] FROM [tblProduction] WHERE [tblProduction].[Barcode] = [tblInventory].[Barcode]))
Which results in MS Access requesting the parameter for [tblInventory].[Barcode]..
How can I include the requirement that [Barcode] has to exist in both the tblProduction and tblInventory tables to allow only that data to be inserted into [dbo_CustomerInv]?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Would an inner join be faster than the IN statement?
Jaerd_S,
Probably. The syntax would be:
Probably. The syntax would be:
strSQL = "Insert into [dbo_CustomerInv] (WOReceived, Barcode, ItemNo, Product, Description1, Width1, Length1, NoRolls, UM, DateRec, QtyRec, CurrentRolls, CurrentInv, Location1) IN '\\ Computer0051\DB\CustomerDB.mdb' SELECT '" & var2 & "', Barcode, '" & varRM1 & "', '" & varRM2 & "', Product, [Width], [Length], RollsBag, '" & "SF" & "', #" & Now.ToShortDateString & "#, SF, RollsBag, SF, '" & "ZCP01" & "' FROM tblProduction INNER JOIN tblInventory ON tblInventory.Barcode = tblProduction.Barcode WHERE (WorkOrd = " & CInt(var2) & ")"
try this
strSQL = "Insert into [dbo_CustomerInv] (WOReceived, Barcode, ItemNo, Product, Description1, Width1, Length1, NoRolls, UM, DateRec, QtyRec, CurrentRolls, CurrentInv, Location1) IN '\\ Computer0051\DB\CustomerDB
INNER JOIN tblInventory ON [tblProduction].[Barcode] = [tblInventory].[Barcode]
WHERE (WorkOrd = " & CInt(var2) & ")"
strSQL = "Insert into [dbo_CustomerInv] (WOReceived, Barcode, ItemNo, Product, Description1, Width1, Length1, NoRolls, UM, DateRec, QtyRec, CurrentRolls, CurrentInv, Location1) IN '\\ Computer0051\DB\CustomerDB
INNER JOIN tblInventory ON [tblProduction].[Barcode] = [tblInventory].[Barcode]
WHERE (WorkOrd = " & CInt(var2) & ")"
ASKER
It may be some time before I get a chance to try the solution you proposed but I believe it will work...
In all fairness, mbizup responded first with an acceptable solution, then expanded on it providing the syntax for the "inner join" that Jared_S proposed but failed to provide. I think mbizup deserves all the points.
In all fairness, mbizup responded first with an acceptable solution, then expanded on it providing the syntax for the "inner join" that Jared_S proposed but failed to provide. I think mbizup deserves all the points.