Solved

I have an SQL statement that was generated in Excel. I want to use this code in Access. How do I convert it?

Posted on 2006-07-13
6
531 Views
Last Modified: 2007-12-19
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.
0
Comment
Question by:dublindaly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17097643
Urm, that might be difficult as we do not have any details

What are you after, just general how to's?
0
 
LVL 34

Expert Comment

by:flavo
ID: 17097644
Never used MS Query, but as a first guess the syntax would be the same.

Can you post the current SQL you have?

Dave
0
 

Author Comment

by:dublindaly
ID: 17097973
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_STATUS, 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='Process Step') AND (S_EVT_ACT.EVT_STAT_CD Not In ('Cancelled','Done') And S_EVT_ACT.EVT_STAT_CD<>'Invalid') AND (S_ORDER_ITEM.X_CIR_ID Is Not Null) AND (S_ORDER_TYPE.NAME<>'Cancellation') AND (S_EVT_ACT.REF_NUM Is Not Null))
ORDER BY S_ORDER_ITEM.CREATED DESC
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Expert Comment

by:rockiroads
ID: 17098097
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)

0
 

Author Comment

by:dublindaly
ID: 17106093
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.NAME
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.ROW_ID = DW_OVERVIEW_S_ORDER_ITEM.PROD_ID) AND (DW_OVERVIEW_S_ORG_EXT.ROW_ID = DW_OVERVIEW_S_ORDER_ITEM.BILL_ACCNT_ID) AND (DW_OVERVIEW_S_ORDER.ROW_ID = DW_OVERVIEW_S_ORDER_ITEM.ORDER_ID) AND (DW_OVERVIEW_S_ORDER.ROW_ID = DW_OVERVIEW_S_ORDER_ITEM.ORDER_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.NAME
HAVING (((DW_OVERVIEW_S_ORDER.STATUS_CD)="In Progress"));
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 17106613
Ive broken  your query down to this to make it easier for you to read. Ive used table aliases also

SELECT DISTINCTROW  a.ORDER_NUM, b.NAME, a.STATUS_CD, c.NAME
FROM DW_OVERVIEW_S_ORDER a, DW_OVERVIEW_S_ORG_EXT b, DW_OVERVIEW_S_PROD_INT c, JOIN DW_OVERVIEW_S_ORDER_ITEM d
WHERE a.ACCNT_ID = b.ROW_ID
AND a.ROW_ID = c.ROW_ID
AND c.ROW_ID = d.PROD_ID
AND b.ROW_ID = d.BILL_ACCNT_ID
AND a.ROW_ID = d.ORDER_ID
AND a.ROW_ID = d.ORDER_ID
AND a.STATUS_CD ="In Progress"


Now from here u can see your joins more clearly

a.ROW_ID matches to C.ROW_ID
but a.ROW_ID also matches to d.ORDER_ID
so that is how a links to c and d?
But to confuse matters, u say C.ROW_ID = D.PROD_ID
this is unnecessary as you already have it joined


This probably serve u better

SELECT DISTINCTROW  a.ORDER_NUM, b.NAME, a.STATUS_CD, c.NAME
FROM DW_OVERVIEW_S_ORDER a, DW_OVERVIEW_S_ORG_EXT b, DW_OVERVIEW_S_PROD_INT c, JOIN DW_OVERVIEW_S_ORDER_ITEM d
WHERE a.ACCNT_ID = b.ROW_ID
AND a.ROW_ID = c.ROW_ID
AND c.ROW_ID = d.PROD_ID
AND b.ROW_ID = d.BILL_ACCNT_ID
AND a.STATUS_CD ="In Progress"


Now using this can you sort out your table relations? ensure joins are correct
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Moving away from Access 2003 adp files 4 48
Using a combo box to search a form. 3 36
Multiple tables for a tabbed form 2 33
Access 2010: Combo Box Query 5 19
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question