ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> ODBC General Issues >> MS Query, FGIs by Customer Code
http://www.istechforum.com/YaBB.pl?num=1333389908

Message started by AndyOB on 04/02/12 at 11:05:08

Title: MS Query, FGIs by Customer Code
Post by AndyOB on 04/02/12 at 11:05:08

Code:

SELECT BKBMMSTR.BKBM_PARENT,
BKBMMSTR."BKBM_PROD_LINE^",
BKBMMSTR.BKBM_COMPONENT,
MTICMSTR.MTIC_PROD_DESC,
MTICMSTR.MTIC_PROD_SUBST_3,
MTICMSTR.MTIC_PROD_SUBST_4,
MTICMSTR.MTIC_PROD_SUBST_5,
MTICMSTR.MTIC_PROD_TYPE,
MTICMSTR.MTIC_PROD_VEND_1,
MTICMSTR.MTIC_PROD_CUST,
BKICMSTR.BKIC_PROD_DPTNT
FROM BKBMMSTR BKBMMSTR, BKICMSTR BKICMSTR, MTICMSTR MTICMSTR
WHERE BKBMMSTR.BKBM_COMPONENT = BKICMSTR.BKIC_PROD_CODE AND MTICMSTR.MTIC_PROD_CODE = BKICMSTR.BKIC_PROD_CODE

Can anyone tell me why this isn't working? If I create a PivotTable with ~~MTIC_CUST_CODE~~ MTIC_PROD_CUST as the Report filter followed by the BKBM_PARENT Field as the first Row Labels, then select just 1 customer from ~~MTIC_CUST_CODE~~ MTIC_PROD_CUST...

Basically I am hoping that I can choose a customer and it'll look through the item dB, and if the customer is part of that item it'll bring up the item's BOM

Any help appreciated.

Edit: See strikeouts above, my apologies for the confusion.

Title: Re: MS Query, FGIs by Customer Code
Post by Kelloggs on 04/02/12 at 13:13:10

MTIC_CUST_CODE???
You mean MTIC_PROD_CUST???

:)

Kelloggs

Title: Re: MS Query, FGIs by Customer Code
Post by Kelloggs on 04/03/12 at 08:09:31

Why dont you query just one customer, if that is the problem?

Actually what does not work?

Could it be because the MTICMSTR.MTIC_PROD_CUST has blanks on it?

:-?

Kelloggs

Title: Re: MS Query, FGIs by Customer Code
Post by AndyOB on 04/03/12 at 08:44:22

I actually figured out the problem, the problem being our customer code for F type items is slightly "off". The data isn't totally accurate, so it's working as it should, but I have to update a lot of records in Evo to point to the correct customer. So sorry for the confusion.

Title: Re: MS Query, FGIs by Customer Code
Post by Kelloggs on 04/03/12 at 08:51:56

If there is a logic (most of the time there is)
You can use an update sql query to update your records, ratter than do it manually.

Let us know if you need help with that

:P

Kelloggs