Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Regarding the fact that this query executes in Access 2007 but not so in Access 2010: it is hard to say why this may be the case, since most of the published differences between the two deal with specific data types and not of specifications of allowable SQL syntax.</p> <p>As other comments have suggested, I would guess the culprit lies in the <code>AND NOT EXISTS (SELECT * FROM ...)</code> conditionals.</p> <p>That being said, I will propose an equivalent query (in theory), and tips on boosting its performance.</p> <h3>Simplified Example</h3> <p>First, let's tackle what this query is trying to accomplish using words. You are seeking a cross join (cartesian product) of tables <code>Os_udaje</code> and <code>Kalendar</code>, which have certain related fields and removing rows which have related records meeting two conditions in three different tables. The latter requirement is accomplished by the <code>NOT EXISTS</code> clauses, and this is what we want to re-write.</p> <p>Take for example:</p> <pre><code>SELECT TableA.Field1, TableB.Field2 FROM TableA, TableB WHERE NOT EXISTS (SELECT * FROM TableC WHERE TableA.Field1=TableC.Field1 AND TableB.Field2=TableC.Field2); </code></pre> <p>Without going into the details of why, we can re-write this query as a three table cross join with a different set of <code>WHERE</code> conditionals:</p> <pre><code>SELECT TableA.Field1, TableB.Field2 FROM TableA, TableB, TableC WHERE (TableA.Field1=TableC.Field1) AND (TableB.Field2&lt;&gt;TableC.Field2); </code></pre> <h3>Full Equivalent Query</h3> <p>Applying this relationship to the original query, we have:</p> <pre><code>SELECT Kalendar.id_kalendar, Os_udaje.Meno, Os_udaje.Priezvisko, Os_udaje.id_os_udaje FROM Os_udaje, Kalendar, Pravocal, REZERVACIA, DOVOLENKA WHERE ((((Kalendar.id_kalendar) Between [Pociatocný dátum] And [Koncový dátum]))) AND ((Kalendar.volno)=No) AND ((Kalendar.vikend)=No) AND (((Os_udaje.Nastupil)&lt; Kalendar.id_kalendar ) AND ((Os_udaje.Odisiel)&gt;Kalendar.id_kalendar)) AND ( Os_udaje.id_os_udaje = Pracoval.id_os_udaje AND Kalendar.id_kalendar &lt;&gt; Pracoval.id_kalendar) AND ( Kalendar.id_kalendar BETWEEN REZERVACIA.platnost_od AND REZERVACIA.platnost_do AND Os_udaje.id_os_udaje &lt;&gt; REZERVACIA.id_os_udaje) AND ( Kalendar.id_kalendar BETWEEN DOVOLENKA.od AND DOVOLENKA.do AND Os_udaje.id_os_udaje &lt;&gt; DOVOLENKA.id_os_udaje); </code></pre> <h3>Tips on Boosting Performance</h3> <p>Since this query is doing a 5-table cross join, it could be very inefficient (take the product of the number of rows in each). Two techniques to boost the performance are to:</p> <p>Use <code>INNER JOIN</code> statements instead of full Cartesian joins:</p> <pre><code>SELECT * FROM (SELECT * FROM Table1,Table2) As SubQry INNER JOIN Table3 ON (SubQry.Field2=Table3.Field2 AND SubQry.Field1&lt;&gt;Table3.Field1); </code></pre> <p>Perform sub-query conditionals first to reduce the number of rows:</p> <pre><code>SELECT Kalendar2.id_kalendar, Os_udaje.Meno, Os_udaje.Priezvisko, Os_udaje.id_os_udaje FROM Os_udaje, (SELECT * FROM Kalendar WHERE ((((Kalendar.id_kalendar) Between [Pociatocný dátum] And [Koncový dátum]))) AND ((Kalendar.volno)=No) AND ((Kalendar.vikend)=No)) AS Kalendar2, Pravocal, ... </code></pre> <h3>Possible Full Answer</h3> <p>I cannot test this query, and do not know if <code>BETWEEN</code> statements work as <code>JOIN</code> conditionals, but here is the answer using joins and nested subqueries:</p> <pre><code>SELECT Kalendar.id_kalendar, Os_udaje.Meno, Os_udaje.Priezvisko, Os_udaje.id_os_udaje FROM ((( SELECT Kalendar.id_kalendar, Os_udaje.Meno, Os_udaje.Priezvisko, Os_udaje.id_os_udaje FROM Os_udaje, Kalendar WHERE ((((Kalendar.id_kalendar) Between [Pociatocný dátum] And [Koncový dátum]))) AND ((Kalendar.volno)=No) AND ((Kalendar.vikend)=No) AND (((Os_udaje.Nastupil)&lt; Kalendar.id_kalendar ) AND ((Os_udaje.Odisiel)&gt;Kalendar.id_kalendar)) ) As SubQry INNER JOIN Pravocal ON (SubQry.id_os_udaje = Pracoval.id_os_udaje AND SubQry.id_kalendar &lt;&gt; Pracoval.id_kalendar)) INNER JOIN REZERVACIA ON (SubQry.id_kalendar BETWEEN REZERVACIA.platnost_od AND REZERVACIA.platnost_do AND SubQry.id_os_udaje &lt;&gt; REZERVACIA.id_os_udaje)) INNER JOIN DOVOLENKA ON (SubQry.id_kalendar BETWEEN DOVOLENKA.od AND DOVOLENKA.do AND SubQry.id_os_udaje &lt;&gt; DOVOLENKA.id_os_udaje); </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POerror with query after change from access 2007 to 2010
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USnicholas
    1. USnicholas
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThx for your answer but mistake was in date format in my operating system. I inserting 01.01.2012 and its required 01. 01. 2012. I vote for your answer becouse you are right in performance optimalization.
      singulars
      1. PO
      1. USEddy_Screamer
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload