Join shows huge jump in values

[wikid]

Hey guys,

I've been googling and searching this forum for the past day and couldn't find a very good solution to this problem.

So in report studio when I perform a join from:

Current Year and Last Year on an employee ID (1..1 = 1..1)
showing revenue month to month,
the revenue returned is multiplied 1000x or more.
(although the base queries return the right data)

I read somewhere that it is because the data being used in the join is being multiplied by say the number of rows, or some other aspect, but none of them seem to specify what it is.

If anyone can please help me out, or if this it too vague I can go into more depth on what it is exactly what I am doing in the join.

edit: what makes the rows unique is the: employee id, month, and their revenue for that month. They are just totaling the revenue of each employee based on the current and past months.

as far as the inner join, I'm still working on that part, just wanted to get this abnormality out of the way

[Lynn]

It would help if you described what makes a row unique in each of your two queries as well as what the join details are. Presumably there must be some date conditions in the join as well.

As a side note, you indicate an inner join but is that the right approach? Your going to lose rows for any employee that is in a particular period this year but not in the corresponding period last year. The reverse is also where you'll lose records.

[wikid]

update:

got it to work properly, here's how I did it (definitely not the cleanest way, but it works)...

Instead of joining the 2 queries directly, what I did was create a new query for each (so one for last year, one for this year), where I just link the base query to so it can use its data (I'm still not sure why it works this way and not the original way I did it, perhaps my filters?).

So after doing that, I drag over only the values I was going to use into each one, and join these new queries together, in my case using a 0..1 relationship.
This returned the proper values, and not some value multiplied 1000x over.

Sorry if this explanation is hard to follow as well, just thought I'd help someone out who might also be facing this problem without direct access to the framework.