Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have a query which gives the details of parts cast in the foundry. I added a yes/no field to one of the tables, to show if a cast was late. From this, I created another query to give me how many casts were late between two given dates. When I ran the query, it gave me a total of 15, when I know there is only 14, and I don't know where the extra 1 is coming from. What I'm trying to achieve is a basic report showing how many were cast, how many of those were late, and what % of those cast were late. Will I need to create another query to do this?

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Not sure if this is what you are after, but it might give you some ideas.
    You can probably use SUM with IIF to get your info out in a single query depending upon the complexity of your data of course
    See attached example

    [attachment=87495:Casts.zip]
    Attached Files Attached Files
    Andrew

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    If you already have the number of items, and the number late, you can put a calculated control on the report to display the percentage that are late.

    Set its control source to : = [numberlate]/[totalparts] and format it as a percentage.
    Change the names to those you are using.

    As for the incorrect total, first just build a plain select query to find all the late parts within the date range. When you are happy that it returns the correct records, convert it to a query that counts them.
    Regards
    John



  4. #4
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Andrew, John. I've used Andrews example. I want the user to input the date range (from and to), but if I replace your dates with parameters, in both columns, it asks for them twice.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    If it asks twice , there is something slightly different between the two pairs of parameters. Perhaps one has more spaces, or different spelling.

    I used Andrew's example with prompts and it did not ask twice for me.
    Regards
    John



Posting Permissions

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