Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Question Unanswered: how to insert a result of one query to another?

    Hi Guys,

    I've just registered and this is my first post. I've written a query and i want to add up one more field into the result table.

    SELECT Project.ProjectID, Client.ClientName, DATEDIFF("d",[Project.ActualStartDate],[Project.ActualFinishDate]) AS ActualDays, DATEDIFF("d",[Project.EstimatedStartDate],[Project.EstimatedFinishDate]) AS EstimatedDays
    FROM Project INNER JOIN Client ON Client.ClientID=Project.ClientID
    GROUP BY Project.ProjectID, Client.ClientName, DATEDIFF("d",[Project.ActualStartDate],[Project.ActualFinishDate]), DATEDIFF("d",[Project.EstimatedStartDate],[Project.EstimatedFinishDate]);


    as you can see my result will contain ProjectID, ClientName, Actualdays and EstimatedDays. Everything is fine. however, I want to add up one more field which is the difference between the number of actual days and estimated days calculated in above query. I don't know how to do that. Any help would be highly appreciated.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Welcome to the forum!

    I'm guessing it would look like this:

    SELECT Project.ProjectID, Client.ClientName, DATEDIFF("d",[Project.ActualStartDate],[Project.ActualFinishDate]) AS ActualDays, DATEDIFF("d",[Project.EstimatedStartDate],[Project.EstimatedFinishDate]) AS EstimatedDays, DATEDIFF("d",[Project.ActualStartDate],[Project.ActualFinishDate])-DATEDIFF("d",[Project.EstimatedStartDate],[Project.EstimatedFinishDate]) as DiffInDays
    FROM Project INNER JOIN Client ON Client.ClientID=Project.ClientID
    GROUP BY Project.ProjectID, Client.ClientName, DATEDIFF("d",[Project.ActualStartDate],[Project.ActualFinishDate]), DATEDIFF("d",[Project.EstimatedStartDate],[Project.EstimatedFinishDate]);

    I'll usually do these in the query designer (using expressions) and then viewing the SQL syntax by changing (upper left), the design icon to SQL. Then I copy/paste that into my code and troubleshoot for minor syntax differences.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Sep 2009
    Posts
    2
    Thanks for your quick reply. and yes that was the right solution you gave me. I am just wondering what tool do you use to generate queries ? I am currently using free copy of FlySpeed Query Tool. It looks very basic with only few functions. Please suggest me a good tool as well. Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •