Hi John.

>- A current price report of all items that have ever been bought by a client. Current price of an item bough by the client is in a seperate price list file.
>
>For example :
>
>If Item No, 12345 has been bough in the past by Customer ABC for Item Price at that time of $10.50 but the current price of that item for that customer ( according to the active current price list used for that customer) is $11.25, on the report result I would like:
>
>Customer ABC
>Item 12345 Current Price $11.25
>
>Many customer share the same price list code and price list file for the same item.
>
>------
>Here are the file structures:
>
>Table: UxDetail (Order Item History file)
>Fields: cCustID, cItemCode, nPrice ($10,50)
>
>Table: ArCus (Customer file)
>Fields: cCustID, cPricListCode
>
>Table: ICPrice
>Fields, cItemCode, cPriceListCode, nCurrentPrice ($11.25)

I'm not sure where you got those file structures from; the actual names of the fields you need are:

UxDetail: CustCode, ItemNumber, and PriceListCode
ICPric: ITEMNO, PRICELIST, MARKUPCOST (I believe it's MARKUPCOST you're looking for)

Given what you've asked for, you may not need ARCus. If you do, the field names from there are IDCUST and PRICLIST.

Just for fun, I built a report that does this in Stonefield Query (www.stonefieldquery.com) in about 30 seconds. If you're interested in knowing more about that, let me know.

Doing it the manual way, you need a SQL statement that looks like:

select distinct UXDETAIL.CustCode,UXDETAIL.ItemNumber,UXDETAIL.PriceListCode,ICPRIC.PRICELIST,ICPRIC.MARKUPCOST from UXDETAIL inner join ICPRIC on UXDETAIL.ItemNumber=ICPRIC.ITEMNO and UXDETAIL.PriceListCode=ICPRIC.PRICELIST order by 1, 2

However, this may be complicated by the fact that UxDetail isn't in the same database as ICPric.If you're using Pervasive as the database engine, you definitely can't do this because it doesn't support queries across database engines (which Stonefield Query provides a workaround for so it's easily done). If you're using SQL Server, be sure to alias the table names with the database names (such as "from UniSales.dbo.UXDETAIL inner join Accpac.dbo.ICPRIC").

Doug