Join messing up my data

[RudiHendrix]

I'm creating a report with a graph with running totals.
The query is this:
If I run this query in tabular mode the running totals don't make sense at all. But if I run the entire report I get the following graph.
This is nice! I checked the data and it is all ok!

However, based upon a value in the database the user wants to see the graph with either a Dutch heading or English heading. Further the data format should be Dutch or English (I'm not sure how to do that, but that is a matter for a little bit later).

So I created another query and that retrieves the language code for a given contract. (In this example I filtered it to be on the safe side.)

Then I create a new query. This query is the result of a join of the first query and the last query. I join on Contractcode from the last = DATES_CNTRCT_CODE from the first query.
In the new query I pull all data from the first query and only the [Tekstwaarde] from the last query and I call this one EMAIL_LANGUAGE.
So far I'm not even using the field EMAIL_LANGUAGE

If I connect the graph to the new query and run the report in the result all of a sudden all of my data is gone! The y-axis has changed to display a maximum of 1. How is this possible?!

I've been struggeling with this all day and I would really like some help!

Thanks in advance!

[RudiHendrix]

Perhaps it is not really the join...
I just added a data item to both queries with just '1' in it. Joined the queries on those data items and I see the same incorrect result :(

[blom0344]

I had more or less the same problem just 2 weeks ago when trying to build a quick report at the customer site. One of the 2 queries simply performed a count on an object.
After joining the 2 queries to build a third, every count was reported as '1'.
Finally I changed the aggregate setting in query 3 for the object to 'maximum' and got the proper results returned.

Not sure why this is needed, but can you check whether changing the aggregate setting solves your case?

TerraNova?  (Waar werk je aan?)

[RudiHendrix]

Thanks for the suggestion! Changing the aggregation does change the way the data is displayed. Now it shows wrong data, but that probably has to do with the fact that I need to calculate the running total in the query that is a result of the join...I shouldn't just take the data from the first query.

Let's see where this gets me!

In the worst case I'll have to change the SQL statement that provides the input for one of the two joined queries and include the new item there.

[RudiHendrix]

Okay...finally I've found the solution. Although the aggregation settings did push me in the right direction that was not the solution.

In order to solve the issue I needed to recreate the running total calculations in the join-result-query instead of just taking those values along. Now everything looks perfect again!

New guideline for Plantje: when joining only create calculations in the last query! :)