Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I corrected a variety of things: (1) put the joins in the right order (2) removed misplaced parentheses (3) added table aliases for readability (4) removed double quotes around <code>"gg"</code> (5) made the query against r.created an open-ended range so that an index could be used if it exists. I also removed <code>jaar</code> from the inner query in the grouping - based on the where clause, this can never be anything other than <code>2012</code>.</p> <p>I don't know what Access will do with this if you paste it back into there, but you should be able to confirm this is a more correct query than the garbage Access produced for you by running it against SQL Server directly. If Access still breaks this, I would put it in a stored procedure or a view in SQL Server, and point Access at that.</p> <pre><code>SELECT jaar = 2012, maand, wicode, pg_intern_code, pg_kriss_code, pg_kriss_naam, COUNT(klavnr) AS AantalVanKLAVNR FROM ( SELECT maand = MONTH(r.created), r.wicode, i.pg_intern_code, k.pg_kriss_code, k.pg_kriss_naam, r.klavnr FROM dbo.tblproductgroep_kriss AS k INNER JOIN dbo.tblrobbe_pg AS tr ON k.pg_kriss_id = tr.pg_code_kriss_id INNER JOIN dbo.tblproductgroep_intern AS i ON i.pg_intern_id = tr.pg_code_intern_id INNER JOIN pc0517.dbo.resul AS r ON tr.robbe_pg_naam = r.prcode WHERE r.created &gt;= '20120101' AND r.created &lt; '20130101' AND r.kanaal = 'gg' ) AS x GROUP BY maand, wicode, pg_intern_code, pg_kriss_code, pg_kriss_naam; </code></pre> <p>It can also be simplified as:</p> <pre><code> SELECT jaar = 2012, maand = MONTH(r.created), r.wicode, i.pg_intern_code, k.pg_kriss_code, k.pg_kriss_naam, r.klavnr FROM dbo.tblproductgroep_kriss AS k INNER JOIN dbo.tblrobbe_pg AS tr ON k.pg_kriss_id = tr.pg_code_kriss_id INNER JOIN dbo.tblproductgroep_intern AS i ON i.pg_intern_id = tr.pg_code_intern_id INNER JOIN pc0517.dbo.resul AS r ON tr.robbe_pg_naam = r.prcode WHERE r.created &gt;= '20120101' AND r.created &lt; '20130101' AND r.kanaal = 'gg' GROUP BY MONTH(r.created), r.wicode, i.pg_intern_code, k.pg_kriss_code, k.pg_kriss_naam; </code></pre> <p>(In other words, the subquery seems unnecessary.)</p>
    singulars
    1. This table or related slice is empty.
    1. POSQL-server syntax error when combining queries (pass-through query)
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAaron Bertrand
    1. USAaron Bertrand
    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
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThis works like a charm. Thank you so much for taking the time!
      singulars
      1. PO
      1. USuser1453540
 

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