MS Query query-> Access

G

Gasperix

Hi,

I try to replicate in Access a query that works fine in MS Query ...

MS Query:

SELECT VEZAK1.Sifra as [SifraP],VEZAK1.Opis as [NazivP],(Data1) as [Znesek]
,VEZAK2.Opis as [Tip]
FROM DIS_Data
LEFT OUTER JOIN Dis_Sifr as VEZAK1 ON dis_data.Kljuc1 = VEZAK1.Id
LEFT OUTER JOIN Dis_Sifr as VEZAK2 ON dis_data.Kljuc2 = VEZAK2.Id

I've tried this:

SELECT VEZAK1.Sifra as [SifraP],VEZAK1.Opis as [NazivP],(Data1) as [Znesek]
,VEZAK2.Opis as [Tip]
FROM
(DIS_Data LEFT OUTER JOIN Dis_Sifr as VEZAK1 ON dis_data.Kljuc1 = VEZAK1.Id)
LEFT OUTER JOIN Dis_Sifr as VEZAK2 ON dis_data.Kljuc2 = VEZAK2.Id

But it takes more than 10 minutes in Access, while MS Query is done in 10 sec.


Can anyone suggest what has to be modified in the second query?
Or at least an explanation of the times needed in MS Query or Access...
 
J

John Spencer

Are you running the Access query against tables on MS SQL server or on
tables that are in a JET (native access db engine) database?

If the latter, have you applied indexes to the same fields as those that are
indexed on the server.

What version of Access? What type of data base (mdb, adb)?

In Access using DAO the query would normally be written as follows (unless
you have checked SQL Server Compatible Syntax)
SELECT VEZAK1.Sifra as [SifraP]
,VEZAK1.Opis as [NazivP],(Data1) as [Znesek]
,VEZAK2.Opis as [Tip]
FROM
(DIS_Data LEFT JOIN Dis_Sifr as VEZAK1 ON dis_data.Kljuc1 = VEZAK1.Id)
LEFT JOIN Dis_Sifr as VEZAK2 ON dis_data.Kljuc2 = VEZAK2.Id
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

Gasperix

John, thanks for your reply.

I'm using Access2007 and Excel 2007.
Database I'm connecting to is a Sybase SQL Anywhere database.

I'd like to point out again that the query as written works in access, but
is tremendously slow comparing to MS Query.

I've tried your suggestion - deleting "outer" - and the result is same.


Regards



John Spencer said:
Are you running the Access query against tables on MS SQL server or on
tables that are in a JET (native access db engine) database?

If the latter, have you applied indexes to the same fields as those that are
indexed on the server.

What version of Access? What type of data base (mdb, adb)?

In Access using DAO the query would normally be written as follows (unless
you have checked SQL Server Compatible Syntax)
SELECT VEZAK1.Sifra as [SifraP]
,VEZAK1.Opis as [NazivP],(Data1) as [Znesek]
,VEZAK2.Opis as [Tip]
FROM
(DIS_Data LEFT JOIN Dis_Sifr as VEZAK1 ON dis_data.Kljuc1 = VEZAK1.Id)
LEFT JOIN Dis_Sifr as VEZAK2 ON dis_data.Kljuc2 = VEZAK2.Id
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Gasperix said:
Hi,

I try to replicate in Access a query that works fine in MS Query ...

MS Query:

SELECT VEZAK1.Sifra as [SifraP],VEZAK1.Opis as [NazivP],(Data1) as
[Znesek]
,VEZAK2.Opis as [Tip]
FROM DIS_Data
LEFT OUTER JOIN Dis_Sifr as VEZAK1 ON dis_data.Kljuc1 = VEZAK1.Id
LEFT OUTER JOIN Dis_Sifr as VEZAK2 ON dis_data.Kljuc2 = VEZAK2.Id

I've tried this:

SELECT VEZAK1.Sifra as [SifraP],VEZAK1.Opis as [NazivP],(Data1) as
[Znesek]
,VEZAK2.Opis as [Tip]
FROM
(DIS_Data LEFT OUTER JOIN Dis_Sifr as VEZAK1 ON dis_data.Kljuc1 =
VEZAK1.Id)
LEFT OUTER JOIN Dis_Sifr as VEZAK2 ON dis_data.Kljuc2 = VEZAK2.Id

But it takes more than 10 minutes in Access, while MS Query is done in 10
sec.


Can anyone suggest what has to be modified in the second query?
Or at least an explanation of the times needed in MS Query or Access...
Click to expand...
Click to expand...
 
J

John Spencer

Sorry, I have no suggestions on why the difference in timing.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
John, thanks for your reply.

I'm using Access2007 and Excel 2007.
Database I'm connecting to is a Sybase SQL Anywhere database.

I'd like to point out again that the query as written works in access, but
is tremendously slow comparing to MS Query.

I've tried your suggestion - deleting "outer" - and the result is same.


Regards



