Does QODBC support the joining of more than two tables?

Example:

select cust.FullName
from customer cust
left outer join invoice inv
  on inv.CustomerRefListID = cust.ListId
left outer join invoice inv2
  on inv2.CustomerRefListID = cust.ListId

Most of the customer details are already within the invoice. So most of the time you don't need to link tables in QODBC as most of the header details are always embedded in the transactions. The headers in QODBC act as placeholders. But if you wanted to see, say the JobEndDate, you can link the tables like this:

SELECT InvoiceLine.CustomerRefListID, InvoiceLine.CustomerRefFullName,
InvoiceLine.TxnDate, InvoiceLine.InvoiceLineDesc,
InvoiceLine.InvoiceLineQuantity, Customer.Balance, Customer.JobEndDate
FROM Customer Customer, InvoiceLine InvoiceLine
WHERE InvoiceLine.CustomerRefListID = Customer.ListID

using MS Query it's easy to visually link tables like this:

Tom, I am sorry, but I didn't mean for you to take my example literally and help me link customer data to invoice data.  I included it as an example of SQL that fails to execute within QODBC to illustrate the joining of more than two tables.  The query I included is valid SQL, but fails to run with QODBC.  The tables I reference could be substituted with any three valid tables that have common keys.  If QODBC will not join more than two tables, this will make my job much more difficult.  Data will have to be fetched with two or three queries and then merged within the application.

Thank you.

You can link more than two tables in QODBC as long as it makes sense. For example:

SELECT InvoiceLine.CustomerRefFullName, InvoiceLine.TxnDate,
InvoiceLine.InvoiceLineDesc, InvoiceLine.InvoiceLineQuantity,
Customer.Balance, Customer.JobEndDate, Item.Type
FROM Customer Customer, InvoiceLine InvoiceLine, Item Item
WHERE InvoiceLine.CustomerRefListID = Customer.ListID
AND InvoiceLine.InvoiceLineItemRefListID = Item.ListID

Note: I've used ListID joins as these are QODBC jump-ins (which act like indexes).

Tom,

When I try using

SELECT InvoiceLine.CustomerRefFullName, InvoiceLine.TxnDate, InvoiceLine.InvoiceLineDesc, InvoiceLine.InvoiceLineQuantity, FROM InvoiceLine InvoiceLine, Item Item WHERE InvoiceLine.CustomerRefFullName LIKE '%50-226%'

everything works fine. As soon as I add 1 item from the customer table the query never finishes and I get an error of  "not responding"

Is there something that I am doing wrong?

Well let's say you haven't shown the statement that didn't work, so who knows? The first thing to understand is that a LIKE will do a table scan, so the bigger your company file, the longer it will take, however this will work:

SELECT InvoiceLine.CustomerRefFullName, InvoiceLine.TxnDate,
InvoiceLine.InvoiceLineDesc, InvoiceLine.InvoiceLineQuantity,
Customer.Balance, Customer.JobEndDate, Item.Type
FROM Customer Customer, InvoiceLine InvoiceLine, Item Item
WHERE Customer.FullName LIKE '%Ecker%'
AND InvoiceLine.CustomerRefListID = Customer.ListID
AND InvoiceLine.InvoiceLineItemRefListID = Item.ListID

but it's MUCH faster to define who you want like this:

SELECT InvoiceLine.CustomerRefFullName, InvoiceLine.TxnDate,
InvoiceLine.InvoiceLineDesc, InvoiceLine.InvoiceLineQuantity,
Customer.Balance, Customer.JobEndDate, Item.Type
FROM Customer Customer, InvoiceLine InvoiceLine, Item Item
WHERE Customer.FullName = 'Ecker Designs:Office Repairs'
AND InvoiceLine.CustomerRefListID = Customer.ListID
AND InvoiceLine.InvoiceLineItemRefListID = Item.ListID

Tom,

Sorry about not including the query that did not run. I thought that I pasted it in the message and then hit submit before I knew it.

This is the query that did not run:

SELECT "InvoiceLine"."CustomerRefFullName", "InvoiceLine"."InvoiceLineDesc", "InvoiceLine"."InvoiceLineQuantity", "InvoiceLine"."InvoiceLineRate", "Customer"."Balance"

 FROM   "InvoiceLine" "InvoiceLine", "Customer" "Customer"

WHERE  "InvoiceLine"."CustomerRefListID" = "Customer"."ListID" AND

"InvoiceLine"."CustomerRefFullName" LIKE '%50-226%'

When I get to the office in the morning I will try your suggestions.....

Thanks for taking the time to answer.

Tom,

I tried this first and it worked fine:

SELECT "Customer"."FullName" FROM   "Customer" "Customer"
WHERE "Customer"."FullName"='Customer ABC'

Then I added an item from the invoice line table as show below and one time it took 2 hours to run and the 2nd time I had to abort the run.

SELECT "Customer"."FullName",  "InvoiceLine"."InvoiceLineItemRefFullName"
FROM   "InvoiceLine" "InvoiceLine", "Customer" "Customer"        <<<< Tables are the wrong way round - Tom
WHERE "Customer"."FullName"=' Customer ABC' 
AND  ("InvoiceLine"."CustomerRefListID"="Customer"."ListID")

