Solved

Need Help with SQL Statement

Posted on 2013-01-24
5
392 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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

830 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