Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need Help with SQL Statement

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

916 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