Dates won't sort by date (query sql)

**SiebeD** · 03-03-2021, 08:22 AM

Hey everyone!

I have a list of data that gives me a value every 15 min. I want to sort this data in a Query but then i want the sum of the 4 values of that hour. I am trying to do this with this SQL code:

Code:

 
SELECT (Format([KwartTijd],'ddddd hh')) AS DATUM, Sum(PS_Hautrage.Vermogen) AS SomHautrage, Sum(PS_Buggenhout.Vermogen) AS SomBuggenhout, Sum(PS_Mainvault.Vermogen) AS SomMainvault, Sum(PS_AssePastinaken.Vermogen) AS SomAssePastinaken, Sum(PS_BrakelKerkhofStrt.Vermogen) AS SomBrakelKerkhofstraat, Sum(PS_AthChaussee.Vermogen) AS SomAthChaussee


FROM ((PS_AthChaussee INNER JOIN PS_BrakelKerkhofStrt ON PS_AthChaussee.Id = PS_BrakelKerkhofStrt.Id) INNER JOIN PS_AssePastinaken ON (PS_AthChaussee.Id = PS_AssePastinaken.Id) AND (PS_AthChaussee.Id = PS_AssePastinaken.Id)) INNER JOIN (((DatumTijd INNER JOIN PS_Hautrage ON DatumTijd.Id = PS_Hautrage.Id) INNER JOIN PS_Buggenhout ON (PS_Hautrage.Id = PS_Buggenhout.Id) AND (PS_Hautrage.Id = PS_Buggenhout.Id) AND (PS_Hautrage.Id = PS_Buggenhout.Id) AND (DatumTijd.Id = PS_Buggenhout.Id)) INNER JOIN PS_Mainvault ON (PS_Hautrage.Id = PS_Mainvault.Id) AND (PS_Buggenhout.Id = PS_Mainvault.Id) AND (DatumTijd.Id = PS_Mainvault.Id)) ON (PS_BrakelKerkhofStrt.Id = DatumTijd.Id) AND (PS_AthChaussee.Id = DatumTijd.Id) AND (PS_AssePastinaken.Id = DatumTijd.Id) AND (PS_AthChaussee.Id = PS_Buggenhout.Id) AND (PS_BrakelKerkhofStrt.Id = PS_Buggenhout.Id) AND (PS_AssePastinaken.Id = PS_Buggenhout.Id) AND (PS_AssePastinaken.Id = PS_Hautrage.Id) AND (PS_AthChaussee.Id = PS_Hautrage.Id) AND (PS_BrakelKerkhofStrt.Id = PS_Hautrage.Id) AND (PS_AssePastinaken.Id = PS_Hautrage.Id) AND (PS_BrakelKerkhofStrt.Id = PS_Mainvault.Id) AND (PS_AssePastinaken.Id = PS_Mainvault.Id) AND (PS_AthChaussee.Id = PS_Mainvault.Id)

GROUP BY (Format([KwartTijd],'ddddd hh'));

The problem is that these values wont sort by date if i do this.
And after this i need to calculate other data (average of a month, a week or a day) to display this on a Graph. I think the best way to do this is is from this query that i am trying to make by hour. But i think this wont work because the dates wont sort correct in this query.

Thanks!

**kd2017** · 03-03-2021, 08:43 AM

That sql statement is quite a mouthful!

Shot in the dark: have you tried saving this query as a subquery, then calling it from another query and sorting it there?

Here's some "pretty" formatting in case someone wants to take a crack at it:

Code:

 SELECT ( Format([KwartTijd], 'ddddd hh') ) AS DATUM, 
       Sum(PS_Hautrage.Vermogen)           AS SomHautrage, 
       Sum(PS_Buggenhout.Vermogen)         AS SomBuggenhout, 
       Sum(PS_Mainvault.Vermogen)          AS SomMainvault, 
       Sum(PS_AssePastinaken.Vermogen)     AS SomAssePastinaken, 
       Sum(PS_BrakelKerkhofStrt.Vermogen)  AS SomBrakelKerkhofstraat, 
       Sum(PS_AthChaussee.Vermogen)        AS SomAthChaussee 
