Results 1 to 3 of 3

Thread: Query Help!

  1. #1
    Join Date
    Jan 2013
    Posts
    22

    Unanswered: Query Help!

    Hey Guys,

    Thank you in advance!

    But I am trying to write a query to figure out how much time we actually take on a project overall based on the number of total number of Qs.

    I have writing the following query to do the just that:

    Code:
    SELECT CBT.[Project Title], Sum(CBT.[Time Take]) AS [Total Time Taken], Max(GlobalPipe.[# Qs]) AS [Total Qs], Max(GlobalPipe.[# Qs]) AS [Total Qs] / Sum(CBT.[Time Take]) AS [Total Time Taken] AS [Time Per Question]
    FROM CBT INNER JOIN GlobalPipe ON (CBT.[Project Title]=GlobalPipe.[CBT Name]) AND (CBT.[Writer Name]=GlobalPipe.[Writer 1]) AND (CBT.Region=GlobalPipe.Region)
    GROUP BY CBT.[Project Title];
    I would really appericate if you anyone to let me know where am I going wrong with this.

    Thank you again!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well to tell us what isn't working might be a good starting point
    bearing in mind we don't know how your tables are designed and what they relate to
    we also don't know why your query isn't working, and we cant find out for ourselvesd because there is no test data.

    unfortunately my crystal ball is being given its annual service so its kinda difficult to diagnose where you are going wrong with this
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    nbkn8ct,

    Is this closer? (TotalTime, TotalQuestions, AverageTime)

    Code:
    SELECT CBT.[Project Title], 
           Sum(CBT.[Time Take]) AS [Total Time Taken], 
           Count(GlobalPipe.[# Qs]) AS [Total Qs], 
           Avg(CBT.[Time Take]) AS [Time Per Question]
    FROM CBT INNER JOIN GlobalPipe ON 
             CBT.[Project Title] = GlobalPipe.[CBT Name] AND 
             CBT.[Writer Name] = GlobalPipe.[Writer 1] AND 
             CBT.Region = GlobalPipe.Region
    GROUP BY CBT.[Project Title];
    hth,
    Wayne

Posting Permissions

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