Knowledgebase
[QODBC-Desktop] How to create an Inventory Adjustment through MS Access using QODBC
Posted by Jack - QODBC Support on 05 August 2014 12:10 PM

How to create an Inventory Adjustment through MS Access using QODBC 

Instructions

Although QuickBooks automatically adjusts your inventory quantities after every purchase and sale, sometimes you still need to adjust them yourself from time to time. QuickBooks allows QODBC to access item inventory adjustment records by modifying table InventoryAdjustmentLine. This article will show you how to create an Inventory Adjustment record using QODBC.

First of all please create new MS Access database & link InventoryAdjustment, InventoryAdjustmentLine table.
Please refer below mentioned article for How to Use QODBC with Microsoft Access:

How to Use QODBC with Microsoft Access

After linking table, we need to execute the query in MS Access query editor:
MS Access>> create tab >> Query Design >> It should popup for choosing table, please click "close" button & click on "SQL" from left top corner below File menu.

There are two fields InventoryAdjustmentLineQuantityAdjustmentQuantityDifference and InventoryAdjustmentLineQuantityAdjustmentNewQuantity in the table "InventoryAdjustmentLine". Both of them can be used to insert records.

Use Field InventoryAdjustmentLineQuantityAdjustmentQuantityDifference

Here is the sample statement:

INSERT INTO InventoryAdjustmentLine (AccountRefListID,InventoryAdjustmentLineItemRefListID,InventoryAdjustmentLineQuantityAdjustmentQuantityDifference) VALUES ('1E0000-933270542' ,'160000-933272656',-100)

The value in this field stands for the adjust quantity. For example, if you need to reduce 100 units of this inventory, value this field -100.

Here is the result in MS Access.

http://support.flexquarters.com/esupport/newimages/InventoryAdjustmentMSAccess/step1.png

http://support.flexquarters.com/esupport/newimages/InventoryAdjustmentMSAccess/Step1.png

 

And this is the result in QuickBooks: 

http://support.flexquarters.com/esupport/newimages/InventoryAdjustmentMSAccess/step3.png

 

Use Field InventoryAdjustmentLineQuantityAdjustmentNewQuantity

We can also use field InventoryAdjustmentLineQuantityAdjustmentNewQuantity to do the same job. Here is the sample code:

INSERT INTO InventoryAdjustmentLine (AccountRefListID, InventoryAdjustmentLineItemRefListID, InventoryAdjustmentLineQuantityAdjustmentNewQuantity ) VALUES ('1E0000-933270542', '160000-933272656',6600)

Value "6600" in the field stands for the quantity after adjustment, that's the only difference.

Here is the result in MS Access:

 

http://support.flexquarters.com/esupport/newimages/InventoryAdjustmentMSAccess/Step4.png

http://support.flexquarters.com/esupport/newimages/InventoryAdjustmentMSAccess/Step2.png

 

Here is the result in QuickBooks: 

http://support.flexquarters.com/esupport/newimages/InventoryAdjustmentMSAccess/Step5.png

 

You can add the field "InventorySiteRefListID" in case you have turned ON multiple inventory sites feature in QuickBooks.

Please refer below query which includes "InventorySiteRefListID":

INSERT INTO InventoryAdjustmentLine (AccountRefListID, InventorySiteRefListID,InventoryAdjustmentLineItemRefListID,InventoryAdjustmentLineQuantityAdjustmentQuantityDifference) VALUES ('1D0000-934380913', '80000001-1481767266','280000-1071524260',-100)

INSERT INTO InventoryAdjustmentLine (AccountRefListID, InventorySiteRefListID,InventoryAdjustmentLineItemRefListID, InventoryAdjustmentLineQuantityAdjustmentNewQuantity ) VALUES ('1D0000-934380913', '80000001-1481767266','280000-1071524260',6600)

 

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).