Results 1 to 6 of 6
  1. #1
    SiebeD is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    14

    Cant combine queries

    Hey everyone!
    I have 2 queries. 1 is with data per day:

    like this. And this is the sql code:

    Code:
    SELECT Tijd.DatumVerbruik, PS_Buggenhout.VerbruikAS PS_Buggenhout_Verbruik
    FROM Tijd INNER JOIN PS_Buggenhout ON Tijd.Id =PS_Buggenhout.IdVermogen;
    And then i also have another Query. But here i haddata per hour and i changed it to data per day. (so first there was a tablewith the data per hour and i made another one with data per day)


    Code:
     SELECT DISTINCTROWFormat$([Tijd].[DatumVermogen],'dd/mm/yyyy') AS [DatumVermogen per dag],Sum(PS_Buggenhout.idVermogen) AS [Som Van idVermogen]FROM PS_Buggenhout INNERJOIN Tijd ON PS_Buggenhout.[idVermogen] = Tijd.[Id]
    GROUP BYFormat$([Tijd].[DatumVermogen],'dd/mm/yyyy');
    But the time field in this query changed to astring. And i want to combine these 2 queries ( i want to devide de first one/the second one) and sort the outcome per day. But because the datafield in thesecond query is now a string this is nog possible and i have an error want Itry to do this.


    Does anyone have a solution for this.

    Thanks!

  2. #2
    SiebeD is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    14
    This is the first Query:
    Click image for larger version. 

Name:	Capture.PNG 
Views:	8 
Size:	18.7 KB 
ID:	44552

    this is the second one :
    Click image for larger version. 

Name:	Capture.PNG 
Views:	8 
Size:	18.7 KB 
ID:	44552

  3. #3
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If you use DateValue() instead of the format it will strip any time component out.

    If a time component of your date field isn't the issue, then don't format it - do the formatting in the final output
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    SiebeD is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    14
    Quote Originally Posted by Minty View Post
    If you use DateValue() instead of the format it will strip any time component out.

    If a time component of your date field isn't the issue, then don't format it - do the formatting in the final output
    I tried to replace "Format()" by "Datavalue()" but then the Query didn't want to proces and i got an error message.
    And what do you mean in the final output?

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    What was the SQL of the query you ended up with?

    It should be something like
    Code:
    SELECT DateValue([Tijd].[DatumVermogen]) AS [DatumVermogen per dag],Sum(PS_Buggenhout.idVermogen) AS [Som Van idVermogen]FROM PS_Buggenhout INNER JOIN Tijd ON PS_Buggenhout.[idVermogen] = Tijd.[Id]
    GROUP BY DateValue([Tijd].[DatumVermogen]);
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    SiebeD is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    14
    Quote Originally Posted by Minty View Post
    What was the SQL of the query you ended up with?

    It should be something like
    Code:
    SELECT DateValue([Tijd].[DatumVermogen]) AS [DatumVermogen per dag],Sum(PS_Buggenhout.idVermogen) AS [Som Van idVermogen]FROM PS_Buggenhout INNER JOIN Tijd ON PS_Buggenhout.[idVermogen] = Tijd.[Id]
    GROUP BY DateValue([Tijd].[DatumVermogen]);
    Yeah i made a syntax error.. i am sorry! But the good news is 'datevalue' works now and the data sorts perfectly.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How do I combine these two queries?
    By racefan91 in forum Queries
    Replies: 18
    Last Post: 10-19-2013, 12:54 PM
  2. Combine three queries into one
    By Ray67 in forum Queries
    Replies: 8
    Last Post: 06-13-2012, 11:23 AM
  3. Combine Queries
    By Steven.Allman in forum Queries
    Replies: 9
    Last Post: 08-30-2010, 12:13 PM
  4. Combine 3 Queries
    By Logix in forum Queries
    Replies: 1
    Last Post: 06-10-2010, 06:03 PM
  5. Combine queries
    By thart21 in forum Queries
    Replies: 1
    Last Post: 03-05-2010, 01:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums