Thread: Combined Statistics (2003)

1. 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. Re: Combined Statistics (2003)

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

3. Re: Combined Statistics (2003)

Yes, that's right. Thanks.

4. 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. 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. Re: Combined Statistics (2003)

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

7. 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.

8. Re: Combined Statistics (2003)

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

9. Re: Combined Statistics (2003)

No, that didn't work.

10. Re: Combined Statistics (2003)

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

11. 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
•