Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

Buy Support
Incidents

If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:

Click Here
If you can't login and post questions or you are having trouble viewing forum posts:
Click Here
Callback
Support

If you live in USA, UK, Canada, Australia or New Zealand, you can leave us details on your question and request us to call you back and discuss them with you personally  (charges apply).

Click Here
 
Buy Support
Incidents
If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:
Click Here

Forum : Joining a report with a select querySearch Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC v9 Forum

 New Topic 
 
 Post Reply 
[1]  
 Joining a report with a select query 
 Author   Message 
  Nolan 
  
 Group: Members 
 Posts: 2 
 Joined: 2008-12-25 
 Profile
 Posted : 2008-12-25 08:05:54

I am trying to create a query that will retrieve the Average Cost field from the inventory valuation summary and combine it with price/cost data from the ItemInventory and ItemService tables.  I can perform these 2 queries separately into 2 separate excel sheets, then use excel functions to combine them, but I would love to have a dedicated query to do this.

The 2 queries I'm using are:

Valuation summary - sp_report InventoryValuationSummary show  Blank, AverageCost  (blank is where the part number goes in this report)

Item Queries - SELECT ItemInventory.Name AS PartNum, ItemInventory.SalesDesc, ItemInventory.SalesPrice AS Price, ItemInventory.PurchaseCost AS Cost, ItemInventory.SalesTaxCodeRefFullName AS Taxable
FROM ItemInventory UNION SELECT ItemService.Name AS PartNum, ItemService.SalesOrPurchaseDesc AS SalesDesc, ItemService.SalesOrPurchasePrice AS Price, ItemService.SalesAndPurchasePurchaseCost AS Cost, ItemService.SalesTaxCodeRefFullName AS Taxable
FROM ItemService

I tried the following:

SELECT (ItemInventory.Name AS PartNum, ItemInventory.SalesDesc, ItemInventory.SalesPrice AS Price, ItemInventory.PurchaseCost AS Cost, ItemInventory.SalesTaxCodeRefFullName AS Taxable
FROM ItemInventory UNION SELECT ItemService.Name AS PartNum, ItemService.SalesOrPurchaseDesc AS SalesDesc, ItemService.SalesOrPurchasePrice AS Price, ItemService.SalesAndPurchasePurchaseCost AS Cost, ItemService.SalesTaxCodeRefFullName AS Taxable
FROM ItemService) AS t1 INNER JOIN sp_report InventoryValuationSummary show  Blank, AverageCost ON t1.PartNum=InventoryValuationSummary.Blank

but got a lexical error with the ( character.

Is it possible to join these into a single query? 

 

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-12-26 10:45:14
sp_report stored procedures can only be run as standalone queries, they can't be combined within any statement. 

  Top 
  Nolan 
  
 Group: Members 
 Posts: 2 
 Joined: 2008-12-25 
 Profile
 Posted : 2008-12-27 01:04:28
Thanks for the reply Tom. Is it possible to reconstruct the Average Cost using the information in tables, since I can't use the information out of the report? I know that the ItemInventory table contains a column for this, but it is blank when I open it in Excel or Access. 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-12-27 10:34:00

Try using the The start of every new connection (with "Load Updated Data" first) and the Dirty Reads option and confirm that the Multi-Table Sync option has been disabled. If not, uncheck the Multi-Table Sync option as this is often defaulted on (checked) with QuickBooks Enterprise and the QODBC Enterprise Desktop Edition.

See: How do I setup the QODBC Optimizer? Where are the Optimizer options? and  How can I see the cost of a stock item and compare it to what I invoiced it for?  for more.

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to