Solved

Need Help with SQL Statement

Posted on 2013-01-24
5
387 Views
Last Modified: 2013-01-24
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.mdb' SELECT '" & var2 & "', Barcode, '" & varRM1 & "', '" & varRM2 & "', Product, [Width], [Length], RollsBag, '" & "SF" & "', #" & Now.ToShortDateString & "#, SF, RollsBag, SF, '" & "ZCP01" & "' FROM tblProduction WHERE (WorkOrd = " & CInt(var2) & ")"

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]?
0
Comment
Question by:Tim313
5 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38814527
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 WHERE (WorkOrd = " & CInt(var2) & ") AND [tblProduction].[Barcode] IN (SELECT Barcode FROM tblInventory)"

Open in new window



Ie:

The addition to the WHERE Clause should be:

AND [tblProduction].[Barcode] IN (SELECT Barcode FROM tblInventory)
0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38814544
Would an inner join be faster than the IN statement?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38814584
Jaerd_S,

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) & ")"

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38814585
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.mdb' SELECT '" & var2 & "', Barcode, '" & varRM1 & "', '" & varRM2 & "', Product, [Width], [Length], RollsBag, '" & "SF" & "', #" & Now.ToShortDateString & "#, SF, RollsBag, SF, '" & "ZCP01" & "' FROM tblProduction
INNER JOIN tblInventory ON [tblProduction].[Barcode] = [tblInventory].[Barcode]
WHERE (WorkOrd = " & CInt(var2) & ")"
0
 

Author Closing Comment

by:Tim313
ID: 38814696
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.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question