Page 1 of 4 123 ... LastLast
Results 1 to 15 of 51
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Comparison (2003)

    I created a report for a user to run, which gives statistics on how many jobs they have allocated, how many they have done and how many they have outstanding. I also created a spreadsheet showing whether the % completed had gone up or down from 1 week to the next. This has caused me problems, as the user deletes lines without telling me, and causes the formulas to give wrong information, or errors.
    Is there an easy way of getting this information from the database without using Excel.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparison (2003)

    I'm fairly certain it's possible. You'll have to design a series of queries to calculate % complete per week. You can then calculate the differences in a report, or create an extra query for that, and base the report on that query. If you want more specific help, we'll need to have detailed information.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Comparison (2003)

    It's information from the database I had help with in post <post#=481534>post 481534</post#>. The stats report is printed every two weeks, and gives the totals and works from the number of actions completed. What I need to do is work on the date they were completed, which the stats report doesn't do.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparison (2003)

    You'll have to put criteria on the completion date, I guess.

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Comparison (2003)

    I've put the criteria in the Date column rather than the Creation Date, and it's still giving me all the results. Will I need to alter the sql? If so I don't know where to start.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparison (2003)

    I don't understand the queries in the database from last year, so you will have to provide precise, relevant and sufficient information.

  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Comparison (2003)

    I've done a copy of the query for the stats (qryStats2), and want to remove the criteria for Date created, and put it in Date, but replace the #28/02/05#, with [Start Date]. I did try it, and when I ran the query I was asked for the Actionee, End Date, Start Date, in that order. Here's the original SQL code

    PARAMETERS [Enter Actionee] Text ( 255 ), [End Date] DateTime;
    SELECT [tblDaily Production Main].[Log No], [tblDaily Production Main].[Actual Shipped], [tblDaily Production Main].[Date Created], [tblDaily Production].Problem, [tblDaily Production].[Raised By], [tblDaily Production].[Action To Be Taken], [tblDaily Production].Actionee, [tblDaily Production].[Target Completion Date], [tblDaily Production].[Time Saved], [tblDaily Production].Comments, [tblDaily Production].Comments2, [tblDaily Production].Completed, [tblDaily Production].Initial, [tblDaily Production].Date, Count([tblDaily Production].Actionee) AS QTY, Count([tblDaily Production].Completed) AS [No Completed], IIf(Mid([Actionee],3,1)="/",Left([Actionee],2),Left([Actionee],4)) AS Name
    FROM [tblDaily Production Main] RIGHT JOIN [tblDaily Production] ON [tblDaily Production Main].[Log No] = [tblDaily Production].[Log No]
    WHERE ((("/" & [Actionee] & "/") Like "/" & [Enter Actionee] & "/*"))
    GROUP BY [tblDaily Production Main].[Log No], [tblDaily Production Main].[Actual Shipped], [tblDaily Production Main].[Date Created], [tblDaily Production].Problem, [tblDaily Production].[Raised By], [tblDaily Production].[Action To Be Taken], [tblDaily Production].Actionee, [tblDaily Production].[Target Completion Date], [tblDaily Production].[Time Saved], [tblDaily Production].Comments, [tblDaily Production].Comments2, [tblDaily Production].Completed, [tblDaily Production].Initial, [tblDaily Production].Date, IIf(Mid([Actionee],3,1)="/",Left([Actionee],2),Left([Actionee],4)), [End Date], [Enter Actionee]
    HAVING ((([tblDaily Production Main].[Date Created])>#2/28/2005# And ([tblDaily Production Main].[Date Created])<=[End Date]) AND (([tblDaily Production].Actionee) Like [Enter Actionee] & "*") AND (([tblDaily Production].Completed)="YES") AND ((Count([tblDaily Production].Completed))=True)) OR ((([tblDaily Production].Actionee) Like [Enter Actionee] & "*")) OR ((([tblDaily Production].Actionee) Like [Enter Actionee] & "*") AND (([End Date]) Is Null)) OR ((([tblDaily Production].Actionee) Like [Enter Actionee] & "*") AND (([End Date]) Is Null)) OR ((([tblDaily Production].Actionee) Like [Enter Actionee] & "*")) OR ((([tblDaily Production].Actionee) Like [Enter Actionee] & "*")) OR ((([tblDaily Production].Actionee) Like [Enter Actionee] & "*") AND (([End Date]) Is Null)) OR ((([tblDaily Production].Actionee) Like [Enter Actionee] & "*") AND (([End Date]) Is Null)) OR ((([Enter Actionee]) Is Null)) OR ((([Enter Actionee]) Is Null)) OR ((([End Date]) Is Null) AND (([Enter Actionee]) Is Null)) OR ((([End Date]) Is Null) AND (([Enter Actionee]) Is Null)) OR ((([Enter Actionee]) Is Null)) OR ((([Enter Actionee]) Is Null))
    ORDER BY [tblDaily Production Main].[Date Created];

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparison (2003)

    If you want to change the order in which you are prompted for the parameters, you must either declare them in the desired order in Query | Parameters, or modify the PARAMETERS line in the SQL:

    PARAMETERS [Enter Actionee] Text ( 255 ), [Start Date] DateTime, [End Date] DateTime;

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Comparison (2003)

    Still getting all results. Do I need to remove something from the SQL. It's asking for them in the right order, but giving me all the records.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparison (2003)

    The criteria in the query don't make much sense, and the sample data in your database from last year provide hardly any scope for trying things out. Let's start from scratch, and take it step by step.

    Part 1: what data do you want to return in the report?

  11. #11
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Comparison (2003)

    I want to report how many Completed there were for each Action between two dates (start date, end date). So we can say an Actionee completed 5 tasks between 7/6/06 and 14/6/06, but between 15/6/06 and 20/6/06, he only completed 2.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparison (2003)

    1) Which field contains the action? "Action To Be Taken"?

    2) The "tblDaily Production" table contains a field "Completed" (text) and a field unfortunately named "Date", but there is no correlation between them. How exactly do we determine whether a task has been completed?

  13. #13
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Comparison (2003)

    Yes, Action to be taken is the Action.The task is only complete if it says "Yes" in the field, and dated.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparison (2003)

    Try this:

    PARAMETERS [Enter Actionee] Text ( 255 ), [Start Date] DateTime, [End Date] DateTime;
    SELECT [Action To Be Taken], Count(*) AS NumberCompleted
    FROM [tblDaily Production]
    WHERE Completed="YES" AND Date Between [Start Date] And [End Date] AND "/" & [Actionee] & "/" Like "*/" & [Enter Actionee] & "/*"
    GROUP BY [Action To Be Taken];

  15. #15
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Comparison (2003)

    Thanks Hans that gives me some of the figures I want. In the same database that has the Stats report, I've notice something that might give me problems with the way I want to set the reports up. The stats report has been set up to give me the total number of actions, number completed, how many outstanding, and what % are completed. All this is based on two dates #28/02/05# and [End Date]. I've just run the report, and typed in 14/06/06 as the End Date. Run it again and typed in 20/06/06, and got the same results. Where should I be looking for the problem?

Page 1 of 4 123 ... LastLast

Posting Permissions

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