FROM   ((PS_AthChaussee 
         INNER JOIN PS_BrakelKerkhofStrt 
                 ON PS_AthChaussee.Id = PS_BrakelKerkhofStrt.Id) 
        INNER JOIN PS_AssePastinaken 
                ON ( PS_AthChaussee.Id = PS_AssePastinaken.Id ) 
                   AND ( PS_AthChaussee.Id = PS_AssePastinaken.Id )) 
       INNER JOIN (((DatumTijd 
                     INNER JOIN PS_Hautrage 
                             ON DatumTijd.Id = PS_Hautrage.Id) 
                    INNER JOIN PS_Buggenhout 
                            ON ( PS_Hautrage.Id = PS_Buggenhout.Id ) 
                               AND ( PS_Hautrage.Id = PS_Buggenhout.Id ) 
                               AND ( PS_Hautrage.Id = PS_Buggenhout.Id ) 
                               AND ( DatumTijd.Id = PS_Buggenhout.Id )) 
                   INNER JOIN PS_Mainvault 
                           ON ( PS_Hautrage.Id = PS_Mainvault.Id ) 
                              AND ( PS_Buggenhout.Id = PS_Mainvault.Id ) 
                              AND ( DatumTijd.Id = PS_Mainvault.Id )) 
               ON ( PS_BrakelKerkhofStrt.Id = DatumTijd.Id ) 
                  AND ( PS_AthChaussee.Id = DatumTijd.Id ) 
                  AND ( PS_AssePastinaken.Id = DatumTijd.Id ) 
                  AND ( PS_AthChaussee.Id = PS_Buggenhout.Id ) 
                  AND ( PS_BrakelKerkhofStrt.Id = PS_Buggenhout.Id ) 
                  AND ( PS_AssePastinaken.Id = PS_Buggenhout.Id ) 
                  AND ( PS_AssePastinaken.Id = PS_Hautrage.Id ) 
                  AND ( PS_AthChaussee.Id = PS_Hautrage.Id ) 
                  AND ( PS_BrakelKerkhofStrt.Id = PS_Hautrage.Id ) 
                  AND ( PS_AssePastinaken.Id = PS_Hautrage.Id ) 
                  AND ( PS_BrakelKerkhofStrt.Id = PS_Mainvault.Id ) 
                  AND ( PS_AssePastinaken.Id = PS_Mainvault.Id ) 
                  AND ( PS_AthChaussee.Id = PS_Mainvault.Id ) 
GROUP  BY ( Format([KwartTijd], 'ddddd hh') );

**SiebeD** · 03-03-2021, 09:32 AM

Originally Posted by kd2017

That sql statement is quite a mouthful!

Shot in the dark: have you tried saving this query as a subquery, then calling it from another query and sorting it there?

Here's some "pretty" formatting in case someone wants to take a crack at it:

Code:

 SELECT ( Format([KwartTijd], 'ddddd hh') ) AS DATUM, 
       Sum(PS_Hautrage.Vermogen)           AS SomHautrage, 
       Sum(PS_Buggenhout.Vermogen)         AS SomBuggenhout, 
       Sum(PS_Mainvault.Vermogen)          AS SomMainvault, 
       Sum(PS_AssePastinaken.Vermogen)     AS SomAssePastinaken, 
       Sum(PS_BrakelKerkhofStrt.Vermogen)  AS SomBrakelKerkhofstraat, 
       Sum(PS_AthChaussee.Vermogen)        AS SomAthChaussee 
