Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need Help with SQL Statement

Posted on 2013-01-24
5
Medium Priority
?
423 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

581 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