Is there any alternative way to write this query because it causes ms access to stop

[bhakti0525]

I have my query like this:

Select p2.SiebelProdOffID as OfferId
   , p2.ProdOffName as RootOffering
   ,p3.SiebelProdOffID as L2OfferId
   ,p1.Participant2Name as L2OfferComponent
   ,(Cstr(p4.End2Min)+'..'+Cstr(p4.End2Max)) 
      as L2Cardinality
   , Null as L3OfferId 
   ,Null as L3OfferComponent
   , Null as L3Cardinality
from ((ProdOffToProdOffAssoc p1
   right join ProdOff p2 
      on (p1.Participant1Name=p2.ProdOffName 
         and p1.AssocTypeName='Aggregation'))
   left join ProdOff as p3 
      on p1.Participant2Name=p3.ProdOffName)

It causes ms access to stop the application.

please omit the attribut of alias p4.

The query looks like this:

Select p2.SiebelProdOffID as OfferId
   , p2.ProdOffName as RootOffering
   ,p3.SiebelProdOffID as L2OfferId
   ,p1.Participant2Name as L2OfferComponent
   , Null as L3OfferId 
   ,Null as L3OfferComponent
   , Null as L3Cardinality 
from (((ProdOffToProdOffAssoc p1
   right join ProdOff p2 
      on (p1.Participant1Name=p2.ProdOffName 
         and p1.AssocTypeName='Aggregation'))
   left join ProdOff as p3 
      on p1.Participant2Name=p3.ProdOffName)

[zmbd]

+ When you say, "... ms access to stop the application. ..."
Do you mean that Access shuts down without any warning or are you intending some other "stops" as in it will not run the intended code or query

+ There are multiple errors in the formatting of the posted SQL; however, I suspect that these are typos.

To get around this, please open your query in "design view, "switch to "SQL View" (various methods, easiest is the bottom right hand corner in Design View, three icons, one is "SQL"; the second easiest is to right-click in the table section of the query editor and select {SQL View} from the shortcut menu

Select the SQL as presented and copy and paste that to the thread. PLEASE select the posted SQL and format it using the [CODE/] tool.

+-+ Off hand:
Block2 Line8 "ProdOffToProdOffAssoc p1", should this be "[ProdOffToProdOffAssoc p1]" if you really have a space there or do you intend "[ProdOffToProdOffAssoc]![p1]" as in a table-field relationship?

Same thing with Block2 Line9 "ProdOff p2"

etc...

[NeoPa]

ZMBD:
should this be "[ProdOffToProdOffAssoc p1]"

No. I suspect this means :

[ProdOffToProdOffAssoc] AS [p1]

These are both valid ways to write the same thing, though I prefer always to be explicit to avoid any possibility of confusion.

[zmbd]

Personally, I don't like the implicit options, especially when mixed in with explicit calls... I wonder if that isn't part of the issue - along the lines of if one is going to square-bracket ([]) part of the SQL one should do so for the entire SQL?

[NeoPa]

In answer to the original question I'd say this looks like the tables are linked by a name field. With large numbers of records this can cause Access to stop responding for long periods of time and appear to have stopped.

First understand that Access typically stops responding in many situations where queries are run. This is actually not an indication of a real problem, but simply of a delay while it works on getting results.

Second, linking tables on a non indexed field is likely to be many times slower than using an index. A long text field in an index is likely to be many times slower than a numeric field or a text one of short length. A Long Integer will take up eight bytes of space. A similar length string field is fine.

When writing queries it's probably a good idea to use either LEFT or RIGHT JOINs, but not both. It will still work as well, but be more difficult to understand.

Seeing your SQL I suspect you may have need of a read of Database Normalisation and Table Structures.

ZMBD:
Personally, I don't like the implicit options, especially when mixed in with explicit calls

I agree, as is probably clear from my comment (Cross-posted).

[bhakti0525]

when i try to run only a part of the query i.e

SELECT p2.SiebelProdOffID AS OfferId
   , p2.ProdOffName AS RootOffering
   , p3.SiebelProdOffID AS L2OfferId
   , p1.Participant2Name AS L2OfferComponent
   , (Cstr(p4.End2Min)+'..'+Cstr(p4.End2Max)) 
      AS L2Cardinality, Null AS L3OfferId
   , Null AS L3OfferComponent, Null AS L3Cardinality
FROM (ProdOff AS p2 
   left JOIN ProdOffToProdOffAssoc AS p1
      ON p1.AssocTypeName='Aggregation' 
      and p1.Participant1Name=p2.ProdOffName) 
   LEFT JOIN ProdOff AS p3 
      ON p1.Participant2Name=p3.ProdOffName;

I have now used left join as you said NeoPa but it gives me error near

p1.AssocTypeName='Aggregation'

saying 'Join expression not supported'. I can't use this condition in where because it gives a completely different resultset.

[bhakti0525]

hey i got the solution for this .
"JOIN expression not supported" error was caused by unbracketed JOIN expression comprising string condition.
This has been an issue since Access version 1.
The one work-around i found, it may not work on all scenarios but it fits most of them.

Problem Query:

SELECT p2.SiebelProdOffID AS OfferId
   , p2.ProdOffName AS RootOffering
   , p3.SiebelProdOffID AS L2OfferId
   , p1.Participant2Name AS L2OfferComponent
   , (Cstr(p4.End2Min)+'..'+Cstr(p4.End2Max))
       AS L2Cardinality, Null AS L3OfferId
   , Null AS L3OfferComponent, Null AS L3Cardinality
FROM (ProdOff AS p2 
   left JOIN ProdOffToProdOffAssoc AS p1
     ON p1.AssocTypeName='Aggregation' 
      and p1.Participant1Name=p2.ProdOffName) 
   LEFT JOIN ProdOff AS p3 
      ON p1.Participant2Name=p3.ProdOffName;

Work Around:

SELECT p2.SiebelProdOffID AS OfferId
   , p2.ProdOffName AS RootOffering
   , p3.SiebelProdOffID AS L2OfferId
   , p1.Participant2Name AS L2OfferComponent
   , (Cstr(p4.End2Min)+'..'+Cstr(p4.End2Max)) 
      AS L2Cardinality, Null AS L3OfferId
   , Null AS L3OfferComponent, Null AS L3Cardinality
FROM (ProdOff AS p2 
   left JOIN ProdOffToProdOffAssoc AS p1  
      ON p1.AssocTypeName & p1.Participant1Name
         ='Aggregation' & p2.ProdOffName) 
   LEFT JOIN ProdOff AS p3 
      ON p1.Participant2Name=p3.ProdOffName;

[NeoPa]

Why are you using a literal value in your JOIN?

The solution is a kludge. This should be in the WHERE clause instead to make sense.

Linking on a calculation (X & Y) is very inefficient as it precludes the use of indexes. Basically a situation that should be avoided.

[zmbd]

Neopa,

Could this then be re-written as a subquery using LEFT JOINS so that

<note this is "air code">
(SELECT [...needed fields...]
FROM ProdOffToProdOffAssoc 
WHERE [AssocTypeName]='Aggregation') AS P2

Then LEFT JOIN p1.Participant1Name=p2.ProdOffName

I've never ran in to OP's situation using the literal value on a join, I've always joined against a table or (sub)Query.

[NeoPa]

It could, but there's no reason I see to complicate it that far. A WHERE clause could be used.