I need to create a Sales History extract that gets information from 4 tables.

I need information from these tables.

InvoiceLine

SalesOrderLinkedTxn

ItemInventory

Customer

The SQL query that I have that works will link 3 tables. I need over 30 fields of data but the query only shows a few fields

SELECT

     InvoiceLine."TxnDate", SalesOrderLinkedTxn."TxnDate",     

     InvoiceLine."RefNumber", SalesOrderLinkedTxn."RefNumber",

     Customer."CompanyName", Customer."CustomerTypeRefFullName"

FROM

    ( InvoiceLine LEFT OUTER JOIN SalesOrderLinkedTxn ON        

      SalesOrderLinkedTxn."LinkedTxnTxnID" = InvoiceLine."TxnID") INNER JOIN Customer ON 

      InvoiceLine."CustomerRefListID" = Customer."ListID"

I need to also link the ItemInventory table

InvoiceLine JOIN ItemInventory ON InvoiceLine."InvoiceLineItemRefListID" = ItemInventory."ListID"

This is where I am having difficulty in constructing the SQL statement.

Can you put me on the correct path?

Tom,

I need the following data items from the related tables, so using the sp_report SalesByCustomerDetail would not give me the right info.

This Query gives me the correct results but it takes over 1 1/2 hours to run. Is there any thing that you see that can speed things up.

SELECT
    InvoiceLine."TxnDate", InvoiceLine."RefNumber", InvoiceLine."ShipAddressAddr1", InvoiceLine."ShipAddressCity", InvoiceLine."ShipAddressState", InvoiceLine."ShipAddressPostalCode", InvoiceLine."PONumber", InvoiceLine."SalesRepRefFullName", InvoiceLine."ShipDate", InvoiceLine."InvoiceLineItemRefFullName", InvoiceLine."InvoiceLineDesc", InvoiceLine."InvoiceLineQuantity", InvoiceLine."InvoiceLineRate", InvoiceLine."InvoiceLineInvoiced",
    Customer."FullName", Customer."BillAddressCity", Customer."BillAddressState", Customer."Phone", Customer."Contact", Customer."CustomerTypeRefFullName",
    ItemInventory."AverageCost", ItemInventory."CustomFieldAffilLicensingGroup", ItemInventory."CustomFieldBarCode", ItemInventory."CustomFieldCategory", ItemInventory."CustomFieldSize", ItemInventory."CustomFieldState",
    SalesOrderLinkedTxn."TxnDate", SalesOrderLinkedTxn."RefNumber"

FROM
  (( InvoiceLine LEFT OUTER JOIN ItemInventory ON       
    InvoiceLine."InvoiceLineItemRefListID"= ItemInventory."ListID") INNER JOIN Customer ON 
    InvoiceLine."CustomerRefListID" = Customer."ListID" ) LEFT OUTER JOIN SalesOrderLinkedTxn ON
    InvoiceLine."TxnID" = SalesOrderLinkedTxn."LinkedTxnTxnID"

Mike W

This will run much faster, but isn't the same thing:

SELECT
    InvoiceLine."TxnDate", InvoiceLine."RefNumber", InvoiceLine."ShipAddressAddr1", InvoiceLine."ShipAddressCity", InvoiceLine."ShipAddressState", InvoiceLine."ShipAddressPostalCode", InvoiceLine."PONumber", InvoiceLine."SalesRepRefFullName", InvoiceLine."ShipDate", InvoiceLine."InvoiceLineItemRefFullName", InvoiceLine."InvoiceLineDesc", InvoiceLine."InvoiceLineQuantity", InvoiceLine."InvoiceLineRate", InvoiceLine."InvoiceLineInvoiced",
    Customer."FullName", Customer."BillAddressCity", Customer."BillAddressState", Customer."Phone", Customer."Contact", Customer."CustomerTypeRefFullName",
    ItemInventory."AverageCost", ItemInventory."CustomFieldAffilLicensingGroup", ItemInventory."CustomFieldBarCode", ItemInventory."CustomFieldCategory", ItemInventory."CustomFieldSize", ItemInventory."CustomFieldState",
    SalesOrderLinkedTxn."TxnDate", SalesOrderLinkedTxn."RefNumber"
FROM SalesOrderLinkedTxn, InvoiceLine, ItemInventory, Customer
WHERE
    SalesOrderLinkedTxn."LinkedTxnTxnID" = InvoiceLine."TxnID"
and InvoiceLine."InvoiceLineItemRefListID"= ItemInventory."ListID"
and InvoiceLine."CustomerRefListID" = Customer."ListID"

Tom,

The only problem with this query is that we have just started using QB for sales orders in 2008. We have plenty of invoices from prior years. Your example will only show the invoice line if there is a sales order. I need to have all invoices regardless of whether there is a sales order for that invoice.

Any ideas on how to rewrite the FROM section ?

FROM
  (( InvoiceLine LEFT OUTER JOIN ItemInventory ON       
    InvoiceLine."InvoiceLineItemRefListID"= ItemInventory."ListID") INNER JOIN Customer ON 
    InvoiceLine."CustomerRefListID" = Customer."ListID" ) LEFT OUTER JOIN SalesOrderLinkedTxn ON
    InvoiceLine."TxnID" = SalesOrderLinkedTxn."LinkedTxnTxnID"

Ok, after a little work getting the join syntax right this will do what you want:

SELECT
    InvoiceLine."TxnDate", InvoiceLine."RefNumber", InvoiceLine."ShipAddressAddr1", InvoiceLine."ShipAddressCity", InvoiceLine."ShipAddressState", InvoiceLine."ShipAddressPostalCode", InvoiceLine."PONumber", InvoiceLine."SalesRepRefFullName", InvoiceLine."ShipDate", InvoiceLine."InvoiceLineItemRefFullName", InvoiceLine."InvoiceLineDesc", InvoiceLine."InvoiceLineQuantity", InvoiceLine."InvoiceLineRate", InvoiceLine."InvoiceLineInvoiced",
    Customer."FullName", Customer."BillAddressCity", Customer."BillAddressState", Customer."Phone", Customer."Contact", Customer."CustomerTypeRefFullName",
    ItemInventory."AverageCost", ItemInventory."CustomFieldAffilLicensingGroup", ItemInventory."CustomFieldBarCode", ItemInventory."CustomFieldCategory", ItemInventory."CustomFieldSize", ItemInventory."CustomFieldState",
    SalesOrderLinkedTxn."TxnDate", SalesOrderLinkedTxn."RefNumber"
FROM   (("InvoiceLine" LEFT OUTER JOIN "Customer" ON "InvoiceLine"."CustomerRefListID"="Customer"."ListID")
LEFT OUTER JOIN "ItemInventory" ON "InvoiceLine"."InvoiceLineItemRefListID"="ItemInventory"."ListID")
LEFT OUTER JOIN "SalesOrderLinkedTxn" ON "InvoiceLine"."TxnID"="SalesOrderLinkedTxn"."LinkedTxnTxnID"