Search results for query: *

Thanks DjangMan. We import via CSV files, I use VBA/Excel and yes with convoluted logic to test for the various pricing combos...as our company has «fairly» simple costing I let VBA handle it...even sends HTML email to admins on every scanned batch ! The reason being: 1- EDI software we use...

Still testing...but the below seems to do it !: SELECT ICPRICP.UNITPRICE, ICPRICP.DPRICETYPE, ICPRICP.PRICELIST, ICPRICP.SALESTART, ICPRICP.SALEEND, ICPRIC.PRICESTART, ICPRIC.PRICEEND, ARCSP.IDCUST, ARCSP.IDCUSTSHPT, ICPRICP.ITEMNO, ICCUPR.EXPIRE, ICCUPR.FIXPRICE, ICCUPR.STARTDATE...

Hello, I need to verify pricing before importing sales orders into accpac 6.0/MSSQL I have the vba I need, however having trouble getting the correct SQL query to return rows with information needed by VBA procedure to verify pricing. Fields needed to verify pricing: ICPRICP.UNITPRICE...

Yes, but I want to use the above code form Excel...

I found some code and modified a bit... Sub MainSub() OESHI1detail5.Compose Array(OESHI1header) OESHI1header.Cancel OESHI1header.Init OESHI1headerFields("ORDNUMBER").Value = " & sheet1.cells(1,1)& " ' Order Number to generate SHIPMENT and INOICE for... for i = 1 to LastRow...

Hello, I need a «Quick» way for our shipping dept to create a SHIPMENT once an order was picked.... I was thinking of this: Dump this query onto an Excel spreadsheet: SELECT OEORDH.ORDNUMBER, OEORDH.ORDUNIQ, OEORDD.LINETYPE, OEORDD.ITEM, OEORDD.DESC, OEORDD.QTYBACKORD FROM OEORDD INNER JOIN...

Thank You Mirtheil :), This seem to do the trick without killing the tables...: UPDATE POPORL SET POPORL.EXPARRIVAL = 20100418 FROM POPORH1 WHERE POPORL.PORHSEQ=POPORH1.PORHSEQ AND POPORH1.PONUMBER='004386'

Hello.. Will this work : UPDATE POPORL SET POPORL.EXPARRIVAL = 20100418 FROM POPORH1 POPORL WHERE POPORL.PORHSEQ=POPORH1.PORHSEQ AND POPORH1.PONUMBER="004386"

Hello, We are using Sage Accpac 500 ERP (Version 5.5A). Is it possible to have web pages QUERY the database ? What set ups must be done if possible to accomplish this ?

Thanks Mirtheil, I needed to add a bracket after the AND in the WHERE clause.... Now I retreive 2 rows with the below, one row with the value column heading of VALLUE containing the value for ICITEMO.OPTFIELD="4250 and another row with the value ICITEMO.OPTFIELD)="4110. Can this be returned...

Thanks Mirtheil, However does not seem return the «both» values as columns for the ICITEMO.VALUES..... SELECT POPORH1.DATE, POPORH1.PONUMBER, POPORH1.VDNAME, POPORL.ITEMNO, POPORL.ITEMDESC, POPORH1.EXPARRIVAL AS PO_ETD, POPORL.EXPARRIVAL AS LINE_ETA, POPORL.OQORDERED, POPORL.OQRECEIVED...

Hello, Trying to built a simple ACCPAC report which requires OPTIONAL FIELD TABLE VALUES to be JOINED to the MAIN select ...... I have this : SELECT POPORH1.DATE, POPORH1.PONUMBER, POPORH1.VDNAME, POPORL.ITEMNO, POPORL.ITEMDESC, POPORH1.EXPARRIVAL AS PO_ETD, POPORL.EXPARRIVAL AS LINE_ETA...

Hi, Not sure how to answer this..... One query would ne nice ...but several is fine as I will run through excel... 1- SELECT ICHIST.ITEMNO, ICITEM.DESC, Sum(ICHIST.QUANTITY) AS SumOfQUANTITY, Sum(ICHIST.HOMEEXTCST)/SumOfQUANTITY AS COST FROM ICHIST LEFT JOIN ICITEM ON ICHIST.ITEMNO =...

Again, thanks.. Now I have this: SELECT ICHIST.ITEMNO, ICITEM.DESC, ICHIST.TRANSTYPE, ICHIST.HOMEEXTCST/ICHIST.QUANTITY AS POCOST, Max(ICHIST.TRANSDATE) AS MaxOfTRANSDATE FROM ICHIST LEFT JOIN ICITEM ON ICHIST.ITEMNO = ICITEM.ITEMNO GROUP BY ICHIST.ITEMNO, ICITEM.DESC, ICHIST.TRANSTYPE...

*lol* I guess I should pay more attention to the data dictionary.... Thansk again etienne...Got this now: SELECT ICHIST.ITEMNO, Sum(ICHIST.QUANTITY) AS SumOfQUANTITY, Sum(ICHIST.HOMEEXTCST)/SumOfQUANTITY AS COST FROM ICHIST WHERE (((ICHIST.ITEMNO)="101614") AND ((ICHIST.TRANSDATE)<=20100115))...

Thanks etienne, This seems to give me the ON HAND QTY at year end: SELECT ICHIST.ITEMNO, Sum(ICHIST.QUANTITY) AS SumOfQUANTITY FROM ICHIST WHERE (((ICHIST.ITEMNO)="101614") AND ((ICHIST.TRANSDATE)<=20100101)) GROUP BY ICHIST.ITEMNO; Now where would I get the VALUE of that item at year end ?

January 01 2010

Sage Accpac 500 ERP (Version 5.5A) with Pervasive 9: I need to provide a spreadsheet with our YEAR END INVENTORY QTY by item with the last received date, last received date cost(prior to year end), and ITEM value at year end.... Can this be done via a few SQL queries ?