Joining Queries

[JHawke]

I am having trouble joining two queries.  I am trying to have a current month sales value and a YTD sales value side by side.  I have created two queries (one for month, and one for YTD) that are lists.  Each table has two columns, Sales Rep, and dollar amount.  When I join the queries with the common sales rep field, I get crazy high numbers that are incorrect.   What am I doing wrong?

[MichaelB]

You possibly shouldn't need to do two queries for this. But, as far as your crazy high numbers, you obviously have duplication going on. Multiple rows of the same Sales Rep exist in the first query by month I presume. And when you join that to the second query (where you might only have one row for the entire year) your resulting joined query will multiply the Year sales figure by the number of months that exist for that Sales Rep. Twelve months in a year, so potentially how you have it set up, it could be multiplying that YTD number by 12.

So, in order to not use two queries, because it sounds like you are basically using the same data, but segmenting it differently, you can use this fancy little treat in a data item: total([Sales] for [SalesRep], [Year]). The "for" clause helps sum your totals by specific segments.

[JHawke]

Thank you!  That is working now.   Great advice.  I will start to work this logic in on more of my reports now.