FROM   ((PS_AthChaussee 
         INNER JOIN PS_BrakelKerkhofStrt 
                 ON PS_AthChaussee.Id = PS_BrakelKerkhofStrt.Id) 
        INNER JOIN PS_AssePastinaken 
                ON ( PS_AthChaussee.Id = PS_AssePastinaken.Id ) 
                   AND ( PS_AthChaussee.Id = PS_AssePastinaken.Id )) 
       INNER JOIN (((DatumTijd 
                     INNER JOIN PS_Hautrage 
                             ON DatumTijd.Id = PS_Hautrage.Id) 
                    INNER JOIN PS_Buggenhout 
                            ON ( PS_Hautrage.Id = PS_Buggenhout.Id ) 
                               AND ( PS_Hautrage.Id = PS_Buggenhout.Id ) 
                               AND ( PS_Hautrage.Id = PS_Buggenhout.Id ) 
                               AND ( DatumTijd.Id = PS_Buggenhout.Id )) 
                   INNER JOIN PS_Mainvault 
                           ON ( PS_Hautrage.Id = PS_Mainvault.Id ) 
                              AND ( PS_Buggenhout.Id = PS_Mainvault.Id ) 
                              AND ( DatumTijd.Id = PS_Mainvault.Id )) 
               ON ( PS_BrakelKerkhofStrt.Id = DatumTijd.Id ) 
                  AND ( PS_AthChaussee.Id = DatumTijd.Id ) 
                  AND ( PS_AssePastinaken.Id = DatumTijd.Id ) 
                  AND ( PS_AthChaussee.Id = PS_Buggenhout.Id ) 
                  AND ( PS_BrakelKerkhofStrt.Id = PS_Buggenhout.Id ) 
                  AND ( PS_AssePastinaken.Id = PS_Buggenhout.Id ) 
                  AND ( PS_AssePastinaken.Id = PS_Hautrage.Id ) 
                  AND ( PS_AthChaussee.Id = PS_Hautrage.Id ) 
                  AND ( PS_BrakelKerkhofStrt.Id = PS_Hautrage.Id ) 
                  AND ( PS_AssePastinaken.Id = PS_Hautrage.Id ) 
                  AND ( PS_BrakelKerkhofStrt.Id = PS_Mainvault.Id ) 
                  AND ( PS_AssePastinaken.Id = PS_Mainvault.Id ) 
                  AND ( PS_AthChaussee.Id = PS_Mainvault.Id ) 
GROUP  BY ( Format([KwartTijd], 'ddddd hh') );

How can you sort the data in another query?

**kd2017** · 03-03-2021, 01:38 PM

Again, this is just a shot in the dark. I don't know if this will fix your issue.
You can wrap your first query in parentheses making it a subquery and 'select' it again like this:

Code:

SELECT * 
FROM   (SELECT ( Format([KwartTijd], 'ddddd hh') ) AS DATUM, 
               Sum(PS_Hautrage.Vermogen)           AS SomHautrage, 
               Sum(PS_Buggenhout.Vermogen)         AS SomBuggenhout, 
               Sum(PS_Mainvault.Vermogen)          AS SomMainvault, 
               Sum(PS_AssePastinaken.Vermogen)     AS SomAssePastinaken, 
               Sum(PS_BrakelKerkhofStrt.Vermogen)  AS SomBrakelKerkhofstraat, 
               Sum(PS_AthChaussee.Vermogen)        AS SomAthChaussee 
        FROM   ((PS_AthChaussee 
                 INNER JOIN PS_BrakelKerkhofStrt 
                         ON PS_AthChaussee.Id = PS_BrakelKerkhofStrt.Id) 
                INNER JOIN PS_AssePastinaken 
                        ON ( PS_AthChaussee.Id = PS_AssePastinaken.Id ) 
                           AND ( PS_AthChaussee.Id = PS_AssePastinaken.Id )) 
               INNER JOIN (((DatumTijd 
                             INNER JOIN PS_Hautrage 
                                     ON DatumTijd.Id = PS_Hautrage.Id) 
                            INNER JOIN PS_Buggenhout 
                                    ON ( PS_Hautrage.Id = PS_Buggenhout.Id ) 
                                       AND ( PS_Hautrage.Id = PS_Buggenhout.Id ) 
                                       AND ( PS_Hautrage.Id = PS_Buggenhout.Id ) 
                                       AND ( DatumTijd.Id = PS_Buggenhout.Id )) 
                           INNER JOIN PS_Mainvault 
                                   ON ( PS_Hautrage.Id = PS_Mainvault.Id ) 
                                      AND ( PS_Buggenhout.Id = PS_Mainvault.Id ) 
                                      AND ( DatumTijd.Id = PS_Mainvault.Id )) 
                       ON ( PS_BrakelKerkhofStrt.Id = DatumTijd.Id ) 
                          AND ( PS_AthChaussee.Id = DatumTijd.Id ) 
                          AND ( PS_AssePastinaken.Id = DatumTijd.Id ) 
                          AND ( PS_AthChaussee.Id = PS_Buggenhout.Id ) 
                          AND ( PS_BrakelKerkhofStrt.Id = PS_Buggenhout.Id ) 
                          AND ( PS_AssePastinaken.Id = PS_Buggenhout.Id ) 
                          AND ( PS_AssePastinaken.Id = PS_Hautrage.Id ) 
                          AND ( PS_AthChaussee.Id = PS_Hautrage.Id ) 
                          AND ( PS_BrakelKerkhofStrt.Id = PS_Hautrage.Id ) 
                          AND ( PS_AssePastinaken.Id = PS_Hautrage.Id ) 
                          AND ( PS_BrakelKerkhofStrt.Id = PS_Mainvault.Id ) 
                          AND ( PS_AssePastinaken.Id = PS_Mainvault.Id ) 
                          AND ( PS_AthChaussee.Id = PS_Mainvault.Id ) 
        GROUP  BY ( Format([KwartTijd], 'ddddd hh') )) AS sbqry 
