Results 1 to 2 of 2
  1. #1
    Join Date
    May 2014

    Unanswered: Difficulty getting correct data ---Running a Report

    I am trying to run a report showing 3 things
    Name of employee
    Start date of open issue
    Days since the issue is open (For calculating days I am using formula =DateDiff("d",[Start Date],Now())
    and my query is

    SELECT [Form].[Employee Name], Count([Form].[Completed By]) AS [CountOfCompleted By], [Form].[Start Date]
    FROM [Form]
    WHERE ((([Form].[Close Date]) Is Null))
    GROUP BY [Form].[Completed By], [Form].[Start Date]
    HAVING ((([Form].[Completed By]) Is Not Null) AND ((Count([Form].[Completed By])) Is Not Null))
    ORDER BY [Form].[Completed By];

    But my problem is my query gives employees name duplicate times

    so my data looks as this
    Name Start Date Days issue open
    John 2/2/2014 2
    John 3/2/2014 4
    John 4 /2/2014 6

    Where is I want employee name to appear once with maybe total number of days
    John 2/2/2014 6

    Showing start date is not required in the report.

    I tried doing where condition for start date and then query gives me correct data as in
    John 2/2/2014 6
    However as start date is where doesn't show up on report and thus no calculation days issue open.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    eeeek: form is a reserved word within Access and may also be in JET SQL. you may get away with it but you really ought NOT to use any rserved word. nor should you use a space in a column or table anme.

    so just guessign, becuase you haven't made your table design clear (or at least clear to me)

    you want all rows where the issue is still open (is that CompletedBy or closedate set to null..... its not clear. lets assume it closedate

    select EmployeeName,  StartDate, Datediff("D", Date(), StartDate) as ElaspedDays, ..... from myTable
    where is null EndDate
    why do you think you need the count function?

    if you think you may have multiple issues open against the same person then (well start date is no longer relevant as there could be different startdates
    select EmployeeName,  sum(Datediff("D", Date(), StartDate)) as ElapeedDays, ..... from myTable
    where is null EndDate
    group by employeename
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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