I am running this query in Crystal Report. I must still be doing something wrong. I also tried with the UNOPTIMIZED option.vThe first query returned 725 records which should be all the items purchased for this customer. The database has 536 customers with 7586 invoices.

Thanks for the help....Just not sure what to try next

The problem lies in the order of the tables in your FROM clause. The first table should always be the table of the main filter of the query. This works much faster:

SELECT "Customer"."FullName",  "InvoiceLine"."InvoiceLineItemRefFullName"
FROM   "Customer" "Customer", "InvoiceLine" "InvoiceLine"
WHERE "Customer"."FullName"='Customer ABC'
AND  ("InvoiceLine"."CustomerRefListID"="Customer"."ListID")

Thanks, that made the query go a lot faster.

If I want to check the actual cost of an item and match it up with the amount that we sell it for on the invoice, would I use the PurchaseOrderLine table or is there a better way to do this?

This does not seem to work:

SELECT "Customer"."FullName",  "InvoiceLine"."InvoiceLineItemRefFullName", "InvoiceLine"."InvoiceLineQuantity", "InvoiceLine"."InvoiceLineDesc", "InvoiceLine"."InvoiceLineRate", "InvoiceLine"."IsPaid", "InvoiceLine"."InvoiceLineAmount", "InvoiceLine"."InvoiceLineItemRefFullName", "InvoiceLine"."InvoiceLineClassRefFullName", "PurchaseOrderLine"."PurchaseOrderLineAmount"
FROM   "Customer" "Customer","InvoiceLine" "InvoiceLine", "PurchaseOrderLine" "PurchaseOrderLine"
WHERE "Customer"."FullName"='Customer ABC'
AND  ("InvoiceLine"."CustomerRefListID"="Customer"."ListID")
AND  ("InvoiceLine"."InvoiceLineItemRefListID" = "PurchaseOrderLine"."PurchaseOrderLineItemRefListID")

As you can tell, I am pretty new at SQL and Crystal Report so I do appreciate all the help you have given me.

Are there any sites that you know of where I can learn more about SQL and Crystal?

Thanks

This will show the Purchase Cost and Aveverage Cost of Stock Items invoiced for your Customer: 'Customer ABC'

SELECT "Customer"."FullName",  "InvoiceLine"."InvoiceLineItemRefFullName",
"InvoiceLine"."InvoiceLineQuantity", "InvoiceLine"."InvoiceLineDesc",
"InvoiceLine"."IsPaid", "InvoiceLine"."InvoiceLineAmount", "InvoiceLine"."InvoiceLineItemRefFullName", "InvoiceLine"."InvoiceLineClassRefFullName","InvoiceLine"."InvoiceLineRate", "ItemInventory"."PurchaseCost", "ItemInventory"."AverageCost"
FROM   "Customer" "Customer","InvoiceLine" "InvoiceLine", "ItemInventory" "ItemInventory"
WHERE "Customer"."FullName"='Customer ABC'
AND  ("InvoiceLine"."CustomerRefListID"="Customer"."ListID")
AND  ("InvoiceLine"."InvoiceLineItemRefListID" = "ItemInventory"."ListID")

Tom,

Thanks for the help you have given me so far. I would like to ask you a few more questions about the correct tables to use when trying to create a report. I need to provide a weekly project status report to several of my customers that show the following:
 
Billed Material -     $xxxxxx
Unbilled Material - $xxxxx
Material on Order - $xxxxx
Total Material Cost- $xxxxxxx   (sum of the 3 lines above)

Billed Labor -     $xxxxx 
Unbilled Labor - $xxxxxx 
Total Labor Cost - $xxxxxx                                (sum of the 2 lines above)

Shipping & other charges - $xxxxxxx

Right now I have to wait on 1 person who runs the following reports in QB to get all of the data and then enters the final numbers in Excel.:

- Open Purchase Order By Job – material on order
- Job Profitability Detail – actual cost for billed material, difference of actual revenue and actual cost to get unbilled material, billed labor, shipping charges
- Time by Job Detail – to get the number of unbilled hours

I would like to automate this process so that other employees can get the data using Crystal Reports or Excel. They may not have QB loaded on their computer.

I am trying to get all of the data I need by using the following tables:

InvoiceLine
PurchaseOrderLine
TimeTracking 

Using Crystal Report I cannot link these table so I am having to use subreports for both the PO information and the Employee Time. By using a subreport for Employee Time I can get the number of hours worked but not the bill rate for each employee so I cannot calculate the $$ for unbilled time. I thought that I could relate the unbilled rates to the existing rates that are used in the PurchaseOrderLine table. I tried your suggestion of using ItemIentory but 95% of our parts are purchased when we need them for a project so I would need to use both ItemInventory and ItemNonIventory.

I do not know how to bring multiple tables into Excel using MS Query. Excel would be the better choice since all employees who need to run the report have Excel on their computer.

I think that maybe I am going in the wrong direction and providing you have the time would like your suggestions on the best way to get the data I need for the report.

Are their other tables that I should be using? Is there a special report that can give me the data?