Link SO_SalesOrderHeader, this has the customer number in it.

Cheers Tyler, I knew it was something simple!

Happy to help!

my query is repeating rows for sales orders and customer numbers even without data in it. How can I make the query have only one row per customer (and SO unless there are multiple SOs for that customer)?

If you have both SO_SalesOrderHeader and SO_SalesOrderDetail, you need to link them with an INNER JOIN on SalesOrderNo.

Since SO_SalesOrderDetail contains the line data, you will have the customer number repeated for every line on the sales order.

I'm not sure what your end goal is but to only have one row per order and customer, you would need to remove the SO_SalesOrderDetail table from the query.

can you refer me to some material on how to create an INNER JOIN. Ultimately I am trying to do some customer ordering analytics...avg sale, frequency, geography, etc....

If you are unfamiliar with writing SQL statements manually then one of the easiest way to build a SQL statement is to let software like Crystal Reports build it for you after adding the tables, linking the tables,entering record selections, and adding fields to the report.

Add tables.

Define links.

Add record selections.

Add fields to report.

Refresh report to display data (required to build SQL statement).

View SQL statement.

Copy SQL statement.

On a side note, from what i have learned, the ProvideX ODBC driver for Sage 100 is most similar to the SQL-92 standard. There are some scalar functions implemented as well as some regular functions like SUM and COUNT. Table joins however are a little weird, for a simple INNER JOIN, you can use the WHERE clause to define the common columns as seen in the last screenshot above or you can define the INNER JOIN or LEFT OUTER JOIN using another method used in the FROM clause.

Here are examples for both SQL statements.

Using WHERE clause.

 SELECT "SO_SalesOrderHeader"."SalesOrderNo", "SO_SalesOrderHeader"."OrderDate", "SO_SalesOrderDetail"."ItemCode", "SO_SalesOrderDetail"."ItemCodeDesc", "SO_SalesOrderDetail"."QuantityOrdered", "SO_SalesOrderHeader"."OrderType"
 FROM   "SO_SalesOrderHeader" "SO_SalesOrderHeader", "SO_SalesOrderDetail" "SO_SalesOrderDetail"
 WHERE  ("SO_SalesOrderHeader"."SalesOrderNo"="SO_SalesOrderDetail"."SalesOrderNo") AND "SO_SalesOrderHeader"."OrderType"<>'Q'

Using FROM clause.

 SELECT "SO_SalesOrderHeader"."SalesOrderNo", "SO_SalesOrderHeader"."OrderDate", "SO_SalesOrderDetail"."ItemCode", "SO_SalesOrderDetail"."ItemCodeDesc", "SO_SalesOrderDetail"."QuantityOrdered", "SO_SalesOrderHeader"."OrderType"
 FROM   {oj "SO_SalesOrderHeader" "SO_SalesOrderHeader" INNER JOIN "SO_SalesOrderDetail" "SO_SalesOrderDetail" ON ("SO_SalesOrderHeader"."SalesOrderNo"="SO_SalesOrderDetail"."SalesOrderNo")}
 WHERE   "SO_SalesOrderHeader"."OrderType"<>'Q'

thank you for the quick response, this is going to take me some time to step through. In the past I have always had a SQL resource and now that I am at a smaller company without one I am having to cobble together what I can.