dublindaly
asked on
I have an SQL statement that was generated in Excel. I want to use this code in Access. How do I convert it?
Hello,
I have a Query developed in MS Query that pulls data from a datawarehouse. The query works fine in Excel but I now want to convert this to Access.
I have linked the various tables in Access that are linked in Excel Query but I now need to know how can I convert the Excel SQL Statement so that it's useable in Access.
Thanks.
I have a Query developed in MS Query that pulls data from a datawarehouse. The query works fine in Excel but I now want to convert this to Access.
I have linked the various tables in Access that are linked in Excel Query but I now need to know how can I convert the Excel SQL Statement so that it's useable in Access.
Thanks.
Never used MS Query, but as a first guess the syntax would be the same.
Can you post the current SQL you have?
Dave
Can you post the current SQL you have?
Dave
ASKER
This is the Current Code. The Data Warehouse name is DW_Overview
Thanks.
SELECT S_ORDER_ITEM.X_CIR_ID, S_EVT_ACT.COMMENTS_LONG, S_EVT_ACT.ROW_ID, S_ORDER_ITEM.VOL_UOM_CD, S_ORG_EXT.NAME, S_PROD_INT.NAME, S_ORDER.ORDER_NUM, S_EVT_ACT.ORDER_ITEM_ID, S_EVT_ACT.OWNER_LOGIN, S_EVT_ACT.REF_NUM, S_ORDER_ITEM.X_OLD_CIR_ID, S_EVT_ACT.TODO_DUE_DT, S_EVT_ACT.TODO_ACTL_END_DT , S_ORDER_ITEM.CREATED, S_ORDER.REQ_SHIP_DT, S_ORDER.FRGHT_AMT_DT, S_ORDER.CRDT_ASGN_TS, S_ORG_EXT_1.NAME, S_EVT_ACT.EVT_STAT_CD, S_ORDER_ITEM.X_ESCALATION, S_ORDER_ITEM.X_EXPEDITE, S_EVT_ACT.CREATED, S_ORDER_ITEM.X_LINE_ITEM_S TATUS, S_ORG_EXT.OU_TYPE_CD, S_EVT_ACT.LAST_UPD, S_ORG_EXT_2.NAME
FROM SIEBEL.S_EVT_ACT S_EVT_ACT, SIEBEL.S_EVT_ACT_X S_EVT_ACT_X, SIEBEL.S_ORDER S_ORDER, SIEBEL.S_ORDER_ITEM S_ORDER_ITEM, SIEBEL.S_ORDER_TYPE S_ORDER_TYPE, SIEBEL.S_ORG_EXT S_ORG_EXT, SIEBEL.S_ORG_EXT S_ORG_EXT_1, SIEBEL.S_ORG_EXT S_ORG_EXT_2, SIEBEL.S_PROD_INT S_PROD_INT
WHERE S_EVT_ACT.ORDER_ITEM_ID = S_ORDER_ITEM.ROW_ID AND S_ORDER.ROW_ID = S_ORDER_ITEM.ORDER_ID AND S_ORG_EXT.ROW_ID = S_ORDER.ACCNT_ID AND S_ORDER_TYPE.ROW_ID = S_ORDER.ORDER_TYPE_ID AND S_ORDER_ITEM.PROD_ID = S_PROD_INT.ROW_ID AND S_ORG_EXT_1.ROW_ID = S_EVT_ACT_X.ATTRIB_03 AND S_EVT_ACT.ROW_ID = S_EVT_ACT_X.PAR_ROW_ID AND S_ORG_EXT_2.ROW_ID = S_ORDER_ITEM.SERV_ACCNT_ID AND ((S_EVT_ACT.TODO_CD='Proce ss Step') AND (S_EVT_ACT.EVT_STAT_CD Not In ('Cancelled','Done') And S_EVT_ACT.EVT_STAT_CD<>'In valid') AND (S_ORDER_ITEM.X_CIR_ID Is Not Null) AND (S_ORDER_TYPE.NAME<>'Cance llation') AND (S_EVT_ACT.REF_NUM Is Not Null))
ORDER BY S_ORDER_ITEM.CREATED DESC
Thanks.
SELECT S_ORDER_ITEM.X_CIR_ID, S_EVT_ACT.COMMENTS_LONG, S_EVT_ACT.ROW_ID, S_ORDER_ITEM.VOL_UOM_CD, S_ORG_EXT.NAME, S_PROD_INT.NAME, S_ORDER.ORDER_NUM, S_EVT_ACT.ORDER_ITEM_ID, S_EVT_ACT.OWNER_LOGIN, S_EVT_ACT.REF_NUM, S_ORDER_ITEM.X_OLD_CIR_ID,
FROM SIEBEL.S_EVT_ACT S_EVT_ACT, SIEBEL.S_EVT_ACT_X S_EVT_ACT_X, SIEBEL.S_ORDER S_ORDER, SIEBEL.S_ORDER_ITEM S_ORDER_ITEM, SIEBEL.S_ORDER_TYPE S_ORDER_TYPE, SIEBEL.S_ORG_EXT S_ORG_EXT, SIEBEL.S_ORG_EXT S_ORG_EXT_1, SIEBEL.S_ORG_EXT S_ORG_EXT_2, SIEBEL.S_PROD_INT S_PROD_INT
WHERE S_EVT_ACT.ORDER_ITEM_ID = S_ORDER_ITEM.ROW_ID AND S_ORDER.ROW_ID = S_ORDER_ITEM.ORDER_ID AND S_ORG_EXT.ROW_ID = S_ORDER.ACCNT_ID AND S_ORDER_TYPE.ROW_ID = S_ORDER.ORDER_TYPE_ID AND S_ORDER_ITEM.PROD_ID = S_PROD_INT.ROW_ID AND S_ORG_EXT_1.ROW_ID = S_EVT_ACT_X.ATTRIB_03 AND S_EVT_ACT.ROW_ID = S_EVT_ACT_X.PAR_ROW_ID AND S_ORG_EXT_2.ROW_ID = S_ORDER_ITEM.SERV_ACCNT_ID
ORDER BY S_ORDER_ITEM.CREATED DESC
What are your tables called in Access?
e.g.
U have this in Excel
SIEBEL.S_EVT_ACT
What is it called in Access?
Change all tablenames to be what they are in Acces
what this is doing
SIEBEL.S_EVT_ACT S_EVT_ACT
is setting table alias,
just doung S_EVT_ACT should be sufficient (if thats your tablename)
e.g.
U have this in Excel
SIEBEL.S_EVT_ACT
What is it called in Access?
Change all tablenames to be what they are in Acces
what this is doing
SIEBEL.S_EVT_ACT S_EVT_ACT
is setting table alias,
just doung S_EVT_ACT should be sufficient (if thats your tablename)
ASKER
Hi, this is the code in Access but it's still not picking up the data. Could it be the Relationship between tables?
SELECT DW_OVERVIEW_S_ORDER.ORDER_ NUM, DW_OVERVIEW_S_ORG_EXT.NAME , DW_OVERVIEW_S_ORDER.STATUS _CD, DW_OVERVIEW_S_PROD_INT.NAM E
FROM ((DW_OVERVIEW_S_ORDER INNER JOIN DW_OVERVIEW_S_ORG_EXT ON DW_OVERVIEW_S_ORDER.ACCNT_ ID = DW_OVERVIEW_S_ORG_EXT.ROW_ ID) INNER JOIN DW_OVERVIEW_S_PROD_INT ON DW_OVERVIEW_S_ORDER.ROW_ID = DW_OVERVIEW_S_PROD_INT.ROW _ID) INNER JOIN DW_OVERVIEW_S_ORDER_ITEM ON (DW_OVERVIEW_S_PROD_INT.RO W_ID = DW_OVERVIEW_S_ORDER_ITEM.P ROD_ID) AND (DW_OVERVIEW_S_ORG_EXT.ROW _ID = DW_OVERVIEW_S_ORDER_ITEM.B ILL_ACCNT_ ID) AND (DW_OVERVIEW_S_ORDER.ROW_I D = DW_OVERVIEW_S_ORDER_ITEM.O RDER_ID) AND (DW_OVERVIEW_S_ORDER.ROW_I D = DW_OVERVIEW_S_ORDER_ITEM.O RDER_ID)
GROUP BY DW_OVERVIEW_S_ORDER.ORDER_ NUM, DW_OVERVIEW_S_ORG_EXT.NAME , DW_OVERVIEW_S_ORDER.STATUS _CD, DW_OVERVIEW_S_PROD_INT.NAM E
HAVING (((DW_OVERVIEW_S_ORDER.STA TUS_CD)="I n Progress"));
SELECT DW_OVERVIEW_S_ORDER.ORDER_
FROM ((DW_OVERVIEW_S_ORDER INNER JOIN DW_OVERVIEW_S_ORG_EXT ON DW_OVERVIEW_S_ORDER.ACCNT_
GROUP BY DW_OVERVIEW_S_ORDER.ORDER_
HAVING (((DW_OVERVIEW_S_ORDER.STA
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What are you after, just general how to's?