[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 


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 the table InventoryAdjustmentLine. This article will show you how to create an Inventory Adjustment record using QODBC.

First, please create a new MS Access database & link InventoryAdjustment, and InventoryAdjustmentLine tables.
Please refer to the below-mentioned article for How to Use QODBC with Microsoft Access:

How to Use QODBC with Microsoft Access

After linking the table, we need to execute the query in the MS Access query editor:
MS Access>> create tab >> Query Design >> It should pop up for choosing a table. Please click the "Close" button & click on "SQL" from the left top corner below the 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 (AccountRefListIDInventoryAdjustmentLineItemRefListIDInventoryAdjustmentLineQuantityAdjustmentQuantityDifference) VALUES ('1E0000-933270542''160000-933272656',-100)

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

Here is the result in MS Access.


And this is the result in QuickBooks:


Use Field InventoryAdjustmentLineQuantityAdjustmentNewQuantity

We can also use the 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:


Here is the result in QuickBooks:


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

Please refer below query, which includes "InventorySiteRefListID":

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

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


(0 vote(s))
Not helpful

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