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
511 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying 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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

808 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