Re: Access and ITHL01

Thanks for the reply. The BPCS version is 6.0 Release 02.

Yes, we are joining to the CALENFY table

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));

Date: Thu, 7 Mar 2013 09:50:53 -0800
From: brobins3d@xxxxxxxxx
To: bpcs-l@xxxxxxxxxxxx
Subject: Re: [BPCS-L] Access and ITHL01

On 3/7/2013 9:14 AM, Adalberto B.C. wrote:

Hi, I have been looking around on the web for an explanation regarding a query from access to BPCS. So far, no luck. We have a access DB that has a query to the ITHL01 table, with a join to a CALENFY, to retrieve some records. The weird thing is that we retrieve the correct information, but it shows doubled on Access. I have reviewed the SQL query in access and I could not find anything that could be causing this. Also, we have the same table in SQL Server, also filled from BPCS, and in this table there are no duplicated records. Is there any setting I need to change in order to prevent the double records?

Can you show the generated SQL statement? Whenever records double up
it's either because logically it can't be solved with straight SQL or
there's a missed criteria in the Where statement.

I assume you're joining to the CALENFY to access a period number. Seems
like an unnecessary file to be maintained since there exists the GPM
file. BTW, could you list the version of BPCS when you reply? We might
also need to see the CALENFY records that would apply to the SQL statement.

Bill

--
This is the BPCS ERP System (BPCS-L) mailing list
To post a message email: BPCS-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/bpcs-l
or email: BPCS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/bpcs-l.