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

    Combined Statistics (2003)

    Many moons ago, (so long I can't remember the post numbers), I had help with splitting statistics in a database, Problems & Innovations. I could print a report which gave me last weeks stats compared to this weeks stats, and I could pick whether I wanted Problems or Innovations. Now, our MD has decided, yet again, that he wants them linked together, Problems and Innovations. This is no problem, I have managed to do it, but he also wants a column which says whether the figures are up or down. I could eventually work this out, but as with most MD's, he's wanting it urgent. What I really need is help with combining the results of two queries. One is outlined as :-

    Name, No Allocated Last week, Qty Done, Percentage Last Wk, Percentage This Week, No Allocated This Week.

    This is is for Innovations. The other query is the same but for Problems. What I want is for all the figures (Innovations and Problems) to be added together, so if we had 10 Innovations and 5 Problems, the number would be 15. I basically want one set of figures for two queries, although they are recorded as Innovations and Problems. I can't post the database because of the number of the complexity, and the time I have. Hope I have explained it clearly.

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

    Re: Combined Statistics (2003)

    What would you like to match the queries on? Name?

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

    Re: Combined Statistics (2003)

    Yes, that's right. Thanks.

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

    Re: Combined Statistics (2003)

    Create a new query based on the two that you already have.
    Join them on the Name field.
    To add the numbers allocated last week together, create a calculated column in the query:

    Total Numbers Allocated Last Week: [FirstQuery].[Numbers Allocated Last Week]+[SecondQuery].[Numbers Allocated Last Week]

    Substitute the correct names. Similar for the others.

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

    Re: Combined Statistics (2003)

    Because the original queries are run from a button on a form, when the new querie is run, I'm ask for information from the form. How do I stop it from doing this?

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

    Re: Combined Statistics (2003)

    You could create duplicates of the queries and remove the reference to the form from the duplicates.

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

    Re: Combined Statistics (2003)

    Edited by Hansv to reduce large screenshot in size - please don't post images larger than 640x480 pixels

    Because of it's complexity, there are a number of queries that are linked. I've managed to create one that gives me the right figures. The only problem is, they are tripled (see attachment). I don't know how to get round this.
    Attached Images Attached Images
    • File Type: jpg x.jpg (13.2 KB, 0 views)

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

    Re: Combined Statistics (2003)

    Try setting the Unique Values property of the query to Yes.

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

    Re: Combined Statistics (2003)

    No, that didn't work.

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

    Re: Combined Statistics (2003)

    Can you change Group By to something else for the variables that are different within a triplet?

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

    Re: Combined Statistics (2003)

    I think they're entering data incorrectly, as I was getting wrong results. So until i get the correct information, I can't do much more. Thanks for your help Hans.

Posting Permissions

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