QuickBooks PriceLevelPerItem Part 1: Import into Microsoft Access Table Using QODBC

Tutorial Overview
  1. Part 1
    1. Introduction
    2. What You Need
    3. What is a Per Item Price Level Table?
    4. Reasons to Import Price Level Per Item Prices
    5. What the Functon Does
    6. Location of the Price Level Per Item Table in QuickBooks
  2. Part 2
    1. The Relationship Between Items, Prices and Jobs
    2. Altering the Function
    3. Reasons for Importing the PriceLevelPerItem Table
    4. Real World Applications for the Imported PriceLevelPerItem Table
    5. Copy & Paste Function
  3. Part 3
    1. Opening the Table
    2. How to Import a Single Price Level
    3. Summary


Introduction

This tutorial provides copy and paste code that imports the Price Level Per Item (or Per Item Price Level) table from QuickBooks using QODBC.

QODBC lets users read and write QuickBooks data from Microsoft Access using VBA (Visual Basic for Applications). QODBC stands for QuickBooks Open Database Connection.

This driver provides more data than that available in QuickBooks. For an idea of the fields available to you when using QODBC, see:

            Import QuickBooks Tables: IIF vs. QODBC

            www.vbquick.com/2011/10/import-quickbooks-table-iif-or-qodbc.html




What You Need
  1. QuickBooks
  2. Microsoft Access
  3. QODBC Read Version, Read-Write Version or Free Trial Version
  4. At least one Per Item Price Level in QuickBooks
To create a Per Item Price Level, select Per Item from the Price Level window. You may do this either through editing an existing Price Level or when creating a new one.





What is a Price Level Per Item Table?

QuickBooks provides access to multiple Price Levels. Contractors generally use this function for contracts.

Contractors who bid work generally bid different prices for each unit depending on the job. Awarded jobs are then added to QuickBooks and the prices entered.

For example, a contractor may have a unit named N VP1.01 that is installed on most jobs. However, prices for this item may vary from job to job due to location, difficulty and other factors. Therefore, the unit price for N VP1.01 changes for each job.




Reasons to Import Price Level Per Item Prices

Reasons vary. Some ideas:
  1. If you can use Microsoft Access, you can create queries. From there, the possibilities are many. You may apply different price levels to jobs already completed to see how different price levels would have affected profit. To do this:
    1. import the invoice lines from the job (invoiceline table)
    2. import the desired Per Item Price Levels (pricelevelperitem table)
    3. join the item field from the invoice lines (invoicelineitemreflistid) to the item field from the price level (pricelevelperitemitemreflistid)
  2. You may create a Customized Invoice Entry Program  in Microsoft Access. See this post for more information on Using QODBC to Create a Customized Data Entry Interface for QuickBooks Invoices:
    1. www.vbquick.com/2011/10/qodbc-quickbooks-invoice-access.html
  3. You may create queries and reports comparing price levels against each other. You may use the Microsoft Access Conditional Formatting function to highlight the highest and lowest prices on each item line.
You may think of additional reasons.



What this Function Does

This function:
  1. Uses QODBC to read the QuickBooks Price Level Per Item records
  2. Imports the records with a Pass-Through query
  3. Takes the records from the Pass-Through query and puts them into a Microsoft Access table



Location of the Price Level Per Item Table in QuickBooks

QuickBooks uses prices from the price level assigned to the customer/job when creating invoices. Users may assign a price level to a job from the Edit Customer window:



Or from the Edit Job window:




Or from each item line on an invoice:


The next post will cover:
  1. The Relationship Between Items, Prices and Jobs
  2. Altering the Function
  3. Reasons for Importing the PriceLevelPerItem Table
  4. Real World Applications for the Imported PriceLevelPerItem Table
  5. Copy & Paste Function
Continue to next post...

Comments

Popular Posts