Hi ,

I want to make a report in MS-Access. For that i want to relate InvoiceLine, BillItemLine, BillExpenseLine tables to find out  InvoiceLine table's corresponding entry in BillItemLine/BillExpenseLine  table.I can't find any helpful information on BillLinkedTxn, InvoiceLinkedTxn tables. Moreover BillItemLine.ItemLineLinkToTxnTxnID, BillItemLine.ItemLineLinkToTxnTxnLineID & InvoiceLine.InvoiceLineLinkToTxnTxnID & InvoiceLine.InvoiceLineLinkToTxnTxnLineID entries appears to be blank in my database.

thanks in advance

Siju V. Eugin

Hi Tom,

 Thanks for your reply. Our requirement is to list Expense and Income of items belonging to a particular job. For the above we use BillExpenseLine table & BillItemLine table to get all expenses and InvoiceLine table for income. We could not make one to one mapping between  InvoiceLine and BillExpenseLine/BillItemLine records after querying InvoiceLine , BillExpenseLine/BillItemLine tables with CustomerReferenceListID.

thanks & regards

Siju V Eugin

Ok, you can start by looking at this:

SELECT BillItemLine.TxnID, BillItemLine.RefNumber, BillItemLine.ItemLineSeqNo,
InvoiceLine.TxnID, InvoiceLine.RefNumber, InvoiceLine.InvoiceLineSeqNo,
BillItemLine.ItemLineDesc, BillItemLine.ItemLineCost,
BillItemLine.ItemLineAmount, InvoiceLine.InvoiceLineDesc,
InvoiceLine.InvoiceLineRate, InvoiceLine.InvoiceLineAmount
from BillItemLine, InvoiceLine
Where BillItemLine.ItemLineCustomerRefFullName = InvoiceLine.CustomerRefFullName
and BillItemLine.ItemLineItemRefFullName = InvoiceLine.InvoiceLineItemRefFullName

Hi Tom,

Thanks for your reply. Actually your query will not result one to one mapping between Invoice Line & BillItemLine/BillExpenseLine rerecords. I will explain the scenario more detail.

The following queries

(1)SELECT * FROM InvoiceLine WHERE InvoiceLineItemRefFullName = 'Delivery' AND InvoiceLine.CustomerRefListID = '8460000-1150760083'

(2)SELECT * FROM BillExpenseLine WHERE ExpenseLineCustomerRefListID = '8460000-1150760083' 

queries (1) & (2) will result 7 records each. Is their any possibility for one to one mapping between these record sets to make a result of 7 records. According to the query you had provided we will get a result of 49 rerecords. If their is no possibility will you please mention how to customize QuickBooks application to get such a result.

thanks & regards

Siju V. Eugin

Sometimes I wish people would read, there is no direct relation between BillExpenseLine or BillItemLine and InvoiceLine unless the item is purchased as part of a job, if that's the case we normally use one of the job detail reports instead.

Try looking at which QuickBooks report suits your requirements:

and let me know. I will tell you the SQL to generate that report.