ORDER  BY DATUM;

or you could save your first query and call it from another query like this:

Code:

SELECT * 
FROM saved_subquery_name 
ORDER  BY DATUM;

My reasoning for this is you have a crazy FROM clause there with all those JOINs/ANDs. I wonder if access is just having a hard time processing and sorting. This way you force access to collect the data FIRST in a subquery, and then sorting them second after all the hard work had been done. I've found sometimes the access sql engine needs some hand holding when the sql start to get complex.

I could be wrong but I believe that if you save your subqueries separately (like example 2) it's supposed to be better for performance.

(edit)
fyi, it depends on the circumstances but if you can get away with it I think it's generally recommended not to do the sorting in the query itself and just sort it in the form or report instead.

**Missinglinq** · 03-03-2021, 02:50 PM

Does KwartTijd include a Time component?

If so, and this also just a guess (I don't use SQL extensively,) but when you use

Format([KwartTijd],'ddddd hh')

your formatting changes KwartTijd from a Date/Time Datatype to a String...that's what the Format() function does. And the 'Group By' wouldn't group Strings that the same way as grouping by Date/Time.

Linq ;0)>

**SiebeD** · 03-04-2021, 01:40 AM

Originally Posted by Missinglinq

Does KwartTijd include a Time component?

If so, and this also just a guess (I don't use SQL extensively,) but when you use

Format([KwartTijd],'ddddd hh')

your formatting changes KwartTijd from a Date/Time Datatype to a String...that's what the Format() function does. And the 'Group By' wouldn't group Strings that the same way as grouping by Date/Time.

Linq ;0)>

O well thats possible indeed because the code from @kd2017 won't work either. And this would explain why.
But is it possible to sort the dates then? Now I am looking into the function 'FormatDateTim()' but here i dont have the option to change the time from every 15 mins to every Hour.

**Welshgasman** · 03-04-2021, 02:41 AM

I would sort by the dates. Use functions like Year(), Month(), Day() and Hour() to get the relevant parts for your summing.?

Dates won't sort by date (query sql)

Thread Tools

Dates won't sort by date (query sql)

Similar Threads

Query Help! Need to sort the number of records in each department between a certain date

Multi table Date query to a Between dates frm

Cross tab query columns sort date by month then year

How to get a query to sort the date properly...

How to SORT a date column and pass the thrid highest one to an SQL query

Tags for this Thread

Posting Permissions