View Single Post
  #5 (permalink)  
Old September 29th, 2005, 11:14 AM
Corey Corey is offline
Friend of Wrox
 
Join Date: Sep 2005
Location: , , USA.
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Corey
Default

Yes most of the fields that use any formulas are dates and currency

Below is the SQL code:

SELECT DISTINCT Buyer.[Buyer Name], SM_PO.PO_EXPTD_DT AS [Expected Date], SM_PO.PO_PO_NUM AS [PO#], SM_SUPPLIER.S_NAME AS Vendor, SM_PRODUCT.P_STYL AS [Item Number], SM_PRODUCT.P_DESC AS Description, SM_PRODUCT.P_CTRL_COST AS [Unit Cost], SM_PRODUCT.P_CTRL_RETAIL AS [Retail Rrice], SM_PO_LN.POL_QTY_ORD AS Qty, ([P_CTRL_RETAIL]-[P_CTRL_COST]) AS [Mark Up], ([p_ctrl_retail]-[P_Ctrl_cost])/[p_ctrl_retail] AS GM, ([P_CTRL_COST]*[POL_QTY_ORD]) AS [Extended Cost], ([P_CTRL_RETAIL]*[POL_QTY_ORD]) AS [Retail Potential], SM_PRODUCT.P_STAT_I AS Status, Last(Tracking_Table.Date) AS [Date], Last(Tracking_Table.Tracking) AS Tracking, Last(Tracking_Table.Status) AS [Note] INTO Landing_A1_tbl
FROM Tracking_Table RIGHT JOIN (((SM_PRODUCT RIGHT JOIN SM_PO_LN ON SM_PRODUCT.P_PROD_INT_NUM = SM_PO_LN.POL_PROD_INT_NUM) LEFT JOIN (SM_PO LEFT JOIN SM_SUPPLIER ON SM_PO.PO_SUP_CD = SM_SUPPLIER.S_SUP_CD) ON SM_PO_LN.POL_PO_NUM = SM_PO.PO_PO_NUM) LEFT JOIN Buyer ON SM_PRODUCT.P_CLASS_CD_2 = Buyer.[Buyer #]) ON Tracking_Table.PO = SM_PO_LN.POL_PO_NUM
GROUP BY Buyer.[Buyer Name], SM_PO.PO_EXPTD_DT, SM_PO.PO_PO_NUM, SM_SUPPLIER.S_NAME, SM_PRODUCT.P_STYL, SM_PRODUCT.P_DESC, SM_PRODUCT.P_CTRL_COST, SM_PRODUCT.P_CTRL_RETAIL, SM_PO_LN.POL_QTY_ORD, ([P_CTRL_RETAIL]-[P_CTRL_COST]), ([p_ctrl_retail]-[P_Ctrl_cost])/[p_ctrl_retail], ([P_CTRL_COST]*[POL_QTY_ORD]), ([P_CTRL_RETAIL]*[POL_QTY_ORD]), SM_PRODUCT.P_STAT_I, SM_PRODUCT.P_CLASS_CD_2
HAVING (((SM_PO.PO_EXPTD_DT) Between Date()-30 And Date()+30) AND ((SM_PRODUCT.P_CTRL_COST)>0))
ORDER BY SM_PO.PO_EXPTD_DT, SM_PRODUCT.P_STYL;