Re: Access and ITHL01

On 3/12/2013 8:09 AM, Adalberto B.C. wrote:

Thanks for the reply. The BPCS version is 6.0 Release 02.
Here it's the SQL:

SELECT BPCSDB_ITHL01.TWHS, BPCSDB_ITHL01.TPFDV, BPCSDB_ITHL01.TPROD, BPCSDB_ITHL01.TTYPE, BPCSDB_ITHL01.TQTY, BPCSDB_ITHL01.TLOT, BPCSDB_ITHL01.TSCST, CALENFY06A09.FYEAR, CALENFY06A09.FPERIOD, [TQTY]*[TSCST] AS MULTIPLICA, [Code Desc].BU, BPCSDB_ITHL01.TRES, [Code Desc].Desc
FROM CALENFY06A09 INNER JOIN ([Code Desc] INNER JOIN BPCSDB_ITHL01 ON [Code Desc].IClass = BPCSDB_ITHL01.TCLAS) ON CALENFY06A09.TTDTE = BPCSDB_ITHL01.TTDTE
WHERE (((BPCSDB_ITHL01.TWHS)="02") AND ((BPCSDB_ITHL01.TTYPE)="R") AND ((CALENFY06A09.FYEAR)=2013) AND ((CALENFY06A09.FPERIOD)=5));

To straight up answer your original question, add the word DISTINCT after the SELECT. Example "Select Distinct BPCSDB_ITHL01".

The downside to this approach is it would hide truly duplicative data.

Have you double-checked the [Code Desc] table to make sure there's no duplicates there? I assume that it's a logical based on the IICL01. I don't know if 6.0.2 added another key field such as a company number.

Bill