John Spencer said:
Are you running the Access query against tables on MS SQL server or on
tables that are in a JET (native access db engine) database?

If the latter, have you applied indexes to the same fields as those that are
indexed on the server.

What version of Access? What type of data base (mdb, adb)?

In Access using DAO the query would normally be written as follows (unless
you have checked SQL Server Compatible Syntax)
SELECT VEZAK1.Sifra as [SifraP]
,VEZAK1.Opis as [NazivP],(Data1) as [Znesek]
,VEZAK2.Opis as [Tip]
FROM
(DIS_Data LEFT JOIN Dis_Sifr as VEZAK1 ON dis_data.Kljuc1 = VEZAK1.Id)
LEFT JOIN Dis_Sifr as VEZAK2 ON dis_data.Kljuc2 = VEZAK2.Id
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Gasperix said:
Hi,

I try to replicate in Access a query that works fine in MS Query ...

MS Query:

SELECT VEZAK1.Sifra as [SifraP],VEZAK1.Opis as [NazivP],(Data1) as
[Znesek]
,VEZAK2.Opis as [Tip]
FROM DIS_Data
LEFT OUTER JOIN Dis_Sifr as VEZAK1 ON dis_data.Kljuc1 = VEZAK1.Id
LEFT OUTER JOIN Dis_Sifr as VEZAK2 ON dis_data.Kljuc2 = VEZAK2.Id

I've tried this:

SELECT VEZAK1.Sifra as [SifraP],VEZAK1.Opis as [NazivP],(Data1) as
[Znesek]
,VEZAK2.Opis as [Tip]
FROM
(DIS_Data LEFT OUTER JOIN Dis_Sifr as VEZAK1 ON dis_data.Kljuc1 =
VEZAK1.Id)
LEFT OUTER JOIN Dis_Sifr as VEZAK2 ON dis_data.Kljuc2 = VEZAK2.Id

But it takes more than 10 minutes in Access, while MS Query is done in 10
sec.


Can anyone suggest what has to be modified in the second query?
Or at least an explanation of the times needed in MS Query or Access...
Click to expand...
Click to expand...
Click to expand...
 
G

Gasperix

John,

fyi - I succeeded to resolve the issue - the pass through query solves
everything.
In a way I feel a bit silly...
;-)

Thanks for your cooperation.

Gasper


John Spencer said:
Sorry, I have no suggestions on why the difference in timing.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
John, thanks for your reply.

I'm using Access2007 and Excel 2007.
Database I'm connecting to is a Sybase SQL Anywhere database.

I'd like to point out again that the query as written works in access, but
is tremendously slow comparing to MS Query.

I've tried your suggestion - deleting "outer" - and the result is same.


Regards



John Spencer said:
Are you running the Access query against tables on MS SQL server or on
tables that are in a JET (native access db engine) database?

If the latter, have you applied indexes to the same fields as those that are
indexed on the server.

What version of Access? What type of data base (mdb, adb)?

In Access using DAO the query would normally be written as follows (unless
you have checked SQL Server Compatible Syntax)
SELECT VEZAK1.Sifra as [SifraP]
,VEZAK1.Opis as [NazivP],(Data1) as [Znesek]
,VEZAK2.Opis as [Tip]
FROM
(DIS_Data LEFT JOIN Dis_Sifr as VEZAK1 ON dis_data.Kljuc1 = VEZAK1.Id)
LEFT JOIN Dis_Sifr as VEZAK2 ON dis_data.Kljuc2 = VEZAK2.Id
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi,

I try to replicate in Access a query that works fine in MS Query ...

MS Query:

SELECT VEZAK1.Sifra as [SifraP],VEZAK1.Opis as [NazivP],(Data1) as
[Znesek]
,VEZAK2.Opis as [Tip]
FROM DIS_Data
LEFT OUTER JOIN Dis_Sifr as VEZAK1 ON dis_data.Kljuc1 = VEZAK1.Id
LEFT OUTER JOIN Dis_Sifr as VEZAK2 ON dis_data.Kljuc2 = VEZAK2.Id

I've tried this:

SELECT VEZAK1.Sifra as [SifraP],VEZAK1.Opis as [NazivP],(Data1) as
[Znesek]
,VEZAK2.Opis as [Tip]
FROM
(DIS_Data LEFT OUTER JOIN Dis_Sifr as VEZAK1 ON dis_data.Kljuc1 =
VEZAK1.Id)
LEFT OUTER JOIN Dis_Sifr as VEZAK2 ON dis_data.Kljuc2 = VEZAK2.Id

But it takes more than 10 minutes in Access, while MS Query is done in 10
sec.


Can anyone suggest what has to be modified in the second query?
Or at least an explanation of the times needed in MS Query or Access...
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

outer joint query 1
upc and pos 3
parent child link query 1
A bug in GUI of creating query with more outer joins between two tables 5
Query the same table three times 0
#deleted on linked table select query 2
Running balance for debit and Credit using query in Access 2007 0
Subquery Help 1

Top